No Duplicates in Multiple Selection Excel Drop Down

No Duplicates in Multiple Selection Excel Drop Down

Thanks to an email question from Leslie, I’ve done another variation on the Data Validation Multiple Selection sample.

Leslie is scheduling training courses, and has a table with dates across the top, and course names down the side. One or more of the instructor names can be selected for each training session.

DataValidationDuplicateBlock01
However, Leslie wants to prevent an instructor’s name from being selected twice on the same day. For example, on Date 01, in the screen shot above, Bob Smith and Mary Jones are each booked for 2 courses.

Check for Existing Names

To prevent the names from being selected twice, I changed the code, to include the COUNTIF function.

  • After a name is selected, the active column is checked for that name.
  • If the name is found, a warning message is shown, and the name is not added in the current cell.

Here is the revised section of the code, with the COUNTIF function:

DataValidationDuplicateBlock02

Testing the Code

With the revised code, if I try to select a trainer who is already booked, I see this message, and the name is not added.

DataValidationDuplicateBlock03

Download the Sample File

If you’d like to test the Block Duplicates code, you can download the No Duplicates in Multiple Selection Excel Drop Down sample file.

The file is in Excel 2007 format, and is zipped. When you open the file, enable macros, if you want to test the block duplicates feature.
_____________

30 thoughts on “No Duplicates in Multiple Selection Excel Drop Down”

  1. That’s really useful, thanks. I had a similar problem but it wasn’t quite as complex – I wanted to make sure that duplicate information wasn’t entered into a column. I used data validation with the forumla =COUNTIF([range],[cell])=1 which did the trick!

  2. The example above will check for the WHOLE column the table cell resides in.
    If you – for example have trainer’s name mentioned BELOW the table, this will result in a duplicate error.
    I think we must restrict the area to the table.

  3. Hi,
    I’m trying to develop a code that will stop duplicates from being entered. For example I am organizing a competition and have developed a code that enables people to type in there first name-. In the likely event of two or more people submitting the same name, I would like to know how to create a prompt to stop this happening and allow the person to choose another name or perhaps extend the name, from john to johnny, or john to john1 etc.
    The code below is what I’ve pieced together from another code on this website, my knowledge is limited with excel coding though I would like to learn more. Can you help?
    Private Sub CommandButton1_Click()
    Dim historyWks As Worksheet
    Dim inputWks As Worksheet
    Dim nextRow As Long
    Dim oCol As Long
    Dim myRng As Range
    Dim myCopy As String
    Dim myCell As Range
    ‘cells to copy from Input sheet – some contain formulas
    myCopy = “D5,D7,D9,D11,D13”
    Set inputWks = Worksheets(“INPUT”)
    Set historyWks = Worksheets(“DATABASE”)
    With historyWks
    nextRow = .Cells(.Rows.Count, “B”).End(xlUp).Offset(1, 0).Row
    End With
    With inputWks
    Set myRng = .Range(myCopy)
    End With
    With historyWks
    With .Cells(nextRow, “B”)
    .Value = cbofirstname
    With historyWks
    With .Cells(nextRow, “C”)
    .Value = cbocontactnumber
    End With
    End With
    End With
    cbofirstname.Value = “”
    cbocontactnumber.Value = “”
    End With
    End Sub

  4. Debra,
    Thanks for this great multi-select code, as well as all your other great advice. You have been an important teacher for this Excel student.
    I modified your code to prevent duplicates from being enterred in the same cell (by checking to see if the most recent selection was already present):
    Else
    Application.EnableEvents = False
    newVal = Target.Value
    Application.Undo
    oldVal = Target.Value
    Target.Value = newVal
    If oldVal “” Then
    If newVal “” Then
    If InStr(1, oldVal, newVal, 1) > 0 Then
    Target.Value = oldVal ‘reinsert old value, user selected same value twice
    Else
    Target.Value = oldVal _
    & “, ” & newVal
    End If
    End If
    End If
    End If
    Thanks again,
    Phil

  5. Hi,
    I am using your VBA code for making multiple selections from a drop down list (to column format). The formula works great, but i would like it to prevent duplicates from being added. However, when i used the above sample, it tells me my variable “newVal” has not been defined… is there something i need to define it as, or do i have to use one of my old values. Thank you!
    Option Explicit
    ‘ Developed by Contextures Inc.
    http://www.contextures.com
    Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo exitHandler
    Dim rngDV As Range
    Dim lRow As Long
    Dim lCol As Long
    lCol = Target.Column ‘column with data validation cell
    lVal = Application.WorksheetFunction _
    .CountIf(Columns(Target.Column), _
    “*” & newVal & “*”)
    If Target.Count > 1 Then GoTo exitHandler
    On Error Resume Next
    Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
    On Error GoTo exitHandler
    If rngDV Is Nothing Then GoTo exitHandler
    If lVal > 1 Then
    If newVal = “” Then
    ‘do nothing
    Else
    MsgBox “Criteria Already Selected.”
    Target.Value = oldVal
    End If
    If Intersect(Target, rngDV) Is Nothing Then
    ‘do nothing
    Else
    If Target.Value = “” Then GoTo exitHandler
    Application.EnableEvents = False
    Select Case Target.Column
    Case 4, 6, 8, 11
    If Target.Offset(0, 0).Value = “” Then
    lRow = Target.Row
    Else
    lRow = Cells(Rows.Count, lCol + 9).End(xlUp).Row + 1
    End If
    Cells(lRow, lCol + 9).Value = Target.Value
    End Select
    End If
    exitHandler:
    Application.EnableEvents = True
    End Sub

  6. How would you modify the duplicate check code to prevent duplicate values being entered into the same cell (instead of the same column)?

  7. Thanks for the add Phil. However you code get’s syntax errors for the below lines. Any thoughts?
    If oldVal “” Then
    If newVal “” Then

    1. Has anyone (Ray maybe?) figured out how to prevent duplicates selected from an excel dropdown in the same cell? I’ve tried Phil’s code above and encountered the same difficulty as Ray – it’s got syntax errors. Any help much appreciated.

      1. Debra,
        I was unable to locate the sample to keep the same item from being selected in a cell. There is no sample sheet called SameCellNoDups that I can find.
        Thank You,
        Andrew

      2. Andrew, go to this page on my site, and then go to the Download the Sample File section.
        That file is named DataValMultiSelect.xls and you will find the SameCellNoDups sheet in there, about 1/2 way through the sheet tabs.

      3. Debra,
        I was able to get the no dups in a cell to work. My problem now is that I’m using a list that I link to from another tab in the booklet. The drop down only allows me to select one entry. If I put the list in the data validation separated by commas it works fine. How do I get it to work from the other list I created on another tab? My list is very lengthy.
        I appreciate all the help you provide.
        Thank You,
        Andrew

      4. I followed the steps for the named range. I still can only select one entry per cell. It does not allow me to make multiple selections.
        Andrew

      5. Is there as way to create a pivot table where data from a cell that has multiple items are counted separately.

  8. Hi,
    Can anyone tell me how I could adapt the code from the original sample file (probably simpler) to prevent duplicate entries in a Range D10:D20 where all 10 cells have a dropdown list, entering only 1 entry into each cell… So, if I entered ‘Bill’ from a dropdown list into cell D12 and then entered ‘Bill’ again into cell D16 it would give me the error and prevent it being entered twice..??
    Any help much appreciated,
    Chris

  9. Debra,
    I tried using the SameCellNoDups sample you have graciously provided on the DataValMultiSelect.xls worksheet. All worked well with 1 exception for which I have a requirement.
    I am not able to remove a single or multiple entry from the cell once I choose it from the drop down list. I would have to remove all the entries.
    What am I doing wrong?
    Your help is greatly appreciated..
    Paul

  10. I’m using some of you VBA coding for a template to set up projects. Basically, in order to get a project set up in our database we need to know who needs access to a project. So I’ve added a named range called (Employee_Names) and I’m using that for my drop down list. So the end user filling out the .xls form, can either pick from the list or type in the names themselves. The issue I’m having is if a user starts to pick from the drop down and then decides it is easier to type the name, it is then causing everything to duplicate. I’m using Data Validation for that cell as follows: Allow: List (checked- ignore blank and in-cell dropdown), nothing selected for Data, Source is my named range (=Employee-Names), no check for apply these changes to all other cells. Input Message is checked, Error Alert is not checked. I’m also using the below VBA code. Any idea how to allow people to type and select from a drop down with causing it to duplicate?
    Sub Multiple_Selection()
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngDV As Range
    Dim oldVal As String
    Dim newVal As String
    Dim lUsed As Long
    If Target.Count > 1 Then GoTo exitHandler
    On Error Resume Next
    Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
    On Error GoTo exitHandler
    If rngDV Is Nothing Then GoTo exitHandler
    If Intersect(Target, rngDV) Is Nothing Then
    ‘do nothing
    Else
    Application.EnableEvents = False
    newVal = Target.Value
    Application.Undo
    oldVal = Target.Value
    Target.Value = newVal
    If Target.Column = 2 Then
    If oldVal = “” Then
    ‘do nothing
    Else
    If newVal = “” Then
    ‘do nothing
    Else
    Target.Value = oldVal _
    & “, ” & newVal
    End If
    End If
    End If
    End If
    exitHandler:
    Application.EnableEvents = True
    End Sub

    1. You could add a new variable:
      Dim lOld As Long
      Then change the following section from this:

      If newVal = "" Then
              'do nothing
            Else
              Target.Value = oldVal _
                & ", " & newVal
            End If

      To this:

      If newVal = "" Then
        'do nothing
      Else
        lOld = Len(oldVal)
        If Left(newVal, lOld) = oldVal Then
          Target.Value = newVal
        Else
           Target.Value = oldVal _
                & ", " & newVal
        End If
      End If
      
  11. Thank you for looking into this but that still does work completely. So I can type and select from the drop down list with it duplicating now but if I move to another cell and than go back to add another name (whether I manually type or pick from the drop down list) it will duplicate what was in the cell. For example, I’m in cell A1 and I type Bob manually, than I select Jeff from the drop down list, which leaves me with Bob, Jeff in cell A1. I then move on to fill in the rest of the template and remember that I forgot to add Joe to cell A1. I go back to cell A1 and whether I manually type or select Joe from the drop down list I get duplicates. So instead of it saying Bob, Jeff, Joe it duplicates it to Bob, Jeff, Joe, Bob, Jeff, Joe. Any suggestions for that?

  12. Actually, the above new variable did work. Sorry I’m new to VBA and didn’t assign it to the sheet I was working in.

  13. Hi, I´m also quite new to VBA. So this code is at the moment for only specific column for Adding multiple options with no duplicates. How should it look like when I want to apply code for entire sheet? Can you help me please? And is there any way I can delete values afterwards in data validation cell?
    Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngDV As Range
    Dim oldVal As String
    Dim newVal As String
    Dim strVal As String
    Dim i As Long
    Dim lCount As Long
    Dim Ar As Variant
    On Error Resume Next
    Dim lType As Long
    If Target.Count > 1 Then GoTo exitHandler
    lType = Target.Validation.Type
    If lType = 3 Then
    Application.EnableEvents = False
    newVal = Target.Value
    Application.Undo
    oldVal = Target.Value
    Target.Value = newVal
    If Target.Column = 3 Then
    If oldVal = “” Then
    ‘do nothing
    Else
    If newVal = “” Then
    ‘do nothing
    Else
    On Error Resume Next
    Ar = Split(oldVal, “, “)
    strVal = “”
    For i = LBound(Ar) To UBound(Ar)
    Debug.Print strVal
    Debug.Print CStr(Ar(i))
    If newVal = CStr(Ar(i)) Then
    ‘do not include this item
    strVal = strVal
    lCount = 1
    Else
    strVal = strVal & CStr(Ar(i)) & “, ”
    End If
    Next i
    If lCount > 0 Then
    Target.Value = Left(strVal, Len(strVal) – 2)
    Else
    Target.Value = strVal & newVal
    End If
    End If
    End If
    End If
    End If
    exitHandler:
    Application.EnableEvents = True
    End Sub

  14. Hi All,
    I am trying to insert a drop down list in excel but I have duplicate txt that I would like to show only once in the drop down. Is there a code I can put or a formula without inserting too many columns or pivot tables? The below is my sample and please note the selection is much larger. Thank you for your help in advance.
    Master Category
    Commodity
    Commodity
    Commodity
    Commodity
    Commodity
    Commodity
    Commodity
    Commodity
    Commodity
    Commodity
    Commodity
    Commodity
    Commodity
    Commodity
    Commodity
    Packaging
    Packaging
    Packaging
    Packaging
    Packaging

  15. I have table like this
    District Crop Block
    TVM Banana A
    TVM Tapioca A
    QLN Banana
    Infact my list is very big consist of around 1500 rows and 6 columns. I need to prepare dependent data validation for all these 6 cloumns which purely depending the next table to the previous drop down

  16. Alert users to duplicates for certain ranges. I have 3 rotas in one worksheet. I have multiple data validation from different named ranges. For each selection, I want the vba to search three different cell ranges for a match to that name. If there is a match, a msg box appears saying this person has already been assigned for…(and list the job that appears in the cell to the left of the matched name) I don’t want to delete the newly selected person or the match as doubling up sometimes is okay, I just want to alert the user that there is a double up.

Leave a Reply to Mark Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.