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

48
votes
  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.

3
excelexcel-2007office-2007
can you add a column to sheet one that does a VLookup into sheet two. By flagging all the records with VLookup, you could sort the rows in sheet one and then highlight / delete the flagged rows. – James L.
awesome James L. thanks a lot for this idea! – user1666562
I'll form the idea into an answer with a simple example. – James L.

3 Answers

74
votes

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

13
votes

I've found a more reliable method (at least on Excel 2016 for Mac) is:

Assuming your long list is in column A, and the list of things to be removed from this is in column B, then paste this into all the rows of column C:

= IF(COUNTIF($B$2:$B$99999,A2)>0,"Delete","Keep")

Then just sort the list by column C to find what you have to delete.

9
votes

Here is how I would do it if working with a large number of "to remove" values that would take a long time to manually remove.

  • -Put Original List in Column A -Put To Remove list in Column B -Select both columns, then "Conditional Formatting"
    -Select "Hightlight Cells Rules" --> "Duplicate Values"
    -The duplicates should be hightlighted in both columns
    -Then select Column A and then "Sort & Filter" ---> "Custom Sort"
    -In the dialog box that appears, select the middle option "Sort On" and pick "Cell Color"
    -Then select the next option "Sort Order" and choose "No Cell Color" "On bottom"
    -All the highlighted cells should be at the top of the list. -Select all the highlighted cells by scrolling down the list, then click delete.