What formula can I use to filter and copy to another sheet

Question

Google Sheets

Data Set of 70,000 contact records with multiple duplicate entries where name, address, and email may have changed

Data includes: Entry ID, Entry date, First, Last, Email, Street, City, Zip, Source,

What formula can I use to filter and copy to another sheet

1. Duplicates based on email where only most recent instance is kept and others are removed to another sheet and
2. Duplicates based on name where only most recent instance is kept and others are removed to another sheet
3. These duplicates matched by address and then only most recent entry is kept and others removed to a third sheet.
4. Those uniques matched with duplicate address entries to isolate couples and households.

And when do I append:

Email deliverability status? What if the most recent entry is a fictitious entry and uses a bogus email? I don't want to get rid of the good email associated with a contact

Also, most recent entry may have excluded the full address, how would I preserve that part of the contact record in my spreadsheet?

Answer 1

Assuming the data range is A2:I and the columns are in the order you list, you can remove all but the latest entry for each email address in E2:E with this formula in another sheet:

=arrayformula( 
  iferror( 
    vlookup( 
      unique(lower(trim(Sheet1!E2:E))), 
      sort( 
        { lower(trim(Sheet1!E2:E)), Sheet1!A2:I }, 
        Sheet1!B2:B, 
        false 
      ), 
      column(Sheet1!A2:I) - column(Sheet1!A2) + 2, 
      false 
    ) 
  ) 
)

Email addresses are usually always in the same format, which makes them relatively easy to work with — in the formula above, they are just converted to lower case, and any leading and trailing spaces are removed. Working with free-form names and addresses is more difficult, so you may want to normalize them before you try the same with them.