How to merge contact lists without duplicates

Step by step

1. Stack the exports into one table with a Source column (see combining sheets into one).
2. Add =LOWER(TRIM([@Email])) as the key.
3. Add an opt-out flag per email: =COUNTIFS([Key], [@Key], [Status], "unsubscribed") + COUNTIFS([Key], [@Key], [Status], "cleaned") > 0.
4. Sort by Updated date, newest first, then Data → Remove Duplicates on the Key column.
5. Set Status to unsubscribed wherever the opt-out flag is TRUE.

Worked example

The newest record supplies Ana's full name, but her unsubscribe from March is preserved even though HubSpot still shows her as subscribed.

Field-by-field rules

Same email, different capitalisation: Excel's Remove Duplicates and UNIQUE ignore case, but Power Query's Remove Duplicates doesn't — lower-case first. More in merging two spreadsheets and removing duplicates.

Combining tags

=TEXTJOIN(", ", TRUE, UNIQUE(FILTER(All[Tag], All[Key] = [@Key])))

This collects every tag a contact had in any source into one cell, without repeats. Import it into the destination tool as a multi-value field, or split it back into tags there.

Before importing the merged list

• Import unsubscribed contacts as unsubscribed — never leave them out, or a later import may re-subscribe them.
• Check the destination tool's own suppression list; it may hold opt-outs that none of your exports included.
• Keep a copy of every source export, so you can show where each consent came from.

Doing it in each tool

Excel 365 and Excel 2021: Remove Duplicates keeps the first occurrence of each key, so sorting newest first decides which row survives. The TEXTJOIN with UNIQUE and FILTER formula for tags needs dynamic arrays, so it won't work in older versions.

Excel 2019: Remove Duplicates and TEXTJOIN are available, but UNIQUE and FILTER are not. Collect tags with a pivot table (Key in rows, Tag in rows beneath it) or with Power Query: group by Key and combine the Tag column.

Google Sheets: Data → Data cleanup → Remove duplicates works on a selected range, and =UNIQUE() returns distinct rows. Sheets' UNIQUE is case-sensitive, so build the lower-cased key first and run UNIQUE or Remove duplicates on that key.

Power Query: Table.Distinct compares text case-sensitively by default, so add a lower-cased key column, sort by Updated descending, then remove duplicates on the key. Power Query does not always keep a sort when it removes duplicates, so wrap the sorted step in Table.Buffer before Remove Duplicates, then check a few contacts to confirm the newest record survived.

Second example: same person, different emails

These are three different email keys, so the merge keeps three rows. That's correct for mailing purposes — each address has its own consent — but you may want to link them for reporting. Flag possible duplicates with a second key such as lower-cased name plus company, review the flagged pairs by hand, and never copy an opt-out from one address onto another without checking: an unsubscribe applies to the address it came from.

Troubleshooting

Check the result

• Distinct keys before the merge must equal rows after it: =ROWS(UNIQUE(All[Key])) in Excel 365 or Google Sheets.
• The count of unsubscribed rows after the merge must be at least the number of distinct keys with any opt-out before it.
• Spot-check five contacts that appeared in several sources and confirm the right values won.

If you merge lists regularly — say after each event or campaign — keep the workbook as a template with the key, flag and status formulas in place, and paste each new export into the stacked table. For the stacking step across many files, see merging two spreadsheets and removing duplicates and combining duplicate rows in Excel.

Doing it in SQL

For lists too large for a spreadsheet, the same rules fit in one query. Window functions pick the newest record per email, and a separate aggregate keeps any opt-out. This runs in DuckDB and in SQLite 3.25 or later:

WITH keyed AS (
  SELECT *, LOWER(TRIM(email)) AS k,
         ROW_NUMBER() OVER (PARTITION BY LOWER(TRIM(email)) ORDER BY updated DESC) AS rn
  FROM contacts
),
optout AS (
  SELECT k, MAX(CASE WHEN status IN ('unsubscribed', 'cleaned') THEN 1 ELSE 0 END) AS opted_out
  FROM keyed GROUP BY k
)
SELECT keyed.k AS email, name,
       CASE WHEN opted_out = 1 THEN 'unsubscribed' ELSE status END AS status
FROM keyed JOIN optout USING (k)
WHERE rn = 1;

On the three-row example above, this returns ana@lune.fr as Ana Martin, unsubscribed, and ben@hart.co.uk as Ben Hart, subscribed — the same result as the spreadsheet method.

Which method when

• A few thousand contacts, once: the spreadsheet steps above.
• A monthly merge of the same exports: Power Query, so a refresh repeats every step.
• Hundreds of thousands of rows: the SQL query, which keeps the newest-record and opt-out rules in one place and is easy to rerun.

Frequently asked questions

How do I merge two mailing lists without duplicates?

Stack them, create a lower-cased email key, sort newest first and remove duplicates on the key — keeping any unsubscribe from either list.

Which record should I keep when emails match?

Usually the most recently updated one for profile fields, but any opt-out from any source must be kept.

Are emails case-sensitive when deduplicating?

In practice treat them as case-insensitive: lower-case them before matching.

How do I keep unsubscribes when merging lists?

Flag every email that is unsubscribed or cleaned in any source, and set the merged status to unsubscribed for all of them.

Can I deduplicate contacts in Google Sheets?

Yes. Add a lower-cased email key, sort newest first and use Data cleanup → Remove duplicates on the key column; handle opt-outs with the same COUNTIFS flag.

Should I merge contacts with the same name but different emails?

Link them for reporting if you're sure, but keep each email as its own mailing record with its own consent and opt-out status.

Related guides

• How to merge two spreadsheets and remove duplicates
• The best tools to compare two spreadsheets
• How to combine duplicate rows in Excel
• How to compare two columns in Excel
• How to compare two Excel sheets for differences