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
LVL 5
CalmSoulAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
IN C Column

Apply this formula..

C1=COUNTIF(A:A,B1)

And Just drag it to your last cell of the data of A Column, This will tell you all the values of B Column which are present IN A Column...

Similarly in D1=COUNTIF(B:B,A1)        and drag it till end

This will tell you all the values of A Column Values which are present in B Column as their count will be 1 if present otherwise 0.

In E Column E1=SUM(C1,D1)

For all those values whose count is 2 you can check that these values are present in both the column...

Again i'm working on the logic that value is present only once not more then once..As the countif shows how much times the value is present in the necessary column...

Saurabh...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Professor JMicrosoft Excel ExpertCommented:
Saurabh

Do you even sleep? :-)

I see your answers round the clock
Saurabh Singh TeotiaCommented:
@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...
CalmSoulAuthor Commented:
This is not consistent solution in some cells I got "2" as the value
Can somebody help me with VBA solution ?
Saurabh Singh TeotiaCommented:
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...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.