Previous Topic Next Topic

Comparing two lists of names in Excel and then sorting them

Closed
atjays Posts 1 Registration date Tuesday 27 January 2015 Status Member Last seen 27 January 2015 - 27 Jan 2015 à 17:43
TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 - 2 Feb 2015 à 11:27
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!!
Related:

1 response

Réponse 1 / 1
TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 555
2 Feb 2015 à 11:27
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