Identify Duplicates that are not identical

[KFM]

I have two spreadsheets that contain duplicate names of idividuals. One
spreadsheet includes the first name in column "A" and the last name in column
"B". The other spreadsheet contains the entire first and last name in column
"A" in the format; "Lastname, Firstname".

Is there a way for me to identify (not eliminate) these duplicates even
though they are not identical?

Thanks,
KFM

 

[Jacob Skaria]

Suppose in sheet1 you have the combined version

In sheet 2 in cell C1 try the below which will combine the entries and
lookup in sheet1 for a match...

=VLOOKUP(B1 & ", " & A1,Sheet1!A:A,1,0)

If this post helps click Yes

 

[Jacob Skaria]

and with error handling....
=IF(ISNA(VLOOKUP(A1 & ", " & B1,Sheet1!A:A,1,0)),"","Duplicate")

If this post helps click Yes

 

[T. Valko]

One way...

Sheet1
A1 = John
B1 = Smith

Sheet2
A1 = Smith, John

On Sheet1 C1 enter this formula:

=IF(ISNUMBER(MATCH(B1&", "&A1,Sheet2!A1:A10,0)),"Dup","")

 

[Jacob Skaria]

just the reverse
=IF(ISNA(VLOOKUP(B1 & ", " & A1,Sheet1!A:A,1,0)),"","Duplicate")

If this post helps click Yes