2 Easy Methods to Prevent Duplicate Values in a Column in Your Excel Workbook

2 Easy Methods to Prevent Duplicate Values in a Column in Your Excel

If you want to avoid entering duplicate values in a column in Excel, you can refer to this article. Here we will share you 2 simple approaches.

At times, when you edit an Excel worksheet, you may want to ensure the data in a column are unique, namely preventing duplicate values in a column. It demands Excel to auto alert you every time when you enter duplicate values in a column. Thereinafter, we will share you 2 quick means to get it.

Method 1：Prevent via “Data Validation”

1. First off, select all the cells in a specific column, such as “Column A”.
2. Then, turn to “Data” tab and click on “Data Validation” button in “Data Tools” group.
3. Next, in the popup dialog box, on “Settings” tab, select “Custom” option from the drop down list of “Allow”.
4. After that, input the following formula in the “Formula” field.

=COUNTIF($A$1:$A$20,A1)=1

5. Later, switch to “Error Alert” tab in the current dialog box.
6. Subsequently, you can customize an error message as per your needs, such as specifying “Duplicate Values” as error title and “The value has been entered in the same column!” as error message.
7. Finally, click “OK”.
8. Now, you can try it.

• Firstly, enter a value in Cell A1.
• Then, input the same value in Cell A2.
• At once, you’ll receive the error message, like the following screenshot.

Method 2: Prevent with Excel VBA

1. To begin with, trigger Excel VBA editor with reference to “How to Run VBA Code in Your Excel“.
2. Then, put the following code into a sheet’s project, like “Sheet 1 (Sheet 1)”

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim strTargetColumn As String
   Dim nTargetRow As Integer
   Dim nLastRow As Integer
   Dim strMsg As String

   strTargetColumn = Split(Target.Address(, False), "$")(0)
   nTargetRow = Split(Target.Address(, False), "$")(1)
   nLastRow = ActiveSheet.Range(strTargetColumn & ActiveSheet.Rows.Count).End(xlUp).Row

   For nRow = 1 To nLastRow
       If nRow <> nTargetRow Then
          If ActiveSheet.Range(strTargetColumn & nRow).Value = Target.Value Then
             strMsg = "The value has been entered in the same column!"
             MsgBox strMsg, vbExclamation + vbOKOnly, "Duplicate Values"
             Target.Select
             Exit For
          End If
       End If
   Next
End Sub

3. Since then, every time when you enter a value in any cells, no matter in any columns, the macro will auto check the other values in the same column. If it finds the same values, you’ll get an alert, as shown in the following figure.

In case of any query or issues please comment or mail on Gauravj75@gmail.com