Comparing two columns - what formula to use

Ajoy

New member
Joined
Feb 4, 2013
Messages
12
Reaction score
0
Points
0
I am trying to compare two columns - one has 187 cells with data, another has 204 cells with data, entries are text in both the columns, some cells do not have any data and some cells are repeated with same data randomly. I am trying to find out what are the data in second column that is not there in the first one. I am sure that second column has some data that is not there in the first column. Please suggest what formula I should use. Thank you very much for your help!
 

JoePublic

Super Moderator
Staff member
Joined
Sep 16, 2011
Messages
223
Reaction score
2
Points
18
Location
UK
Excel Version(s)
2016
Assuming columns A and B:
=isnumber(MATCH(B1,A:A,0))
will return TRUE if B1 matches a value in column A and FALSE otherwise. You can then copy this down as far as necessary.
 

Ajoy

New member
Joined
Feb 4, 2013
Messages
12
Reaction score
0
Points
0
Hi JoePublic,
Thanks for your prompt response. I tried with the formula but it did not work, it gives value FALSE where ever blanks are there. There are many blanks in both columns and they are spaced out randomly, so it is not possible to get the solution. Is there a way I can define the blanks? Thanks again for your help!

Ajoy
 
Last edited:

JoePublic

Super Moderator
Staff member
Joined
Sep 16, 2011
Messages
223
Reaction score
2
Points
18
Location
UK
Excel Version(s)
2016
Interesting-what response did it give then?
 

Ajoy

New member
Joined
Feb 4, 2013
Messages
12
Reaction score
0
Points
0
It gives TRUE response when what ever is there in B matches with column A, but for any row where both columns have blanks it gives FALSE response.
 

JoePublic

Super Moderator
Staff member
Joined
Sep 16, 2011
Messages
223
Reaction score
2
Points
18
Location
UK
Excel Version(s)
2016
But you said
I am trying to find out what are the data in second column that is not there in the first one
and that is what that formula gives you, is it not?
 

Ajoy

New member
Joined
Feb 4, 2013
Messages
12
Reaction score
0
Points
0
You are correct, but there are many blanks in between and the blanks are randomly spaced. So I am getting too many FALSE and hence cannot find which cells are there in column B that are not there in A. Wish I could attach a sample file for you to see, you would know what I mean.
 

JoePublic

Super Moderator
Staff member
Joined
Sep 16, 2011
Messages
223
Reaction score
2
Points
18
Location
UK
Excel Version(s)
2016
Perhaps a simple change to:
=IF(B1="","",isnumber(MATCH(B1,A:A,0)))
 

Ajoy

New member
Joined
Feb 4, 2013
Messages
12
Reaction score
0
Points
0
Thank you very much, it worked perfectly!
 
Top