# How to do a simple set operation in Google docs spreadsheet?

Suppose I have some names in cells `B1:B3`, and a full list of all the names in `A1:A4`, is it possible for me to generate a list of names in `A1:A4` that don't appear in `B1:B3`?

E.g. Cells `A1:A4` have "A", "B", "C", "D", if `B1:B3` contain "A", "C", "D", then I want to get "B".

I looked through the function list but couldn't figure out how to do it.

Update: With Lance's help, the formula I came up with was:

``````FILTER(A1:A4, A1:A4<>B1, A1:A4<>B2, A1:A4<>B3)
``````

This gives the right result, however I still have an issue. The problem is that my cells `B1:Bx` can expand. I'm managing a sport team and using a Google Docs form for people to submit, they select their name from a picklist and choose a radio button "Yes" (I can play), or "No" (I'm out). What I want to achieve here is to display in my spreadsheet those people who haven't responded. The goal is to update this list automatically after each new submission. Unfortunately when new rows are filled in the spreadsheet, the formula would be screwed up. I'm not sure if there is a solution for this.

Update 2: I found a work around. I copied the cells from `B1:Bx` to another region `C1:Cx` first, then used the above formula but against `C1:Cx`. Now even `B1:Bx` expands, the formula is still valid.

WOW, I was going to get you an exact solution, Google Docs is so frustrating. Script-loop City.

You can do this with the FILTER worksheet function, with your arrayCondition_1 being a formula of functions that gives a true value for each line that isn't duped. I was able to see that the MATCH function couldn't be used.

``````=FILTER(A:A, NOT(COUNTIF(B:B, A:A)))
``````

`COUNTIF` counts the number of times each player in column `A` appears in column `B` and returns an array of the counts.

`NOT` casts the numbers to boolean values (true or false) and then takes the inverse.

`FILTER` removes the players who did appear in the `B` column response list.

The trick here is that `NOT` casts the counts to booleans. If a player hasn't responded, they will appear 0 times in the list of responses. If they have responded, they'll appear 1 or more times (maybe a player trying to change their answer by submitting the survey a second time). 0 casts to `False` and all other numbers cast to `True`. So players that have responded map to `True` and those yet to respond map to `False`. `NOT` then takes the inverse, giving you a mask of those players yet to respond.

This solution supports a growing list of submissions that may include blank rows or duplicate submissions. To limit to the specific range described in the question use: `=FILTER(A1:A4, NOT(COUNTIF(B:B,A1:A4)))`

Here’s a spreadsheet showing how this can be done easily:

Union is `={setA; setB}`

Difference is `=filter(setA, iferror(MATCH(setA, setB, false)))`

Intersection is `=filter(setA; MATCH(setA, setB, false))`

Explanation `setA` and `setB` can be named ranges or you can use normal range notation. Named ranges just make this clearer.

Union is just a new range made by juxtaposing both ranges.

Intersection (next easiest) depends on looking for indices of `setA` in `setB` where they exits, and filtering setA by that.

Difference is similar, but filters `setA` to pick out only members where finding the index in `setB` is not found.

Extra Credit

Union with duplicate elimination is just setA + (setB-setA), so by the above

``````={setA;filter(setB, iserror(MATCH(setB,setA,false)))}
``````

A small contribution: Deduplication is a little easier than shown above. Just do:

`=UNIQUE(AnyRange or Formula)`

A small contribution member == is_member

is_member(element1, setB)

=iferror(IF(MATCH(element1, setB, 0)>0, 1, 0), 0)

If element1 is not a member then MATCH foobars, hence the iferror