• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Howto prevent duplicate entries with three columns

Murli

New Member
I want to prevent duplicate entries in three columns combined using data validation, i.e. for example column A has first name and Column B has middle name, Column C has last name. The first name can be duplicate, middle name can be duplicate, last name can be duplicate, but not all three combined at the same time.


i.e. records in excel sheet should have unique entries for FirstName,MiddleName,Surname combination


Awaiting pointers, whereby above scenario is addressed.
 
Note that you should try to limit the size of the arrays in this formula. Using the entire column could get calculation intesive.

Starting with row 2

Code:
=SUMPRODUCT(--($A2&$B2&$C2=$A$2:$A$200&$B$2:$B$200&$C$2:$C$200))<2
 
Dear Murli,

At first join three columns in a column using & or CONCATENATE

Now prepare a pivot table and check duplicate names. It is easy process.


Regards,

Muneer
 
Hi Murli,


If it is just sample [but your data is about something else] then your premise might be correct. But different people can have the same names, and preventing the entry may then prove more of headache if the situation comes.


So you may have to add something more to make it unique key / combination e.g. address or phone number etc.


That said, you can use conditional formatting using formula approach posted by Luke M!
 
Thanks for the response Luke as well as Muneer. Shrivallabha your are right, that I would have to add one more field to get the unique key.
 
Back
Top