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.

Comparison

Get Back Precious Excel Data

Excel is admittedly prone to crash. And every time when it crashes, the currently opened Excel file can get corrupted like a breeze. Hence, in order to avoid losing valuable Excel data, not only should you back up them on a regular basis, but also you have to get hold of a remarkable xls recovery tool, such as DataNumen Excel Repair. It will come in handy in the event of severe Excel data corruption.

Author Introduction:

Shirley Zhang is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including corrupted mdf and outlook repair software products. For more information visit www.datanumen.com