Monday, 13 August 2012

How to Prevent Duplicate Entries in a Column with Excel Data Validation

PRIMARY KEY In Excel Custom Validation to Prevent Duplicate Entries in a Column
A Primary key Column is a column with a unique values for each row. The purpose is to bind data together, across tables, without repeating all of the data in every table.

Suppose In the "EMPLOYEE TABLE" table , the "EMP_ID" column is the primary key, meaning that no two rows can have the same Employee ID. The Employee ID distinguishes two persons even if they have the same name.


Suppose If you try to enter duplicate values in "EMP_ID" Column as "EMP_123" or "emp_123" in any cell Above or Below of already entered value "Emp_123" , then you will get the following “error alert” message.


How to Set Up Validation :
You can declare EMP_ID column as a Primary Key in Excel by using the Custom Data Validation as below:

>> First select entire empty column(a column with no input).
>> Next go to  DATA > DATA VALIDATION
>> Next choose CUSTOM from Allow Drop down list and write the following formula in the Formula Box.

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




This means each cell beginning from A1 to entire range of A column allows only unique values.

Note: 
This validation works for both Text and Numbers.
It will restrict the user to enter the Duplicates above or below the existed value.
You can also use different custom validations as per you requirement.

Please tell about your requirement.
I will try to help you as per your requirement.

Thanks.,
Tamatam

6 comments:

  1. how to make string as a primary key in excel

    ReplyDelete
  2. When I use this formula, the check is only looking at rows before the current row. subsequent rows are allowed to be duplicate.

    ReplyDelete
  3. Hi,

    I just want to say that this was extremely helpful and quick!!

    Thank you!

    ReplyDelete
  4. HOW TO MODIFY THE ALERT BOX ??

    ReplyDelete
  5. Indeed, this was extremely helpful and quick.

    Thank you so much!

    ReplyDelete
  6. How to find which columns are having Unique Constraints in Excel ??

    ReplyDelete

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts