3

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.

Solomon Closson
  • 6,111
  • 14
  • 73
  • 115
  • 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 Jun 28 '13 at 17:40
  • Perhaps it can create another sheet with the rows from Sheet1 than? That would be even better IMHO. – Solomon Closson Jun 28 '13 at 17:43
  • https://stackoverflow.com/questions/1500153/find-the-differences-between-2-excel-worksheets/1500222#1500222 – NickSentowski Feb 14 '20 at 23:20

1 Answers1

7

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

xQbert
  • 34,733
  • 2
  • 41
  • 62