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.
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!
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!
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
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
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
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
=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]
« Older Best prepaid mobile broadband in the U.S. | What's the best solution to this car drama that... Newer »
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