compare two column cell by cell

CalmSoul
CalmSoul used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Top Expert 2015
Commented:
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...
Professor JMicrosoft Excel Expert
Top Expert 2014

Commented:
Saurabh

Do you even sleep? :-)

I see your answers round the clock
Saurabh Singh Teotia
Top Expert 2015

Commented:
@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

Author

Commented:
This is not consistent solution in some cells I got "2" as the value
Can somebody help me with VBA solution ?
Saurabh Singh Teotia
Top Expert 2015

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial