A customer list re-exported every month often contains the same customer ID multiple times — once per export where their record was included, each with a different "last updated" date. Deduplicating by keeping just the first occurrence throws away the most current information. This recipe flags the row with the latest date for each key as the one to keep, and every earlier duplicate as superseded.
Customer C-104 appears three times; only the row with the newest date is flagged Keep.
The example
One customer ID (C-104) was re-exported three times with updated info each time; two others appear once.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Customer ID | Updated | Status | |
| 2 | C-104 | 1/5/2026 | [email protected] | Superseded |
| 3 | C-201 | 2/2/2026 | [email protected] | Keep |
| 4 | C-104 | 3/18/2026 | [email protected] | Superseded |
| 5 | C-104 | 6/9/2026 | [email protected] | Keep |
| 6 | C-330 | 4/4/2026 | [email protected] | Keep |
The formula
MAXIFS finds the latest date for the current row's key; the IF compares this row's date against it:
How it works
MAXIFS filters by key, then IF compares:
MAXIFS($B$2:$B$6,$A$2:$A$6,A2)looks across the whole date range, restricts to rows where the ID column matches this row's ID, and returns the latest date among just those matches.B2=compares this row's own date to that latest date. If they match, this row IS the most recent record for its ID.- The absolute references (
$B$2:$B$6,$A$2:$A$6) keep the range fixed when the formula is filled down; onlyA2andB2change per row. - Filter or sort on the result column to keep only "Keep" rows for a clean, deduplicated export.
If two records for the same key share the exact same latest date, both will show Keep — add a tiebreaker column (a row number or timestamp) if that is possible in your data and needs to resolve to exactly one row.
Try it: interactive demo
This row's date and the latest date on record for the same key:
Variations
Keep the FIRST occurrence instead (oldest wins)
Swap MAXIFS for MINIFS to flag the earliest record per key as the one to keep, useful when the first entry is the authoritative source and later rows are just re-confirmations.
Count how many superseded rows exist per key
A quick COUNTIFS shows how many times each key was re-exported, useful for spotting an ID with an unusually high update frequency that might indicate a data-entry problem.
Pitfalls & errors
MAXIFS ignores blank date cells but not text-formatted dates — a date typed or imported as text will not compare correctly against real date values and can cause every row for that key to show Superseded, or none to show Keep.
The absolute references in the ranges must cover every row of data, including rows added later. A range typed as $B$2:$B$6 will silently ignore row 7 if new data is appended below it without extending the formula's range.
Add a helper column that counts occurrences of the ID (COUNTIF) before filtering — a customer with only one record will always show Keep regardless of this formula, and separating "was ever duplicated" from "is the current version" is often useful information on its own.
Practice workbook
Frequently asked questions
Does this actually delete the superseded rows?
What if the key spans two columns, like first name plus last name?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class