finding duplicate names in 2 sheets

[markman235]

Hi everyone,

I have 2 separate sheet. Sheet 1 has Last Name, First Name in columns A & B respectively.

On Sheet 2 I have First Name, Last Name in columns A & B respectively.

Is there any easy way to check for duplicate first and last names between the two sheets?


Thanks!

Mark

 

[JoeMo]

One way would be to concatenate the names in a helper column on each sheet then compare the two helper columns. Something like this:

On Sheet1 with say Last name in col A and First name in col B, then in col C:

=B1&"|"&A1

and copy down.
On Sheet2 (reversal)

=A1&"|"&B1

and copy down.
Then on both sheets in col D:

=IF(ISNA(MATCH(C1,Sheet2!$C$1:$C$100,0)),"NM","")

change the range to cover all your names and change the sheet name to the sheet you want to check for a match.
Names that have no match will have "NM" in col D while those that are matched will have nothing in col D.

 

[markman235]

You are awesome!!! Thank you so much!

 

[JoeMo]

You are awesome!!! Thank you so much!

You are welcome - thanks for the reply.