Prevent duplicate entries in multiple columns

In this article, you will see how to prevent duplicate entries in a range using data validation.

You will also see how to allow unique record entry based on the combination of values in multiple columns.

Also, VBA to prevent duplicate entries in a range.

Prevent duplicate entries in a single column

To prevent duplicate entry in a single column or a range first select that range.

Make sure first cell is active in a given selection

Keeping selection intact press ALT D L keys in series (do not keep hold previous key) to bring Data Validation pane.

Alternatively,  go to Data tab and click on Data validation option to bring Data Validation pane.

From Allow drop-down select Custom.

In formula option write the below formula. You can modify formula range as per your requirement.

=COUNTIF($B$3:$B$11,B3)=1

Optionally,  you can write Error Alert message like “Only unique entries allowed.” ,when duplicate entry is done.

Click on OK button. 

If you try to manually enter duplicate value then error message will pop up like the below.

Prevent duplicate entries in multiple columns in Excel

Here, you will see how to prevent duplicate record entry based on combination of more than one column (multiple column) values.

In the below image, I do not want duplicate entry of Class and Roll No record values combined.

First create one column which concatenates values of Class and Roll No.

Fill down formula in the below cells.

Cell B3 formula 

=SUBSTITUTE(CONCATENATE(C3,D3),” “,””)

Select class and roll no range (columns whose values combination must be unique).

Make sure the first cell (leftmost and topmost) is active in a selection. 

Press ALT D L keys in series (do no keep hold perevious key) to bring Data Validation pane.

Alternatively,  go to Data tab and click on Data validation option to bring Data Validation pane.

In Allow drop-down select Custom.

In formula section write the below formula rule

=COUNTIF($B$3:$B$10,$B3)=1

Note that in $B3, B column is absolute. B column is concatenated column.

Write Error Alert message if you want. Click on Ok button.

If you try to manually enter duplicate record then error message will pop up like the below.

Excel VBA to prevent duplicate entries

You can write VBA code in Excel to prevent duplicate entries in a range.

This VBA code auto deletes duplicate value entered.

You can modify range in VBA code as per your requirement.

In Sheet1 , in range B3 to B11 when duplicate value is entered then it will get auto deleted.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
 Application.EnableEvents = False
 Application.ScreenUpdating = False
  On Error GoTo jmp
  Dim entryRng As Range
  ''range in which value must be unique
  Set entryRng = Range("B3:B11")
  If Not Intersect(Target, entryRng) Is Nothing _
  And Target.Value <> "" Then
     If Application.WorksheetFunction. _
     CountIf(entryRng, Target.Value) > 1 Then
         Target.ClearContents
     End If
  End If
jmp:
 Application.EnableEvents = True
 Application.ScreenUpdating = True
End Sub

To use the above code just press ALT+F11 to open VBA IDE. 

In VBA  IDE double click on sheet in which unique entry value range in present.

Copy the above code section in paste in that worksheet module. Make sure to save your workbook either in binary (.xlsb) or in macro (.xlsm) format.

You can modify the below line of the above code section as per your need.

Set entryRng = Range(“B3:B11”)

Make sure sure VBA Event in enabled so that code can run automatically when cell value of the worksheet changes.

To Enable VBA event run the below code line in Immediate window and you are done.

Application.EnableEvents = True