5

I am using Google Sheets to track changes in a group's roster. Cells B2:B240 contain the old list of names, and C2:C240 contains the new list. Both lists are alphabetized. There is a lot of overlap between the two lists, so I want to identify new members (i.e. present in C but not in B) using the QUERY tool. I did find a way to check for differences on an individual row level, but that method is inconvenient for my spreadsheet layout.

Here is a sample spreadsheet: https://docs.google.com/spreadsheets/d/1e7bEZtcmwEKvEef795sAam3lv3CMu6MlqyhHiTWr214/edit?usp=sharing

Essentially, I want column D to list all the names that are in C but not B. That will give me a list of all the players who joined, and I can use the same logic to find those who left (i.e. in B but not C). Column D in the sample has this formula:

=query(B2:C240, "select C where C<>B")

It gives me the first unique entry, but also lists all the other entries where B and C don't match up because of the offset.

  • I don't understand what you are looking for John. The QUERY formula you posted, works just fine and is way more easy then the formula 1999 posted. Do you want to have only one entry? Please share an example doc with us that shows the expected outcome, so we can have a better understanding of the problem. Welcome on Web Applications !! – Jacob Jan Tuinstra Jul 4 '15 at 19:17
  • 1
    Thanks. I added a sample link and some further explanation. – John Jul 5 '15 at 8:04
  • @JacobJanTuinstra Of course it's way easier because it only checks equality within each row. That's N operations. But the OP wants to check each entry in C against the entire B column. That's N^2 running time, so with N=240 it's probably going to take a little while to do in the Sheets. – user79865 Jul 5 '15 at 16:18
2

This can be done with FILTER by MATCH:

=FILTER(C2:C240,ISERROR(MATCH(C2:C240,B2:B240,0)))

Here, MATCH returns an error if the element is not found in column B (the third argument, 0, requires exact match). The command ISERROR returns true in such a case, and this is what the column C is filtered on.


In SQL, this would be implemented using, e.g., select ... where not exists ... The query language available in Sheets is not powerful enough for that.

  • Yes, I used similar logic to check each cell, but it's time-consuming and requires me to copy the formula down the entire list, then scroll down and find the new names. A query command would be much more efficient for my purposes. – John Jul 5 '15 at 8:04
  • I didn't have to copy my formula, it's only entered in one cell. Also, I don't think that query is capable of what you want to do. – user79865 Jul 5 '15 at 15:57
  • Hi 1999, I based my comments on the sample file I prepare. Have a look if you will: docs.google.com/spreadsheets/d/… – Jacob Jan Tuinstra Jul 5 '15 at 19:33
  • Thank you both-- Jacob, your spreadsheet made things much clearer, and I was able to adopt your formula to do what I needed. – John Jul 5 '15 at 20:19
1

There are some reasons why you might want to use QUERY rather than Filter and Match. You can get around the language limitations of Googlesheets Query by using a JOIN.

That original question spreadsheet isn't working for me, so here is another example. I've kept the column names the same as the original question so the query is the same:

There are two columns

table example

You need something like this

query(C2:C8,"select C where C!='Thursday' and C!='Friday' and C!='Saturday'")

...which won't scale particularly well. So you can replace the where with a JOIN (spreadsheet "join" rather than the SQL one) to append as much as you need to that where.

query(C2:C8,"select C where C<>'"&JOIN("' and C<>'",D2:D10)&"'"&"")

That will make your WHERE have as many bits as you need. Make sure you're careful with appending the spreadsheet join to the end of the query - it is easy to mess up. In Sheets when you highlight just the join part you get a preview of just the result of the JOIN.

JOIN syntax is here https://support.google.com/docs/answer/3094077

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.