Monday, July 8, 2013

Compare two columns: return in a third column what is in B but not in A

Question:

( by Naast )

Hello everyone, this is my first question here.

It's exactly what the title says. I have this document and I'm trying to make a list of characters that are in B but not in A. 

I have next to no knowledge of spreadsheets, but I'd really like to learn more.


Solution:

Have a look at the following screenshot:


Put the following formula in Cell C3:

=query(arrayformula(iferror(if(match(B:B;A:A;0)>0;"");B:B));"select Col1 where Col1<>'' order by Col1")

And make sure that you don't have extra spaces in any of your cells, that is there should not be any cell having extra white space at the beginning or at the end of the text.

Or else try the following formula:

=query(arrayformula(iferror(if(match(trim(B:B);trim(A:A);0)>0;"");trim(B:B)));"select Col1 where Col1<>'' order by Col1")

Above formula has trim function which will take care of the extra spaces of the contents of all cells.


I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.


I also take up private and confidential projects:
If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

7 comments:

  1. Great post. Clear and concise.

    Is there anywhere you can direct me to learning what is going on in that equation. I'd like to tweak it for my needs. It would make experimenting easier if I knew what was happening. I tried a little, but to no avail.

    I have 2 sheets, each with a list of songs (Col A) along with the artist (Col B). I want to compare the two lists and display the differences on a 3rd sheet. However, I want it to display the song title AND artist.

    While I'm sure you could just give me the equation for my situation, I'm looking to use this as a learning opportunity.

    Cheers!

    ReplyDelete
  2. Just wanted to say thanks - this is a very clever and concise formula! Great stuff.

    ReplyDelete
  3. Excellent. Works amazingly...

    ReplyDelete
  4. Thanks, that's what I was looking for!

    ReplyDelete
  5. it says the formula contains error. What should i do??

    ReplyDelete
    Replies
    1. probably only same elements in col A and col B ?

      Delete