Comparing two lists of names in Excel and then sorting them

[Closed]
Report
Posts
1
Registration date
Tuesday January 27, 2015
Status
Member
Last seen
January 27, 2015
-
Posts
2806
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 20, 2021
-
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!!

1 reply

Posts
2806
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 20, 2021
482
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