excel delete row if column contains value from to-remove-list

user1666562 picture user1666562 · Sep 12, 2012 · Viewed 115k times · Source
  1. Let's say that I've got a sheet - number one - with over 5000 rows (say, columns 'A' - 'H' each).
  2. In another sheet - number two - I have a "to-remove-list" - a single column 'A' with 400 values, each containing alphanumerical string (example: xxx1234).
  3. I have to remove every entire row from sheet number one, if column 'E' contains any value from "to-remove-list" (from column 'A' of sheet number two).
  4. By removing the entire row, I mean delete the row and move it up (not leaving the blankspace)

How do I achieve that? Any help would be much appreciated.

Answer

James L. picture James L. · Sep 13, 2012

Given sheet 2:

ColumnA
-------
apple
orange

You can flag the rows in sheet 1 where a value exists in sheet 2:

ColumnA  ColumnB
-------  --------------
pear     =IF(ISERROR(VLOOKUP(A1,Sheet2!A:A,1,FALSE)),"Keep","Delete")
apple    =IF(ISERROR(VLOOKUP(A2,Sheet2!A:A,1,FALSE)),"Keep","Delete")
cherry   =IF(ISERROR(VLOOKUP(A3,Sheet2!A:A,1,FALSE)),"Keep","Delete")
orange   =IF(ISERROR(VLOOKUP(A4,Sheet2!A:A,1,FALSE)),"Keep","Delete")
plum     =IF(ISERROR(VLOOKUP(A5,Sheet2!A:A,1,FALSE)),"Keep","Delete")

The resulting data looks like this:

ColumnA  ColumnB
-------  --------------
pear     Keep
apple    Delete
cherry   Keep
orange   Delete
plum     Keep

You can then easily filter or sort sheet 1 and delete the rows flagged with 'Delete'.