Avatar of CalmSoul
CalmSoul
Flag 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
Microsoft ExcelMicrosoft OfficeVBA

Avatar of undefined
Last Comment
Saurabh Singh Teotia

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Saurabh Singh Teotia

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Professor J

Saurabh

Do you even sleep? :-)

I see your answers round the clock
Saurabh Singh Teotia

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

ASKER
This is not consistent solution in some cells I got "2" as the value
Can somebody help me with VBA solution ?
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Saurabh Singh Teotia

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