0

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?

2
  • 1
    Welcome to Web Applications. Please show what you tried and add a brief description of your search efforts as is suggested in How to Ask.
    – Rubén
    Jul 30 '21 at 17:39
  • 1
    Consider sharing a publicly editable sample spreadsheet with realistic-looking data, and showing your hand-entered expected results there. Jul 30 '21 at 18:56
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.