How to Merge Several Excel or CSV Files and Remove Duplicates (Keeping the Latest Row)

You have three monthly exports (jan.csv, feb.csv, mar.csv) with the same columns, and you need one clean list. The same customer shows up in more than one file, sometimes as an exact copy and sometimes with a newer plan or status. You want one row per customer, and it should be the latest one.

I'll use this sample data throughout. Each file has the columns Email, Name, Plan, Updated:

Email	Name	Plan	Updated
[e-mail]	Ana	Basic	2024-01-05
[e-mail]	Bo	Pro	2024-01-09
[e-mail]␣	Ana	Pro	2024-02-03
[e-mail]	Bo	Pro	2024-01-09
[e-mail]	Ana	Team	2024-03-14

(␣ is a trailing space. It's there because real exports often have one.)

The right answer is one row for Ana (Team, March 14) and one for Bo.

Before you start, make sure Updated is a real date, not text. If =ISNUMBER(D2) returns FALSE, fix the dates first, or "latest" will be computed on text.

Step 1: Clean the key

Duplicates are matched on a key, here the email. [e-mail] and [e-mail] are the same person but not the same text. If you're using formulas, add a helper column Key:

=LOWER(TRIM(A2))

This works the same in Excel and Google Sheets. Power Query does the same thing with its own menus (see below).

Option A: Power Query (Excel, best for files you get every month)

Power Query is built into Excel 2016 and later on Windows and into Microsoft 365 for Mac. "From Folder" support on Mac depends on your version.

Combine the files

1. Put all the files in one folder, with the same column headers in each.
2. Data → Get Data → From File → From Folder, pick the folder, and click Combine → Combine & Transform Data.
3. Pick the sample file or sheet when asked and click OK. You get one stacked table, plus a Source.Name column showing which file each row came from.

Clean the key

1. Select the Email column, then Transform → Format → Trim, then Transform → Format → lowercase.
2. Make sure Updated has the calendar (date) type icon. If not, right-click it and choose Change Type → Date, or Using Locale… for DD/MM data.

Keep the latest row per email

1. Click the Updated column's filter arrow and choose Sort Descending.
2. Select the Email column and choose Home → Remove Rows → Remove Duplicates.

There's a known catch here. Power Query doesn't promise to keep your sort order when it removes duplicates, so on large data it can sometimes keep an older row. To fix it, look at the formula bar for the Remove Duplicates step. It will look something like this (your step name may differ):

= Table.Distinct(#"Sorted Rows", {"Email"})

Change it to:

= Table.Distinct(Table.Buffer(#"Sorted Rows"), {"Email"})

Table.Buffer locks in the sorted order before duplicates are removed.

1. Close & Load. Next month, drop the new file into the folder and click Refresh.

Option B: Formulas (Excel 365 or Google Sheets)

First, stack the files onto one tab. You can paste each file's data under the previous one on a tab called All. Or use a formula:

Excel 365 / Excel 2024:   =VSTACK(Jan!A2:D200, Feb!A2:D200, Mar!A2:D200)
Google Sheets:            ={Jan!A2:D200; Feb!A2:D200; Mar!A2:D200}

Blank rows at the bottom of each range get stacked too, so the formulas below filter them out.

Remove exact duplicates only (rows identical in every column):

=UNIQUE(FILTER(All!A2:D500, All!A2:A500<>""))

UNIQUE and FILTER need Excel 365 or Excel 2021 or later. Google Sheets has both. Note that exact matching treats [e-mail] and [e-mail] as different rows, which is why the next formula is usually what you want.

Keep the latest row per email (Excel 365):

=LET(r, FILTER(All!A2:D500, All!A2:A500<>""),
     d, SORT(r, 4, -1),
     k, LOWER(TRIM(INDEX(d, 0, 1))),
     FILTER(d, XMATCH(k, k) = SEQUENCE(ROWS(d))))

How it works:

• r is the stacked data without blank rows.
• d sorts it by column 4 (Updated), newest first.
• k is the cleaned email for each sorted row.
• XMATCH(k, k) gives, for each row, the position of the first row with the same email. A row is kept only if that position equals its own row number. That means it's the first, and therefore newest, row for that email.

Google Sheets version. It's the same logic. SORT takes FALSE for descending, and wrapping the formula in ARRAYFORMULA makes sure LOWER and TRIM run on the whole column:

=ARRAYFORMULA(LET(r, FILTER(All!A2:D500, All!A2:A500<>""),
     d, SORT(r, 4, FALSE),
     k, LOWER(TRIM(INDEX(d, 0, 1))),
     FILTER(d, XMATCH(k, k) = SEQUENCE(ROWS(d)))))

LET, XMATCH and SORT need Excel 365 or Excel 2021 or later.

Option C: No dynamic arrays (Excel 2019 and earlier, or if you prefer clicking)

Sort, then Remove Duplicates. Excel's Remove Duplicates keeps the first row it finds and deletes the ones below it, so sort first:

1. Add the Key helper column (=LOWER(TRIM(A2))), then copy it and Paste Special → Values.
2. Data → Sort: sort by Updated, Newest to Oldest.
3. Data → Remove Duplicates, uncheck everything except Key, and click OK.

Google Sheets has the same thing under Data → Data cleanup → Remove duplicates. Sort by Updated (Z→A) first, and tick only the Key column.

Work on a copy, because this deletes rows.

Lookup approach (Excel 2021+, or 2019 with the INDEX/MATCH variant). On a summary tab, put each unique key in column A, then:

B2:  =MAXIFS(All!$D$2:$D$500, All!$E$2:$E$500, A2)
C2:  =XLOOKUP(1, (All!$E$2:$E$500=A2)*(All!$D$2:$D$500=B2), All!$C$2:$C$500, "not found")

Here column E on the All tab is the Key helper column. B2 finds the newest date for that key, and C2 returns the Plan from the row with that key and that date. MAXIFS needs Excel 2019 or later. In Excel 2019, replace XLOOKUP with:

=INDEX(All!$C$2:$C$500, MATCH(1, (All!$E$2:$E$500=A2)*(All!$D$2:$D$500=B2), 0))

In Excel 2019 and earlier, enter it with Ctrl+Shift+Enter. Google Sheets has MAXIFS and XLOOKUP too.

If two rows share the same key and the same newest date, every method here keeps just one of them. Check those rows by hand if they can disagree.

Check the result

=ROWS(UNIQUE(FILTER(All!E2:E500, All!E2:E500<>"")))   → number of distinct keys
=SUMPRODUCT(--(COUNTIF(A2:A100, A2:A100)>1))          → in your result: rows whose key appears more than once (want 0)

The number of result rows should equal the number of distinct keys, and the second check should be 0. Spot-check two or three people against the original files.

What I tested

I built three small sheets in LibreOffice Calc (headless) with the cases above: an exact duplicate (Bo), a later row with different case and a trailing space (Ana), and a later plan change (Cy). Then I checked the formulas against the results computed separately in Python (pandas: sort by date, drop duplicates on the cleaned key, keep first):

• The LET / SORT / XMATCH formula returned exactly 4 rows (Ana: Team, Bo: Pro, Cy: Pro, Dee: Basic). That matched pandas.
• UNIQUE(FILTER(…)) removed only the exact Bo duplicate (8 rows → 7).
• MAXIFS + XLOOKUP and the INDEX/MATCH version returned the latest plan for every key.
• Both check formulas returned the expected values (4 keys, 0 repeats).

I could not test VSTACK, the Google Sheets {…;…} stacking, the ARRAYFORMULA Sheets version or the Power Query steps (including Table.Buffer), because they need real Excel or Google Sheets. They're written to the documented syntax. Check them on a copy first.

---

Disclosure: I run Spreadsheet Fix in 48h, a paid service. If you'd rather hand it off, prices start at $29 for one tab of formula fixes. A multi-file merge like this one is usually the $129 Custom option, with scope confirmed first. Either way, you get a full refund if it's not back in 48h: https://yeraldinmueses.gumroad.com/l/spreadsheet-fix

Thanks for reading. If your files don't line up neatly (different column names, extra columns), ask in the comments.

Yeraldin

Drafted with AI assistance; formulas tested in LibreOffice Calc as described above.