• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 289
  • Last Modified:

Need way to prevent duplicates in Excel 2007 drop down lists

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.

  • 3
  • 2
1 Solution
Chris BottomleyCommented:
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

netwrkedAuthor Commented:
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.


Chris BottomleyCommented:
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.

netwrkedAuthor Commented:
That works perfectly, many thanks!
Chris BottomleyCommented:
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.


Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now