compare two column cell by cell

Hello All:

I have two columns, A and B ..

A
99999999,001
99454999,001
99923999,001
99999d99,001
99999999,091
....

B
99999999,001
9945fd99,001
99923999,001
99999d99,001
9999gg99,091
.............

I want to get list common in both A and B
I want to get list present in A not present in B
I want to get list present in B not present in A

Regards,
cs
Saurabh Singh Teotia

membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Professor J

Saurabh

Do you even sleep? :-)

@Jim... Hahahha... :-) .. I do but unfortunately or fortunately...In that also i'm fast and it only last for couple of hours and i'm back online doing something or other... :-)

Saurabh...

This is not consistent solution in some cells I got "2" as the value
Can somebody help me with VBA solution ?
Calm,

I will provide you a VB solution of this if you want but trust me its more easy to do it with formula..and remeber my last suggestion that i told you I'm assuming your value is only once but in this case your value is present more then once... so use this formula then in C1...

C1=IF(COUNTIF(A:A,B1)>0,1,0)

This will change all your values to 1 from whatever they are and will tell you how many values in B Column exist in A Column..

D1=IF(COUNTIF(B:B,A1) >0,1,0)

This will tell you for A Column how many values present in B Column...

and then E1 remains=Sum(C1:D1)

Any value which is equal to 2 are present in both...

Saurabh...