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, 2022 in Others by Kithuzzz
• 38,020 points
1,499 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, 2022 by narikkadan
• 63,720 points

Related Questions In Others

0 votes
1 answer

How to trick an Excel function that wants a column as input to accept a list of values as if these were in a column

Use VSTACK: vstack to make an array: Use it as value ...READ MORE

answered Mar 18, 2023 in Others by narikkadan
• 63,720 points
434 views
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, 2022 in Others by gaurav
• 23,260 points
4,120 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, 2022 in Others by narikkadan
• 63,720 points
509 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, 2022 in Others by Kithuzzz
• 38,020 points
1,101 views
0 votes
1 answer

Excel formula for searching two text in one cell and return values based on the result

You can include a second IF within ...READ MORE

answered Oct 10, 2022 in Others by narikkadan
• 63,720 points
1,164 views
0 votes
1 answer

Updating Powerpoint - Excel Link in Different Computer

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

answered Oct 14, 2022 in Others by narikkadan
• 63,720 points
780 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, 2022 in Others by narikkadan
• 63,720 points
4,563 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, 2022 in Others by narikkadan
• 63,720 points
582 views
0 votes
1 answer

How to remove values from drop-down if value deleted from validation list

Try this code: Private Sub Worksheet_Change(ByVal Target As ...READ MORE

answered Dec 11, 2022 in Others by narikkadan
• 63,720 points
596 views
0 votes
1 answer

How to unmerge multiple cells and transpose each value into a new column in Pandas dataframe from excel file

Try this: df = pd.read_excel("Sample_File.xlsx", header=[0,1,2,3,4,5], index_col = ...READ MORE

answered Jan 8, 2023 in Others by narikkadan
• 63,720 points
2,414 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