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

0 votes
  1. Let's imagine I have a sheet with over 5000 rows (let's say columns "A" through "H") on it.
  2. I have a "to-remove-list" in sheet number two, which is a single column named "A," with 400 values in it, each of which has an alphanumeric string (example: xxx1234).
  3. If any value from the "to-remove-list" (from column "A" of sheet number two) is present in column "E," then I must remove every single row from sheet number one as a whole.
  4. By deleting the row and moving it up, I mean to remove the entire row (not leaving the blank space)

How can I accomplish that?

Nov 6 in Others by Kithuzzz
• 20,660 points
50 views

1 answer to this question.

0 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
answered Nov 6 by narikkadan
• 37,660 points

Related Questions In Others

0 votes
1 answer

datatable remove column on export to pdf and excel

When we are using jquery datatable for ...READ MORE

answered Feb 17 in Others by gaurav
• 22,040 points
1,822 views
0 votes
0 answers

Simple way to remove blank cells dynamic dropdown list Excel

Every time I create a dependent dynamic ...READ MORE

Nov 2 in Others by Kithuzzz
• 20,660 points
41 views
0 votes
1 answer

How to find a value in an excel column by vba code Cells.Find

Just use: Dim Cell As Range Columns("B:B").Select Set cell = ...READ MORE

answered Nov 17 in Others by narikkadan
• 37,660 points
17 views
0 votes
1 answer

Prompt message if Excel column has some value in asp.net c#

I believe it is wise to use ...READ MORE

answered 3 days ago in Others by narikkadan
• 37,660 points
10 views
0 votes
1 answer
0 votes
1 answer

Updating Powerpoint - Excel Link in Different Computer

You could look at the Excel shape's ...READ MORE

answered Oct 14 in Others by narikkadan
• 37,660 points
50 views
0 votes
1 answer

Append same text to every cell in a column in Excel

Solution All your data is in column A ...READ MORE

answered Oct 17 in Others by narikkadan
• 37,660 points
56 views
0 votes
1 answer

Using c# to select a worksheet in excel

Try this: Excel.Worksheet xlWorkSheetFocus = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(2); xlWorkSheetFocus.Activate(); I hope this ...READ MORE

answered Nov 14 in Others by narikkadan
• 37,660 points
24 views
0 votes
1 answer

1-step shortcut to delete entire row in excel 2016

Highlight the whole row by clicking on ...READ MORE

answered Oct 22 in Others by narikkadan
• 37,660 points
35 views
0 votes
1 answer

How to increment row value in an index function in excel?

Try this- =INDEX($F$27:$F$40,COLUMN(A$1)) It will automatically increase the row ...READ MORE

answered Nov 17 in Others by narikkadan
• 37,660 points
19 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP