Previous Topic Next Topic

Comparing two lists of names in Excel and then sorting them

Closed
atjays Posts 1 Registration date Tuesday January 27, 2015 Status Member Last seen January 27, 2015 - Jan 27, 2015 at 05:43 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 - Feb 2, 2015 at 11:27 AM
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

Answer 1 / 1
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 545
Feb 2, 2015 at 11:27 AM
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
0