How to merge two sheets and keep the latest record per ID

The situation

Two exports overlap: an old customer list and a newer one, a backup and the live sheet, two regional copies that were both edited. Some IDs appear in both with different values. Plain merging and removing duplicates only removes rows that are identical, and Remove Duplicates on the ID alone keeps whichever row happens to come first. You want the most recent version of each record.

Six stacked rows, four distinct IDs, four rows out. C-002 keeps Sheet A's row because it is newer, even though Sheet B is the “newer” file.

Method 1: sort, then Remove Duplicates

1. Copy Sheet B's rows under Sheet A's in one sheet with identical columns. Add a Source column if you want to know where each row came from.
2. Make sure Updated holds real dates, not text.
3. Sort by Updated, Newest to Oldest (Data → Sort).
4. Data → Remove Duplicates, tick only Customer ID, OK.

Remove Duplicates keeps the first occurrence of each value and deletes the rest. Because the newest row is now first, it survives. It's a one-off operation that deletes data, so work on a copy. In Google Sheets, Data → Data cleanup → Remove duplicates behaves the same way.

If two rows for the same ID share the same date, which survives depends on their order after sorting. Add a second sort level (for example Source, with the preferred system first) to make ties predictable.

Method 2: MAXIFS flag (keeps the source intact)

With the stacked data in A:E, add a column that marks the latest row per ID. MAXIFS is available in Excel 2019, Microsoft 365 and Google Sheets:

=IF(D2 = MAXIFS(D:D, A:A, A2), "keep", "older")

Filter on “keep”, or, in Microsoft 365, pull the result straight into a new sheet: =FILTER(Stacked!A2:E500, Stacked!F2:F500="keep"). The source rows stay untouched, so you can see what was dropped. Ties produce two “keep” rows for the same ID; check with =COUNTIFS(A:A, A2, F:F, "keep")>1.

Excel 365 users can also stack and dedupe in one formula by sorting newest first and taking the first row per ID with XLOOKUP against UNIQUE IDs, though the helper-column version is easier to check.

Method 3: SQL with ROW_NUMBER

WITH stacked AS (
  SELECT *, 'Sheet A' AS source FROM sheet_a
  UNION ALL BY NAME
  SELECT *, 'Sheet B' AS source FROM sheet_b
), ranked AS (
  SELECT *, row_number() OVER (
           PARTITION BY customer_id
           ORDER BY updated DESC, source DESC) AS rn
  FROM stacked
)
SELECT * EXCLUDE (rn) FROM ranked WHERE rn = 1 ORDER BY customer_id;

UNION ALL BY NAME and EXCLUDE are DuckDB conveniences that line columns up by name and drop the helper column; in other databases, list the columns explicitly with UNION ALL. The second ORDER BY key breaks ties deterministically. DuckDB also has QUALIFY rn = 1, and arg_max(column, updated) if you only need a few columns. In Analistable you'd ask “combine these sheets and keep the latest row per customer” and get this query, run in your browser.

Power Query

Append both tables, sort Updated descending, then Remove Duplicates on the ID. Power Query doesn't always preserve sort order through later steps, so wrap the sort in Table.Buffer before removing duplicates, or use Group By on the ID with an “All Rows” aggregation and take the row with the max date. The second is more reliable.

When “latest” isn't the right rule

• No reliable date: if neither file has an updated timestamp, decide which source wins (for example the CRM over the spreadsheet) and sort by a priority column instead.
• Field-level freshness: the newest row might have a blank email that the older row had. Combine with COALESCE(new.email, old.email) or a lookup fallback rather than taking whole rows.
• Audit trail: keep the dropped rows on a separate tab. Reconciliation reviewers will ask what was discarded.

If duplicates should be summed rather than replaced, see combining duplicate rows in Excel. More approaches are in the reconcile data in Excel guide.

Checks before you replace the old sheet

Count distinct IDs in the stacked data with =COUNTA(UNIQUE(A2:A500)) (Microsoft 365) or count(DISTINCT customer_id) in SQL. The result must have exactly that many rows. If it has more, ties or inconsistent IDs ("C-001" and "C001") slipped through; if fewer, some IDs were blank and collapsed together.

Then spot-check a handful of IDs that appeared in both sheets and confirm the row you kept is the newer one. Finally, compare a few fields between the kept and dropped rows: if an older row has information the newer one lacks, such as a phone number, decide whether to carry it over before deleting anything.

Frequently asked questions

Does Google Sheets keep the first or last duplicate?

Data cleanup → Remove duplicates keeps the first occurrence, like Excel, so sort newest first beforehand.

Which row does Excel's Remove Duplicates keep?

The first occurrence from the top. Sort newest first before removing duplicates and you keep the latest record.

How do I remove duplicates across two sheets but keep the latest row?

Stack both sheets into one table, sort by the date column descending, then Remove Duplicates on the ID column only.

Is there a formula to get the latest record per ID?

Yes. Flag rows with =IF(D2=MAXIFS(D:D, A:A, A2), "keep", "older"), then filter on keep.

What if two rows have the same latest date?

Add a tie-breaker such as a source priority or a time column to the sort, or the result depends on row order.

Related guides

• How to merge two spreadsheets and remove duplicates
• How to combine duplicate rows in Excel
• How to reconcile data in Excel
• Payroll reconciliation template
• How to do a bank reconciliation in Excel