Analistable

How to merge two spreadsheets and remove duplicates

By the Analistable team · Updated · 2 min read

In Microsoft 365, =UNIQUE(VSTACK(Sheet1!A2:C100, Sheet2!A2:C100)) merges two lists and keeps one copy of each identical row. In any version, paste the second list under the first and use Data → Remove Duplicates, choosing which columns define a duplicate. To keep the newest version of each record, sort by date (newest first) before removing duplicates.

Part of our guide: How to compare two spreadsheets for differences and matches

Decide what counts as a duplicate

Duplicate definitions
DefinitionExampleChoose in Remove Duplicates
Whole row identicalSame email, name and cityAll columns
Same keySame email, but the city changedOnly the Email column

Duplicates by key need a rule for which row wins. Usually that's the most recent one.

Method 1: UNIQUE + VSTACK (Microsoft 365)

=UNIQUE(VSTACK(ListA!A2:C100, ListB!A2:C100))

Removes rows that are identical in every column. To dedupe by one column only — for example email in column A — keep the first occurrence of each email:

=LET(all, VSTACK(ListA!A2:C100, ListB!A2:C100),
     keys, CHOOSECOLS(all, 1),
     FILTER(all, (XMATCH(keys, keys) = SEQUENCE(ROWS(keys))) * (keys <> "")))

XMATCH(keys, keys) returns the position of each key's first appearance, so comparing it with the row number keeps only first occurrences.

Method 2: Remove Duplicates (any version)

  1. Copy list B's data rows under list A, on the same sheet.
  2. If you want the newest record, sort by the date column, newest first (Data → Sort).
  3. Click in the data and choose Data → Remove Duplicates.
  4. Tick only the columns that define a duplicate (for example Email) and click OK.

Excel reports how many duplicates were removed and keeps the first row of each group — the newest, because you sorted first.

Method 3: Power Query (refreshable)

Append the two tables (Data → Get Data → Combine Queries → Append), sort by date descending, then select the key column and choose Home → Remove Rows → Remove Duplicates. One catch: Power Query doesn't guarantee that it keeps the sorted order when removing duplicates. Wrap the sort step in Table.Buffer so the newest row reliably wins:

Sorted = Table.Buffer(Table.Sort(Appended, {{"Updated", Order.Descending}})),
Deduped = Table.Distinct(Sorted, {"Email"})

Worked example

List A and List B merged, deduplicated by email, newest kept
EmailNameCityUpdated
ana@lune.frAnaLyon2026-09-30
ben@hart.co.ukBenLeeds2026-08-12
eva@nordic.dkEvaAarhus2026-09-14

Ana appeared in both lists, with Paris in the older one. Only the newer row with Lyon remains.

Emails in different capitalisation (Ana@Lune.fr) are treated as the same by Remove Duplicates and UNIQUE, but not by Power Query, which is case-sensitive. Lower-case the column first with Transform → Format → lowercase.

Frequently asked questions

How do I combine two lists in Excel without duplicates?
Use =UNIQUE(VSTACK(list1, list2)) in Microsoft 365, or paste one list under the other and use Data → Remove Duplicates.
Which duplicate does Remove Duplicates keep?
The first one in the current order. Sort the data first to control which row is kept.
Is Remove Duplicates case-sensitive?
No, Excel's Remove Duplicates ignores case. Power Query's Remove Duplicates is case-sensitive.

Related guides