Sign up ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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.

share|improve this question
The solution for the expanding problem is to use a named range. It'll probably take some work to get the filter just right though. – Lance Roberts Oct 1 '10 at 0:57
@Lance, I found a workaround and updated my post again. Thanks. – grokus Oct 1 '10 at 13:08
Great question, I found it while searching for this answer myself. Your workaround is pretty good, but I think I finally got exactly what you were looking for. Check out my answer below. – Anthony DiSanti Jul 16 at 5:04

2 Answers 2

up vote 3 down vote accepted

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.

share|improve this answer
I found using Chrome is much better when dealing with Google Docs. – grokus Sep 30 '10 at 21:18
Today is crazy for me, but if I can remember I'll take a crack at this next week (if the answer hasn't been found by then). – Lance Roberts Sep 30 '10 at 21:48
it's OK, I was able to use FILTER to get the results I wanted. Thanks. – grokus Sep 30 '10 at 21:55
@grokus, great, you should post the formula you used for future reader's reference. – Lance Roberts Sep 30 '10 at 22:41
I updated my original post. – grokus Sep 30 '10 at 23:29

COUNTIF counts the number of times each player (A1 through A4) appears in column B (supporting new submissions) and returns an array of the counts.

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

FILTER removes the players who did appear in the 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. If you'd like to be able to add people to the team roster without updating the formula, try this: =FILTER(A:A, NOT(COUNTIF(B:B,A:A)))

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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