Compare List

  • Thread starter Thread starter mc-iii
  • Start date Start date
M

mc-iii

Please help,

I have two lists in column, no in the same order,

List1 List2

a c
b z
c h
d r
g g

I want to know the elements of the list 1 that are not in the list2 (a
b, d)

and the element od the list2 that are not in the list1 (z,h,r)

Exits any formula for it?

Thank
 
Check out Chip Pearson's web site on the subject:

http://www.cpearson.com/excel/duplicat.htm
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



Please help,

I have two lists in column, no in the same order,

List1 List2

a c
b z
c h
d r
g g

I want to know the elements of the list 1 that are not in the list2 (a,
b, d)

and the element od the list2 that are not in the list1 (z,h,r)

Exits any formula for it?

Thanks
 
In what follows, I'll try to sell a different formula system...

Let A2:B7 house the sample you provided:

{"List1","List2";"a","c";"b","z";"c","h";"d","r";"g","g"}

C1 must house a 0.

C2: Count1

C3, copied down:

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

D1 must house a 0.

D2: Count2

D3, copied down:

=IF((A3<>"")*ISNA(MATCH(B3,$A$3:$A$7,0)),LOOKUP(9.99999999999999E+307,$D$1:D2)+1,"")

E1, copied to F1:

=LOOKUP(9.99999999999999E+307,C3:C7)

E2 and F2 house the labels: Not in List2 and Not in List1

E3, copied across to F3 then down:

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

The results area, E1:F5, will now show:

{3,3;
"Not in List2","Not in List1";
"a","z";
"b","h";
"d","r"}
 
Back
Top