Excel - How to Delete All rows from 1 sheet that do not contain column input from another sheet

3
votes

I have a list of email addresses in 1 sheet in my Excel Book that are on Column M (Sheet1) with 2050 rows long (there is other data on other columns, such as First and Last Name, etc.), and another Sheet (Sheet2) that have a list of email addresses in Column A (210 rows long). I need to keep all email addresses in Sheet1 - Column M (need to keep entire row) that match with the email addresses in Sheet2 - Column A, but need to remove all other rows where email addresses that do not match from Sheet2 column A with Sheet1 - Column M.

Is there a formula to do this?

Or Perhaps it can create another sheet (Sheet3) with the rows from Sheet1 that match Sheet1 - Column M with Sheet2 - Column A?

Thanks very much.

1
excelexcel-formula
A formula cannot Delete a Row. Formulas cannot change another cell, with the exception of when the other cells have references to the cell with the function. A function alone cannot modify another cell though, not without either condition statemens or VBA COde – user2140261
Perhaps it can create another sheet with the rows from Sheet1 than? That would be even better IMHO. – Solomon Closson
stackoverflow.com/questions/1500153/… – NickSentowski

1 Answers

6
votes

Here's one method but doesn't use just a formula:

Setup an additional column as in "N" below. and fill using a similar formula

enter image description here

  • Turn on filtering for your sheets and filter for "no"
    • data --> filtering click on drop down for "N" and un-select yes/blanks
  • now place cursor in cell aX (where x will be the first row that is no)
  • press ctrl-end to highlight all rows that are "No"
  • Now press delete.

Or you can copy and paste the "YES" using filtering to another sheet.

I believe the crux of the issue here was identifying the records that didn't match which the formula in the image above: and here: =IF(ISNUMBER(MATCH(M5,Sheet2!A:A,0)),"Yes","No") does