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

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.

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 !! Jul 4, 2015 at 19:17
• Thanks. I added a sample link and some further explanation.
– John
Jul 5, 2015 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, 2015 at 16:18

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, 2015 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, 2015 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/… Jul 5, 2015 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, 2015 at 20:19
• Note that this will leave duplicates in, which is exactly the behavior I wanted. A query will not leave in duplicates. Jun 28, 2021 at 20:07

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.