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


--
mc-iii
------------------------------------------------------------------------
mc-iii's Profile: http://www.excelforum.com/member.php...o&userid=10709
View this thread: http://www.excelforum.com/showthread...hreadid=277809

Hi

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

Cheers
JulieD

"mc-iii" wrote in message
...

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


--
mc-iii
------------------------------------------------------------------------
mc-iii's Profile:
http://www.excelforum.com/member.php...o&userid=10709
View this thread: http://www.excelforum.com/showthread...hreadid=277809

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!
==============================================


"mc-iii" wrote in message
...

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


--
mc-iii
------------------------------------------------------------------------
mc-iii's Profile:
http://www.excelforum.com/member.php...o&userid=10709
View this thread: http://www.excelforum.com/showthread...hreadid=277809

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"}

mc-iii Wrote:
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
------------------------------------------------------------------------
Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=277809