Unique ID Verification

**jdot** · 05-01-2013, 08:19 PM

Hello All,

I am working with a set of data with 36k+ rows.

Each row has a name and an ID number assigned. The names appear multiple times.

example:
John 1
John 1
John 1
Jack 2
Jack 2
Tom 3
Bill 4
Bill 4
Bill 4
Bill 4
Bill 4
Bill 4

I am trying to verify my data to ensure that each name is only assigned an ID once. I do not have any duplicate people as in John is John. Another John that shows up MUST be the person.
John 1
John 1
John 1
Jack 2
Jack 2
Tom 3
Bill 4
Bill 5
Bill 4

So from the data set above, Bill is assigned ID#4 and ID#5. Is there a formula or something on excel that when that occurs, it's highlighted or some text can be used in a formula? That way I can filter this third column to find the issue.

John 1 N
John 1 N
John 1 N
Jack 2 N
Jack 2 N
Tom 3 N
Bill 4 Y
Bill 5 Y
Bill 4 Y

OR

John 1 N
John 1 N
John 1 N
Jack 2 N
Jack 2 N
Tom 3 N
Bill 4 N
Bill 5 Y
Bill 4 N

Either way works, just a way for me to easily and quickly identify duplicate ID's assigned to someone. Thanks in advance!

**rylo** · 05-01-2013, 08:28 PM

Hi

Say your data is in the range A1:B9, then try
C1: =COUNTIF($A$1:$A$9,A1)=COUNTIFS($A$1:$A$9,A1,$B$1:$B$9,B1)
Copy down to C9, and do a search on the FALSE

HTH

rylo

**benishiryo** · 05-01-2013, 08:31 PM

hi jdot. another alternative, assuming data starts from A2:B10
=COUNTIFS($A$2:$A$10,A2,$B$2:$B$10,"<>"&B2)>0

or if you are using Excel 2003 & below (please update your excel version in your profile. would be useful for us to know):
SUMPRODUCT(($A$2:$A$10=A2)*($B$2:$B$10<>B2))>0

i wouldnt go with your 2nd method because what if Bill with ID 4 & 5 appeared twice each. which is "y" & "n"?

**jdot** · 05-01-2013, 08:58 PM

rylo, I went with your suggestion since it showed up first. It slowed my excel down at first, but when it was done calculating, it was exactly what I was looking for! Thank you so much!