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

  • 4
    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. Sep 12 '12 at 17:45
  • awesome James L. thanks a lot for this idea! –  Sep 12 '12 at 20:06
  • I'll form the idea into an answer with a simple example. – James L. Sep 12 '12 at 22:22

5 Answers5

77

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

James L.
  • 9,384
  • 5
  • 38
  • 77
  • 10
    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")` – James May 31 '13 at 17:51
  • @James The `$` sign makes the selected area fixed so it doesn't "move away" when you drag down or copy-paste the cell. – totymedli May 16 '17 at 19:10
  • 1
    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. – James L. May 18 '17 at 22:55
13

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.

Ian Howlett
  • 131
  • 1
  • 3
10

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.
K Swink
  • 101
  • 1
  • 2
1

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

horseyride
  • 17,007
  • 2
  • 11
  • 22
1

New Answer 9/28/2022

Now you can use FILTER function that simplifies it.

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

sample excel file

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.

David Leal
  • 6,373
  • 4
  • 29
  • 56