# Turning in my spreadsheet wizard badge.August 2, 2011 10:33 AM   Subscribe

Spreadsheet question: I want a column containing all the names in THAT column EXCEPT all the names in THOSE columns.

Ok, here's the deal.

Cells A2:A93 contain a bunch of unique names.

Cells C2:N15 contain a bunch of names as well as blank cells. Not all names in A2:A93 are in C2:N15, but all names in C2:N15 are in A2:A93.

I would like a formula that magically makes column B contain all the names that ARE in A2:A93 but NOT in C2:N15.

Is this possible? How do I do it? Thank you!
posted by Salvor Hardin to Computers & Internet (6 answers total) 5 users marked this as a favorite

in excel, i'd just do a vlookup, then filter for #N/A

I'm sure there's a better way to do it, though.
posted by empath at 10:49 AM on August 2, 2011

Best answer: COUNTIF is the formula you want to use. In this case, you can put =COUNTIF(C2:N15,A2) in B2 and you will get either a 0 or 1 (in fact you could get higher numbers if you have duplicate values in C2:N15). Copy that formula down column B.

Now you can add an IF Formula that says IF B2=1, then give me the value in A2.
posted by soelo at 10:49 AM on August 2, 2011

ARE in A2:A93 but NOT in C2:N15.

Oh, sorry. In that case you want B2=0 not 1.
posted by soelo at 10:50 AM on August 2, 2011

Response by poster: Excellent! soelo - your idea worked - thanks. The only issue now is that I now have a sparse list of names in column B. Is there a way to display the names in column B stacked up at the top of the column without the blanks interspersed? I thought google's filter functionality would do it, but it isn't working. Anyhow, the man problem is solved - thanks!

empath - thanks for your answer - I'm sure your way would work too, but I'm not clear on how I would use vlookup in this circumstance.
posted by Salvor Hardin at 11:04 AM on August 2, 2011

Response by poster: Ah, nevermind, figured out my second problem. For posterity, in a new column, I added this formula to the first cell:
=SORT(B2:B100)

The new column then contained the names I wanted in column B, but without all the blank cells in between.

Thanks everyone!
posted by Salvor Hardin at 11:16 AM on August 2, 2011

This question is raising my "sending a spreadsheet to do a database's job" red flags.

Beware of mission creep.
posted by flabdablet at 4:41 PM on August 2, 2011 [1 favorite]

« Older Best prepaid mobile broadband in the U.S.   |   What's the best solution to this car drama that... Newer »