Data validation - prevent duplicates

[kiwikiki718]

Hi I have a dropdown list I created using the data validation option based on list I have in another sheet.

I was wondering if I could also prevent users from selecting a duplicate value from the dropdown list within the same cells.

 

[kiwikiki718]

Hi I have a dropdown list I created using the data validation option based on list I have in another sheet.

I was wondering if I could also prevent users from selecting a duplicate value from the dropdown list within the same cells.

For example

Cells A1:A5 I want to prevent users from selecting a duplicate vaule.

Cells A10:A15 I want to prevent users from selecting a duplicate value.

Cells A20:A25 I want to prevent users from selecting a duplicate value.

 

[Skyybot]

Unless there are duplicates in the list that is used for the Validation, they can't "Select" duplicates.

 

[kiwikiki718]

Unless there are duplicates in the list that is used for the Validation, they can't "Select" duplicates.

There are no duplicates in the list being used for data validation. However from the dropdown list.l, I am able to select duplicate values for each cell.

 

[Skyybot]

That's kinda the point of a drop down list. Do you want to create a list where if someone picks a value, that value will be subtracted from the list?

 

[kiwikiki718]

That's kinda the point of a drop down list. Do you want to create a list where if someone picks a value, that value will be subtracted from the list?

Yes that will work as well. So for range A1:A5 subtracted from the list.

Range A10:A15 which uses the same list prevent duplicates within that range etc.

 

[Skyybot]

If so, that will take some serious VBA coding. And data structure knowledge would be necessary. Maybe @Jaafar Tribak can help.

 

[kiwikiki718]

If so, that will take some serious VBA coding. And data structure knowledge would be necessary. Maybe @Jaafar Tribak can help.

I was thinking something less complex with just throwing an error message.

 

[kiwikiki718]

I was thinking something less complex with just throwing an error message.

I know it can be done using the data validation but was unsure of how to input the formula since I am already utilizing the data validation for the dropdown list.

 

[Jaafar Tribak]

You can use the worksheet change event ... something along these lines:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    Const TARGET_RANGE_ADDRESS = "A1:A5,A10:A15,A20:A25"

    On Error GoTo RestoreAppEvents
    With Application
    If Not (.Intersect(Target, Range(TARGET_RANGE_ADDRESS)) Is Nothing Or IsEmpty(Target.Cells(1&))) Then
        If IsDuplicate(Range(TARGET_RANGE_ADDRESS), Target.Text) Then
            .EnableEvents = False
            .Undo
            MsgBox "Duplicates not allowed in range : " & TARGET_RANGE_ADDRESS, vbCritical
        End If
    End If
RestoreAppEvents:
    .EnableEvents = True
    End With

End Sub

Private Function IsDuplicate(ByVal Rng As Range, ByVal InputVal As String) As Boolean
    Dim i As Long, n As Long
    For i = 1& To Rng.Areas.Count
        n = n + Evaluate("=COUNTIF(" & Rng.Areas(i).Address & ",""" & InputVal & """)")
    Next i
    IsDuplicate = n > 1&
End Function