CODEWITHSUNDEEP
excelexcel-2007office-2007
user1666562
user1666562

Reputation:

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

  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.

Upvotes: 50

Views: 132015

Answers (5)

David Leal
David Leal

Reputation: 6759

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.

Upvotes: 1

horseyride
horseyride

Reputation: 21318

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

Upvotes: 1

K Swink
K Swink

Reputation: 101

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.

Upvotes: 10

Ian Howlett
Ian Howlett

Reputation: 131

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.

Upvotes: 13

James L.
James L.

Reputation: 9451

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

Upvotes: 77

Related Questions