Link to home
Start Free TrialLog in
Avatar of CalmSoul
CalmSoulFlag for United States of America

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of Saurabh Singh Teotia
Saurabh Singh Teotia
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Professor J
Professor J

Saurabh

Do you even sleep? :-)

I see your answers round the clock
@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...
Avatar of CalmSoul

ASKER

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...