Comparing two columns - what formula to use

A

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!
 
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.
 
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:
Interesting-what response did it give then?
 
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.
 
But you said
I am trying to find out what are the data in second column that is not there in the first one
Click to expand...
and that is what that formula gives you, is it not?
 
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.
 
Perhaps a simple change to:
=IF(B1="","",isnumber(MATCH(B1,A:A,0)))
 
Thank you very much, it worked perfectly!
 
You must log in or register to reply here.
Back
Top