Using query to find the entries in one column which are not in the other

Question

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.

Answer 1

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.

Answer 2

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

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