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, cl As Range, bDuplicate 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
            For Each cl In rng.Cells
                If Application.CountIf(Range("no_duplicates"), cl.Value) > 1 Then
                    bDuplicate = True
                End If
            Next
            If bDuplicate Then
                MsgBox "One or more of the entered values already exists and duplicate values are not allowed!", vbCritical, "Duplicate value"
                bUndo = True
            End If
        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 checks for duplicate values if a user copies multiple values at once.

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  
# Al 2023-01-17 10:56
Thanks for this - the code works great but I don't want it to prevent the user from changing multiple cells at the same time. I can't work out how to remove this from the code - can you help?
Reply
# Manfred van den Noort 2023-01-17 17:30
Please try this:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, bUndo As Boolean, cl As Range, bDuplicate 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
For Each cl In rng.Cells
If Application.CountIf(Range("no_ duplicates"), cl.Value) > 1 Then
bDuplicate = True
End If
Next
If bDuplicate Then
MsgBox "One of the entered values already exists and duplicate values are not allowed!", vbCritical, "Duplicate value"
bUndo = True
End If
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
Reply
# Al 2023-01-18 10:12
Wonderful, huge help, thanks so much
Reply
# Manfred van den Noort 2023-01-20 12:16
You're welcome.
I have added this piece of code to the text of this article.
Reply
# Vicky Brown 2021-08-02 09:09
I have 10 worksheets the same (different areas) that all have the same column that can only have one value (no duplicates) in any of them together. Can this code be changed to include all of the worksheets. Thank you
Reply
# Manfred van den Noort 2021-08-03 19:10
Yes, that is possible.
You can extend the data validation formula as follows:
=COUNTIF(A$2:A$100,A2)+COUNTIF(Sheet2!A$2:A$100,A2)+....=1
Reply
# Vicky Brown 2021-08-04 09:06
Thank you. Worked great.
Reply
# 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