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

by

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’.

Leave a Comment