0

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)

0

1 Answer 1

1

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.

9
  • 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, 2012 at 21:44
  • Do you implement the task using macro or just manually? Do you have a predefined number of columns?
    – nidu
    Dec 14, 2012 at 21:46
  • yes, predefined columns.
    – James Huckabone
    Dec 14, 2012 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, 2012 at 21:47
  • Deletion is required or hiding is enough?
    – nidu
    Dec 14, 2012 at 21:49

You must log in to answer this question.

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