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.

I have a spreadsheet. It's a google docs spreadsheet, but I'd be open to using another kind of spreadsheet if what I want just isn't possible on a google docs spreadsheet.

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

Ok, here's the deal.

I have a spreadsheet. It's a google docs spreadsheet, but I'd be open to using another kind of spreadsheet if what I want just isn't possible on a google docs spreadsheet.

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!

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

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

Oh, sorry. In that case you want B2=0 not 1.

posted by soelo at 10:50 AM on August 2, 2011

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

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

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

=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]

Beware of mission creep.

posted by flabdablet at 4:41 PM on August 2, 2011 [1 favorite]

This thread is closed to new comments.

I'm sure there's a better way to do it, though.

posted by empath at 10:49 AM on August 2, 2011