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:
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:
At 'Formula:' the following formula must then be entered:
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:
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.