Excel Comparing two columns

[WCS_MarkP]

HI,
I have two pairs of columns that contain data from different sources. I need to compare the two pairs and get rid of any that don't appear in both pairs (they are first names and surnames). The issue is that there are several duplicate surnames in the surname columns which I think may be causing the issue. I've tried various different ways but the results are either plain wrong or inconsistent.
I've also tried combining the first and second names to fullnames and then comparing the two fullname columns but this is also not working.
What I would like is an output for each name that says something like "John Smith is only in AD"," John Smith is only in Library" or "John Smith is in both"
The first (pair of) columns, AD, has more entries than the second (pair of) columns.
I know this should be simple but I can't find a way.

Thanks for listening
Mark P.

 

[AmjiBhai]

Greeting.
Attached please find an excel file ...Please follow the following steps
1. copy AD data in the sheet named as AD (paste on A1...no header please)
2. copy Library data in the sheet named as Library (paste on A1...no header please)
3. Run the interface it will make a master list for you ensuring "no-duplicates"