Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

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?

ie:

   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 stackoverflow.com 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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .