Unique ID Verification

1. Unique ID Verification

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!

2. Re: Unique ID Verification

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

3. Re: Unique ID Verification

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"?

4. Re: Unique ID Verification

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!

There are currently 1 users browsing this thread. (0 members and 1 guests)