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.

_____________

13 comments to No Duplicates in Multiple Selection Excel Drop Down

  • 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!

  • Hugo

    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.

  • @Hugo, yes, if you're going to have trainer names below the table, then you'll have to restrict the COUNTIF function to the table range.

  • Contextures Blog » Excel Drop Down Multiple Select or Remove

    [...] prevent duplicates in multiple selection column [...]

  • John

    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

  • Phil

    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

  • Andreas

    Hi

    Could someone upload the code to prevent duplicates from being enterred in the same cell.

    /A

  • Dustin

    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

  • Mark

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

  • Ray

    Thanks for the add Phil. However you code get's syntax errors for the below lines. Any thoughts?

    If oldVal "" Then
    If newVal "" Then

  • ChrisC

    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

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>