change language:

In cases where it is necessary for a user to enter unique values ​​in Excel and therefore no duplicate values ​​may occur, this can be enforced using data validation. Suppose column A contains the following data:

prevent duplicate values 1

In this case it is preset that no duplicate values ​​can be entered in A2:A100. This is done as follows. Make sure the input column is empty. Then from A2 select cells A2:A100 and choose the option Data Validation (this option can be found on the ribbon at the Data tab, but this can differ per Excel version). Then in the Data Validation screen, under the Settings tab, select at 'Allow:' the option Custom:

prevent duplicate values 2

At 'Formula:' the following formula must then be entered:

=COUNTIF(A$2:A$100;A2)=1

In this case, the input is checked for duplicate values ​​for A2 through A100. This can of course be adjusted. Optionally, a specific error text can be entered on the Error message tab that will appear when the user enters a double value. Then choose OK and the check for entering duplicate values ​​is set for the cell range A2:A100.

If, for example, in the example above in cell A11 the value 5 is entered, then that is not possible and the following error message appears:

prevent duplicate values 3

This message also appears if an attempt is made to change an already entered value to one that already exists.

This solution works fine in most cases, but data validation does have some limitations. For example with 'copy - paste' or Ctrl+D you could still enter duplicate values ​​without an error message. With VBA you could optimize this even further by turning off Ctrl + D for the workbook and preventing copying and pasting for that cell range. But then it is better to choose a full VBA approach.

First give cell range A2:A100 the name 'no_duplicates' and then put the code below in the Worksheet_Change event of the relevant worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range, bUndo As Boolean
    If Not Intersect(Target, Range("no_duplicates")) Is Nothing Then
        Set rng = Intersect(Target, Range("no_duplicates"))
        bUndo = False
        If rng.Cells.Count > 1 Then
            MsgBox "It is not allowed to change multiple cells at the same time!", vbCritical, "Multiple cells changed"
            bUndo = True
        ElseIf Application.CountIf(Range("no_duplicates"), rng.Value) > 1 Then
            MsgBox "The entered value already exists and duplicate values are not allowed!", vbCritical, "Duplicate value"
            bUndo = True
        End If
        If bUndo Then
            Application.EnableEvents = False
            Application.Undo
            Application.EnableEvents = True
        End If
    End If
End Sub

This piece of code also prevents the user in that range from changing multiple cells at the same time.

Questions / suggestions

Hopefully this article helped you to prevent entering duplicate values. If you have any questions about this topic or suggestions for improvement, please post a comment below.

Comments  
# Vishal Arora 2021-02-18 15:58
This code gives error method undo of object _application failed
Reply
# Manfred van den Noort 2021-02-20 10:53
I can't reproduce this error. Which Excel version are you using?
Reply
arrow_up