Matching Name Fields in Two Columns

[chcalissie]

I'm trying to match two columns of names to determine what names in Col A/B are not in Col C/D and vice versa. I need to come up with the list of those in A/B that are not in C/D and those in C/D that are not in A/B. One person suggested concatenating the first and last name columns. But, after that, I'm not sure what to do.

I've attached an example.

Any help is appreciated.

[ragulduy]

The AB/CD columns don't seem to fit your example so I'm assuming you mean columns A/B on the two separate sheets. Try putting:

=IF(ISNUMBER(MATCH(A2&B2,JSA!A$1:A$150&JSA!B$1:B$150,0)),"Found","Not Found")

in E2 of sheet Hotel as an array formula (confirm with ctrl+shift+enter) and copy down.

[Punnam]

Hi ,
I didn't found names in Column C/D .
Are you trying to compare Cou.A/B in Sheet Hotel to Coul.A/B in sheet JSA

Punnam

[chcalissie]

Sorry, I attached the wrong spreadsheet. On the worksheet I attached, it would be comparing A&B on Hotel to A&B on JSA. I tried the formula above with the Text Example spreadsheet and got NOT FOUND for the first name which is on both worksheets. So, what am I doing wrong.

[ragulduy]

Hi - did you enter it as an array formula (confirm with ctrl+shift+enter, not just enter). See attached example.

[Punnam]

Hi,

try this


Punnam

[chcalissie]

You're right. I didn't do the Control+Shift+Enter. It worked now. Thanks. So to find the ones that are on JSA and not on Hotel, I just change the formula around? Please let me know. Thanks again. I was going to attach another file to this thread, but I can't figure out how to attached another file in a reply.

[ragulduy]

Yes, you would just replace the sheet references to reverse the formula.

[chcalissie]

Thanks. I did that and it worked! YEAH!!