Comparing two lists of names in Excel and then sorting them Closed

Question

Hello,

I am wanting to compare two columns of information containing names from two different excel sheets.   Names that do not appear on both lists should be flagged/highlighted in some fashion and further compared to another list of "exemptions" producing a final clean list of names that do not appear on both lists and are not on the exemption list.

Would this simply be done with a 3rd sheet using a vlookup with iferror and a 4th sheet comparing the errors with the exemption sheet? I feel like there could be a more streamlined way of producing this. 

Any help is appreciated!!

TrowaD · Answer

Hi Atjays,

Take a look at the following example:

List 1 - List 2 - List 3
A2:A4 - B2:B4 - C2:C4
abc - def - ghi
ghi - jkl - mno
stu - vwx - yza

The name you want to cross check is in D2:
=SUM(($A$2:$A$4=D2)+($B$2:$B$4=D2)+($C$2:$C$4=D2))
This is an array formula and needs to be confirmed by hitting Ctrl+Shift+Enter.

Putting abc in D2 will yield 1; 1 occurrence in the 3 lists.
Putting ghi in D2 will yield 2; 2 occurrences in the 3 lists.
Putting pqr in D2 will yield 0; this value is not present in the 3 lists.

The formula is ready to be dragged down for you to enter all the names you want to check in a column.

Best regards,
Trowa

Comparing two lists of names in Excel and then sorting them

1 response

Similar discussions

Subscribe To Our Newsletter!