Answers / Question 1

Remove duplicates but keep the newest row, with one formula (Google Sheets)

A single SORTN formula that returns one row per ID or email, always the most recent one, even when the data comes from two ranges.

The problem

You have a list where the same ID shows up several times, each with a different date, and you only care about the most recent entry for each ID. You want a live view that updates on its own, without deleting anything, and ideally one that still works when the rows come from more than one range.

Inspired by a public question on Reddit (r/googlesheets). The problem above is described in our own words and the solution below is ours.

The fix: sort newest-first, then let SORTN drop the repeats

Assume dates are in column A and the ID is in column B, with data in A:E. Put this in an empty tab:

=SORTN(SORT(FILTER(A2:E, B2:B<>""), 1, FALSE), 9^9, 2, 2, TRUE)

Read it from the inside out: FILTER drops rows with no ID, SORT(...,1,FALSE) puts the newest dates on top, and SORTN(..., 9^9, 2, 2, TRUE) keeps every row (9^9 is just “a very big number”) but removes rows whose column 2 repeats, keeping the first one it sees, which is now the newest. The result comes out sorted by ID.

If the rows come from two places with the same columns, stack them first with LET:

=LET(d, {Sheet1!A2:E; Sheet2!A2:E}, SORTN(SORT(FILTER(d, INDEX(d,,2)<>""), 1, FALSE), 9^9, 2, 2, TRUE))

Prefer to delete the old rows permanently?

1. Work on a copy first.
2. Sort the tab by date, Z→A, so the newest rows are on top.
3. Use Data → Data cleanup → Remove duplicates, tick only the ID column. It keeps the first occurrence, which is now the newest.

Good to know

• SORTN compares keys exactly, so ABC-1 and abc-1 count as different IDs. If case or stray spaces differ, add a helper column with =ARRAYFORMULA(LOWER(TRIM(B2:B))) and de-duplicate on that.
• The date column must hold real dates, not text that looks like dates, or the sort order will be wrong. =ISNUMBER(A2) returns TRUE for a real date.

Related recipe: Remove duplicates, keep latest, a full step-by-step version with more options.

Formulas follow documented Google Sheets / Excel behavior; they aren't machine-tested, so try them on a copy first. Always try new automations on a copy of your sheet first.