Comparing two lists (ListA vs ListB)

  • Thread starter Thread starter RJS
  • Start date Start date
R

RJS

Given two lists of names (ListA and ListB), I'd like to
find which names from ListA *ARE NOT* in ListB.

ListA ListB
----- -----
Joe Joe
Jim Frank
Amy Andy
Sue

Put another way - Is there a way in excel to compare these
two lists in a way that results in:
ListComparison:
---
Jim
Amy

I can't comprehend VLOOKUP and MATCH doesn't seem to work
either.

Thanks for any help,
--Bob
 
Let A2:B6 house your sample

{"ListA ","ListB";"Joe","Joe";"Jim","Frank";"Amy","Andy";"","Sue"}

In C2 enter: 0 (which is required)

In C3 enter & copy down:

=IF((A3<>"")*ISNA(MATCH(A3,B:B,0)),LOOKUP(9.99999999999999E+307,$C$2:C2)+1,"
")

In E1 enter:

=LOOKUP(9.99999999999999E+307,C:C)

In E2 enter: ListA-ListB (which is just a label)

In E3 enter & copy down:

=IF(ROW()-ROW($E$3)+1<=$E$1,INDEX(A:A,MATCH(ROW()-ROW($E$3)+1,C:C)),"")

Note that the ROW($E$3) bit anchors this formula to E3, the first cell in
which the formula is entered.

The results range in column E will now have the desired list:

{2;"ListA-ListB";"Jim";"Amy"}
 
Back
Top