Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a spreadsheet with multiple columns. The first is the master and the 4 or 5 others contain data that I would like to eliminate from the master. How would I perform this function?


   Column 1       Column 2       Column 3
               |             |              |
test 1         | test 2      | test 2       | 
test 2         | test 4      | test 1       |
test 3         |             |              |
test 4         |             |              |
test 5         |             |              |

I would like to remove test 1,2 & 4 from column 1 because they were found in Column (n)

share|improve this question

migrated from Dec 15 '12 at 18:01

This question came from our site for professional and enthusiast programmers.

up vote 1 down vote accepted

If it should be done manually. Suppose your table starts in A1 and has no headers.

Then I would:

  1. Add an artificial column (for example D) with formula. For D1 it will be =COUNTIF($B$1:$B$5;A1)+COUNTIF($C$1:$C$5;A1), fill down other cells in the column.
  2. Add filter to the table via Home -> Editing -> Sort & Filter -> Filter. Dropdown buttons will appear on column top cells.
  3. Switch off all values except 0 from artificial column using dropdown button (apply filter).

How it works: every COUNTIF finds the count of occurrences of values from A column in values in either B or C columns. If value from A is in B or C the COUNTIF returns non 0 value. So we just have to exclude these non 0 values.

share|improve this answer
I don't understand your answer ... at all. I don't even know what to say to get you to clarify. Ok, I'll try... 1) What is an artificial column? 2) What is and why am I adding a filter? 3) "switch of 0 values from artificial column" ... huh? 4) "If it should be done manually" ... huh? 5) "using vlookup" ... huh/how? – James Huckabone Dec 14 '12 at 21:44
Do you implement the task using macro or just manually? Do you have a predefined number of columns? – nidu Dec 14 '12 at 21:46
yes, predefined columns. – James Huckabone Dec 14 '12 at 21:46
basically just want a filter applied to column 1 that checks if the value exists in other columns and then deletes it from column 1 if found ... – James Huckabone Dec 14 '12 at 21:47
Deletion is required or hiding is enough? – nidu Dec 14 '12 at 21:49

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.