• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need help comparing 2 columns of numbers

Chris Martin

New Member
I have 2 columns of numbers (possibly in text format) A is say 200 long and B is say 180 long.

Not all that are in B will be in A and vice versa.

Most numbers will appear in both columns but will not always be in cells directly next to each other.

All I need to know is what numbers appear in column A but not B and what numbers appear in column B but not A.

Please help.
 
Hi,

Use a vlookup formula

Formula in Cell C2 =VLOOKUP(A2,B:B,1,0)
Formula in Cell D2 =VLOOKUP(B2,A:A,1,0)

Copy to last non blank row

#N/A in column C will give available in A but not available in B
#N/A in column D will give available in B but not available in A
 
Thanks that works great, is there any way to tweak it so if any of the cells are blank it just ignores them?

I want to just be able to paste in the 2 rows of data every time I go to use it but the number of rows of data will never be the same.
 
Hi,

Just use a if condition

Formula in Cell C2 =If(A2="","",VLOOKUP(A2,B:B,1,0))
Formula in Cell D2 =If(B2="","",VLOOKUP(B2,A:A,1,0))
 
Great, my next and hopefully last question is when it does find a match instead of displaying the number can it show a blank box? and when it doesn't match show "NS" in the box?
 
Hi

Try Below

Formula in Cell C2 =IF(ISERROR(VLOOKUP(A2,B:B,1,0)),"NS","")
Formula in Cell D2 =IF(ISERROR(VLOOKUP(B2,A:A,1,0)),"NS","")
 
That's great thanks, the only thing is it now puts NS in cells that were blank with the last formulae.

Sorry to be a pain, I do appreciate your help with this.
 
Hi

Missed that, Try Below

Formula in Cell C2 =IF(A2="","",IF(ISERROR(VLOOKUP(A2,B:B,1,0)),"NS",""))
Formula in Cell D2 =IF(B2="","",IF(ISERROR(VLOOKUP(B2,A:A,1,0)),"NS",""))
 
Great, Thanks for all your help.

Once I have this do you know how I can get excel to make a list of numbers from column A that did = NS and a list next to it from column b that did =NS?
 
Back
Top