How to merge two spreadsheets and remove duplicates
By the Analistable team · Updated · 5 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
| Definition | Example | Choose in Remove Duplicates |
|---|---|---|
| Whole row identical | Same email, name and city | All columns |
| Same key | Same email, but the city changed | Only 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)
- Copy list B's data rows under list A, on the same sheet.
- If you want the newest record, sort by the date column, newest first (Data → Sort).
- Click in the data and choose Data → Remove Duplicates.
- 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
| Name | City | Updated | |
|---|---|---|---|
| ana@lune.fr | Ana | Lyon | 2026-09-30 |
| ben@hart.co.uk | Ben | Leeds | 2026-08-12 |
| eva@nordic.dk | Eva | Aarhus | 2026-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.
Prepare both lists before merging
- Same columns in the same order. UNIQUE + VSTACK and Remove Duplicates both work by position, so if list B has City before Name, rearrange it first.
- Clean the key:
=LOWER(TRIM(A2))in a helper column catches “ Ana@Lune.fr” and “ana@lune.fr ” as the same email. - Consistent formats: a phone number stored as text in one list and as a number in the other won't match. Convert one side.
- A date column if you want the newest record to win. Without one, you can only keep whichever row comes first.
See what overlaps before you delete anything
It's worth knowing how many records are in both lists, so you can check the result. Next to list A, add:
=IF(COUNTIF(ListB!$A:$A, A2)>0, "In both", "Only in A")With 1,200 rows in list A, 800 in list B and 150 emails in both, the merged, deduplicated list should have 1,200 + 800 − 150 = 1,850 rows. If Remove Duplicates reports a different number, some emails are probably duplicated inside one of the lists as well. For more overlap formulas, see finding matches between two lists.
Combine information instead of choosing one row
Sometimes neither row is complete: list A has the phone number and list B has the company. Instead of keeping one row, fill the gaps from the other list. On the merged unique list (emails in column A):
=LET(a, XLOOKUP(A2, ListA!A:A, ListA!D:D, ""),
b, XLOOKUP(A2, ListB!A:A, ListB!D:D, ""),
IF(a<>"", a, b))This takes the phone from list A when it exists and from list B otherwise. Swap a and b to make list B win. For contact lists specifically, see merging contact lists without duplicates.
Google Sheets version
The same approach works in Google Sheets. =UNIQUE(VSTACK(ListA!A2:C, ListB!A2:C)) merges and removes identical rows; wrap it in QUERY(..., "where Col1 is not null", 0) to drop the blank rows from open-ended ranges. Data → Data cleanup → Remove duplicates lets you choose the columns, and like Excel it keeps the first occurrence, so sort newest first beforehand. Note that UNIQUE in Google Sheets is case-sensitive, unlike Excel's.
Troubleshooting
| Symptom | Cause | Fix |
|---|---|---|
| Two rows for the same email | Extra space or different capitalisation | Dedupe on a LOWER(TRIM()) helper column |
| Older record kept | Data wasn't sorted by date first | Sort newest first, then remove duplicates |
| Remove Duplicates removed too much | Only a broad column (such as City) was ticked | Undo and tick only the key column |
| UNIQUE returns #SPILL! | Cells in the output area aren't empty | Clear space below and to the right |
Remove Duplicates changes the data in place. Work on a copy of the sheet, or keep the originals on separate tabs, so you can check the result against them.
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.
- How do I keep the newest row when removing duplicates?
- Sort by the date column with the newest first, then run Remove Duplicates on the key column. Excel keeps the first row of each group, which is now the newest.
- Can I see which rows were removed?
- Not from Remove Duplicates itself. Before deduplicating, add =COUNTIF($A$2:A2, A2)>1 in a helper column and fill down; TRUE marks every repeat after the first.
- How do I merge two lists that have different columns?
- Rearrange both lists to the same column order first, adding empty columns where one list lacks a field. UNIQUE, VSTACK and Remove Duplicates all compare columns by position, not by header name.