Need way to prevent duplicates in Excel 2007 drop down lists

Posted on 2010-01-03
Last Modified: 2012-05-08
I am looking for a way to change the order of a drop down list dynamically.  Example:

Col1       Col2
1            Sample1
2            Sample2
3            Sample3
4            Sample4

First column is static (rank/priority).  Drop down is in Col2.  If I want to change the first row to Sample4, I will have two Sample4's.   Is there a way that if I put Sample4 on the first row, it will swap out and put Sample1 where Sample4 is (was) or at least clear Sample1 to eliminate the duplicate?   Same for the rest of the rows.  My actual sheet has 18 rows of information.  Essentially, I have formulas based on priorities and I need to model the data based on changing the priority order.

Thanks in advance for any help/advice.

Question by:netwrked
    LVL 59

    Expert Comment

    by:Chris Bottomley
    Hello netwrked,

    Drag will result in only one copy, if it is always a 'swap' then a macro could implement a swap of two selected items as below:

    Note the use of a new sheet should mean the format is remembered during teh 'swap'.


    Sub swapTwo()
    Dim rng As Range
    Dim sh As Worksheet
        Application.ScreenUpdating = False
        Set rng = Selection
        If rng.Cells.Count <> 2 Then Exit Sub
        Set sh = ThisWorkbook.Sheets.Add
        rng.Cells(1).Copy sh.Range("a1")
        rng.Cells(2).Copy rng.Cells(1)
        sh.Range("a1").Copy rng.Cells(2)
        Application.DisplayAlerts = False
        Application.DisplayAlerts = True
        Application.ScreenUpdating = True
    End Sub

    Open in new window


    Author Comment

    I probably was not clear, sorry about that.  I am not trying to do a copy.  I have a range of cells in a column that each contain a dropdown.  There are 18 rows of data that are displayed.  If I change one cell's drop down, I need to ensure that there are no duplicates in the other cells.  I have attached a sample spreadsheet of what I am trying to do.


    LVL 59

    Accepted Solution

    Assuming macros are ok, (I saved as 2003 format to make the upload work but you can resave as 2007 .xlsm if you want) the following hopefully does that for you.


    Author Closing Comment

    That works perfectly, many thanks!
    LVL 59

    Expert Comment

    by:Chris Bottomley
    Glad to help ... and sorry for my initial failure to understand - the sample file was (and always is) a good idea as it helps both understanding and testing.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
    This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now