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

Question

Let s say that I ve got a sheet - number one - with over 5000 rows  say  columns  A  -  H  each   In another sheet - number two - I have a  to-remove-list  - a single column  A  with 400 values  each containing alphanumerical string  example  xxx1234   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   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

User · Accepted Answer

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

User · Answer

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

User · Answer

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  amp  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

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

Examples related to excel

Examples related to excel-2007

Examples related to office-2007