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

Question

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.

Hawkinson

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. – Melia

awesome James L. thanks a lot for this idea! – Hawkinson

I'll form the idea into an answer with a simple example. – Melia

Answer 1

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

Melia

Not sure if this will help anyone else, but I had to write it like =IF(ISERROR(VLOOKUP(A1,Sheet2!$A$3:$A$7,1,FALSE)),"Keep","Delete") – Trochaic

@Trochaic The $ sign makes the selected area fixed so it doesn't "move away" when you drag down or copy-paste the cell. – Geologize

The formula on Sheet1 can reference the range in Sheet2 three ways: 1) the full column via A:A, 2) a fixed range of cells via $A$3:$A$7, 3) create a named region in Sheet2 to use in the formula in Sheet1. #1 performs the worst, #2 and #3 are more difficult to maintain if the data changes on Sheet2. If you don't use the full column A:A syntax, then you must use the $ to peg the range, or must use a named region. Doing this makes the formulas on Sheet1 reference the same range on Sheet2 when you copy/paste the formula on Sheet1 to multiple cells. – Melia

Answer 2

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.

Krall

Answer 3

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.

Fleawort

Answer 4

For a more modern answer, bring the data into powerquery, merge the 2nd sheet into the first with a left outer join. Expand. Use drop down filter to remove any rows that don't match as null. Remove test column and file close and load back to excel

Afterclap

Answer 5

New Answer 9/28/2022

Now you can use FILTER function that simplifies it.

=FILTER(A3:B7, ISNUMBER(MATCH(A3:A7,D3:D4,0)))

Note: The question requires to modify the original data sheet, this is in a general not recommended, because you are altering the input, better to have a working sheet with the transformations required.

Reardon