Compare List

[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

 

[JulieD]

Hi

Check out Chip Pearson's website for ideas on working with duplicates
http://www.cpearson.com/excel/duplicat.htm

Cheers
JulieD

 

[RagDyer]

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

 

[Aladin Akyurek]

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$12)+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"}