DEV Community: Yeraldin Mueses

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

Yeraldin Mueses — Tue, 20 Oct 2026 13:09:05 +0000

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
ana@x.com	Ana	Basic	2024-01-05
bo@x.com	Bo	Pro	2024-01-09
ANA@x.com␣	Ana	Pro	2024-02-03
bo@x.com	Bo	Pro	2024-01-09
ana@x.com	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. ANA@x.com and ana@x.com 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

Put all the files in one folder, with the same column headers in each.
Data → Get Data → From File → From Folder, pick the folder, and click Combine → Combine & Transform Data.
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

Select the Email column, then Transform → Format → Trim, then Transform → Format → lowercase.
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

Click the Updated column's filter arrow and choose Sort Descending.
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.

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 ANA@x.com and ana@x.com 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:

Add the Key helper column (=LOWER(TRIM(A2))), then copy it and Paste Special → Values.
Data → Sort: sort by Updated, Newest to Oldest.
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.

How to Fix Mixed-Format Dates in Excel and Google Sheets (DD/MM vs MM/DD, Text Dates)

Yeraldin Mueses — Tue, 20 Oct 2026 13:08:35 +0000

You open a CSV that someone exported from a UK or European system, and the date column is a mess. Some cells are right-aligned, some are left-aligned. 15/04/2024 sits there as text. 03/04/2024 looks fine but is now March 4 instead of April 3. Sorting and filtering are broken, and a pivot by month is wrong without any warning.

Here's what happened and how to fix it in Excel and Google Sheets.

Why this happens

The file was written in DD/MM/YYYY order, and your spreadsheet runs in a MM/DD/YYYY locale (for example, US English). When the file opens:

Any value where the day is 12 or less, like 03/04/2024, looks like a valid US date, so it gets converted to a real date with day and month swapped.
Any value where the day is over 12, like 15/04/2024, can't be a US date, so it stays text.

So one column can hold wrong real dates and unconverted text at the same time. Changing the cell format won't fix either kind.

Step 0: Diagnose the column

Real dates are numbers underneath. Put this next to your data (assume dates are in column A, starting in row 2):

=ISNUMBER(A2)

TRUE means it's a real date (possibly swapped). FALSE means it's text. This works the same in Excel and Google Sheets.

If every cell is FALSE (all text), skip to the Text to Columns or Power Query options. They're cleaner. The formula below handles the mixed case.

Option 1: The formula fix (mixed real and text dates)

This formula assumes the original data was DD/MM/YYYY with a 4-digit year, and that every real-date cell in the column got there by the swap described above. In a helper column B:

=IF(ISNUMBER(A2),
    DATE(YEAR(A2), DAY(A2), MONTH(A2)),
    DATE(RIGHT(A2,4),
         MID(A2, FIND("/",A2)+1, FIND("/",A2,FIND("/",A2)+1)-FIND("/",A2)-1),
         LEFT(A2, FIND("/",A2)-1)))

What it does:

If the cell is a real (swapped) date: it rebuilds the date with day and month switched back. DATE(year, month, day) takes the old day as the month and the old month as the day.
If the cell is text: it splits on the slashes. LEFT up to the first / is the day, MID between the two slashes is the month, and RIGHT(…,4) is the year. Using FIND instead of fixed positions means 1/2/2024 and 01/02/2024 both work.

Fill it down, format column B as a date (I suggest yyyy-mm-dd so nothing is ambiguous), then copy column B and Paste Special → Values over the original if you want to replace it.

This works as written in Excel (any modern version) and Google Sheets.

Excel 365 / Google Sheets tidier version for an all-text column using LET:

=LET(s, A2,
     a, FIND("/", s),
     b, FIND("/", s, a+1),
     DATE(RIGHT(s,4), MID(s, a+1, b-a-1), LEFT(s, a-1)))

LET needs Excel 365 or Excel 2021 or later. Google Sheets supports it too.

Watch out:

Don't use the swap branch if some real dates in the column were already correct, for example typed in by hand. The formula can't tell them apart, and you'd swap good dates. In that case, re-import the source file (Option 2 or 3).
If your text uses - or . instead of /, use SUBSTITUTE(A2,"-","/") in place of A2, or change the FIND character.
Two-digit years (03/04/24) need 2000+RIGHT(A2,2) instead of RIGHT(A2,4).

Why not just DATEVALUE?

DATEVALUE converts text to a date using your current locale. In a US-locale workbook, DATEVALUE("15/04/2024") returns #VALUE!, and DATEVALUE("03/04/2024") gives you March 4, which is the same wrong answer. It's useful when the text is already in your locale's order or in ISO format (2024-04-15), but it can't fix DD/MM text in an MM/DD workbook.

In Google Sheets you can get around this. Go to File → Settings → Locale, set it to a DD/MM country like United Kingdom, and =DATEVALUE(A2) will read text cells as DD/MM. The locale change doesn't fix cells that were already converted wrongly, so you still need the swap branch for those.

Option 2: Text to Columns (Excel, when the column is all text)

If the column is still all text (for example, you imported it with the column set to Text), Excel's built-in converter is quick:

Select the date column.
Data → Text to Columns.
Choose Delimited, click Next, uncheck all the delimiters, and click Next.
Under Column data format, choose Date and pick DMY from the dropdown.
Click Finish.

Excel reads each text value as day-month-year and turns it into a real date. Use it on text cells. Cells that were already converted into swapped real dates still need the formula.

Google Sheets' Data → Split text to columns has no DMY option. Use the locale and DATEVALUE approach, or the formula above.

Option 3: Power Query (Excel, the best fix if you still have the original file)

The cleanest fix is to stop Excel from guessing in the first place:

Data → Get Data → From File → From Text/CSV, and pick the original file.
Click Transform Data.
Right-click the date column header and choose Change Type → Using Locale…
Set Data Type to Date and Locale to the source country (for example, English (United Kingdom)).
Click OK, then Close & Load.

Power Query reads the raw text with the rules you chose, so nothing gets swapped. Next month, drop in the new file and click Refresh. Power Query is built into Excel 2016 and later on Windows and into Microsoft 365 for Mac. Older Windows versions need the free add-in.

Step 4: Check your results

Don't trust it until you've checked it. With the fixed dates in column B:

=COUNT(B2:B500)                         → how many real dates you have
=COUNTA(A2:A500)                        → how many non-blank source cells
=SUMPRODUCT((A2:A500<>"")*NOT(ISNUMBER(B2:B500)))  → filled rows that still are NOT real dates (want 0)
=TEXT(MIN(B2:B500),"yyyy-mm-dd")        → earliest date: does it make sense?
=TEXT(MAX(B2:B500),"yyyy-mm-dd")        → latest date: does it make sense?
=COUNTIF(B2:B500,">"&TODAY())           → any dates in the future?

The first two numbers should match, and the third should be 0. A minimum or maximum that's way off, or unexpected future dates, usually means some rows went through the wrong branch.

One more thing no formula can solve: if the source mixed DD/MM and MM/DD rows, a value like 03/04/2024 really is ambiguous. To see how many rows could go either way, count the fixed dates where the day is 12 or less:

=SUMPRODUCT(--(DAY(B2:B500)<=12))

For those rows, check another column (an invoice number, the order of entries, the source system) or ask whoever exported the file.

What I tested

I built a sample column in LibreOffice Calc (headless, en-US locale) with the same mix you get from a DD/MM file opened in a US locale. It had 03/04/2024, 1/2/2024, 12/11/2024, 9/10/2023 and 05/06/2024 stored as swapped real dates, and 15/04/2024, 31/12/2023 and 28/02/2024 left as text. The main formula returned the correct date for all 8 rows. The LET version and the text-only branch also got all 8 right, and the check formulas returned the expected counts. I couldn't test the Excel Text to Columns or Power Query steps or Google Sheets itself here, so those steps follow the standard menus. If a menu name differs in your version, look for the same option nearby.

Disclosure: I run Spreadsheet Fix in 48h, a paid service. If you'd rather hand it off, fixing a date column like this on one tab is the $29 Fix option. You get a full refund if it's not back in 48h: https://yeraldinmueses.gumroad.com/l/spreadsheet-fix

Thanks for reading. Questions or edge cases are welcome in the comments.

Yeraldin

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

Your quarterly estimate is a lie if you key off invoice date

Yeraldin Mueses — Wed, 02 Sep 2026 16:35:46 +0000

Q3 federal estimates are due Tuesday, September 15, 2026. If you add up invoices you sent in June–August, you will under- or over-pay, because 1099 income is cash-basis: March invoice, April payment = Q2.

This is a checklist, not tax advice. It will not tell you what you owe. Not a penalty guarantee. Not affiliated with the IRS. CPA if income jumped, S-corp, or multi-state.

The 10-minute log

Open a sheet. Three columns: client, payment date, amount.
Drop every deposit that actually cleared Jun 1–Aug 31. Ignore "they said they'd pay."
Subtract expenses you can document. No receipt, don't invent it.
Park a set-aside out of checking. A common 1099 starting point is 25–35% of net — yours may be different.
Pay via IRS Direct Pay or EFTPS. Screenshot the confirmation.

If you already paid 100% of last year's total tax (Form 1040 Line 24) split across four dates, you may be in safe harbor even if 2026 is bigger (110% if AGI was over $150k). Year one off W-2: ignore last year and park the set-aside the day each deposit clears.

If this still lives in 14 Google Docs

Pipeline, invoices, and the quarterly worksheet belong in one place you open every week. I packed that into a $29 Freelance Ops Pack (Sheets OS + invoice PDF + quarterly worksheet): https://yeraldinmueses.gumroad.com/l/letldi

Comment RITUAL if you want the Sep 15 checklist as a one-pager.

The 20-minute ritual I run the week before a 1099 estimated-tax due date

Yeraldin Mueses — Fri, 28 Aug 2026 23:15:35 +0000

Q3 federal estimates are due Tuesday, September 15, 2026. This is the quarter that nails people because Q2 was only two months.

This is a checklist, not tax advice. It will not tell you what you owe. It is not a penalty guarantee. If income jumped, you have an S-corp, or you are multi-state, talk to a CPA before you pay. Not affiliated with the IRS.

Do this the week before Sep 15

List what actually hit your account Jun 1–Aug 31. Client payments only. Ignore "they said they'd pay."
Subtract expenses you can document (software, contractors, ads, a real home-office % if you already use one). If you don't have the receipt, don't invent it.
Move a set-aside out of checking. A common starting point for 1099s is 25–35% of net. Yours may be different. If the money stays in checking, you will spend it.
Pay the IRS via Direct Pay or EFTPS. Screenshot the confirmation. Put it in a folder named taxes-2026.
Check your state. NY, CA, and others often want their own estimate. This post does not calculate that.

The cash-basis trap

A 1099 invoice log has to be cash-basis or your quarterly estimate is a lie: March invoice, April payment = Q2 income. Key the quarter off payment date, not invoice date.

If you already paid 100% of last year's total tax (Form 1040 Line 24) split across four dates, you may be in safe harbor even if 2026 is bigger (110% if AGI was over $150k). If this is year one off W-2, ignore last year and park a set-aside the day each deposit clears.

If this still lives in 14 Google Docs

That's the whole problem. Pipeline, invoices, and this quarterly worksheet belong in one place you open every week — not the night before a due date.

I packed that into a $29 Freelance Ops Pack (Sheets OS + invoice PDF + the quarterly worksheet): https://yeraldinmueses.gumroad.com/l/letldi

Comment RITUAL if you want the checklist as a standalone one-pager and I'll send it.