Your customer file has one problem: every time a customer moves or changes phone number, a new row is added. The old row stays. After a few years, the same person appears two, three or four times, and you only want the most recent row for each customer.

With Microsoft 365, two functions solve this without deleting a single row: MAXIFS finds the latest date of each customer, and FILTER keeps only the rows with that date.

The customer file

Our file contains 24 rows, stored in an Excel table named tbl_Customers. The last two columns are the ones that matter:

• EmailAddress: it identifies the customer. A customer can change address, city or phone number, but the email stays the same. This is our unique key.
• Last Update: the date the row was created.

Look at Tiffany Coon: she appears 4 times, with 4 different cities. Only the most recent row is still valid.

Why Remove Duplicates doesn't work here

The first reflex is to use the Remove Duplicates tool. It removes nothing: each row of the same customer has a different address, so for Excel these are different rows.

And if you only check the EmailAddress column, Excel keeps the first row it finds, not the most recent one. You would keep the old address and lose the new one.

Step 1: Extract the list of customers with UNIQUE

First, we need each customer only once. Email is the key, so extract the list of unique emails. In cell K2, outside the table, write:

=UNIQUE(Client[EmailAddress])

The result spills down automatically: 14 emails, one per customer. Tiffany Coon had 4 rows in the table, she now appears only once.

Step 2: Find the latest date with MAXIFS

MAXIFS returns the largest value of a column, but only for the rows that meet a condition. Here, the largest date, only for the rows with the same email. In cell L2, write:

=MAXIFS(Client[Last Update],Client[EmailAddress],K2#)

• Client[Last Update]: the column where we look for the largest value.
• Client[EmailAddress]: the column where we check the condition.
• K2#: the list of emails created by UNIQUE. The # sign means "the whole spilled list", so MAXIFS returns one date for each email.

Format the result as a date. Next to Tiffany Coon's email, you now read 11/05/2023: the date of her last move, to Spartanburg.

Step 3: Return the full row with FILTER

We have the email and the latest date of each customer. We now return to the original table and keep the row that matches both. In another worksheet, in cell A1, write:

=FILTER(Client,(Client[EmailAddress]=K2)*(Client[Last Update]=L2))

• Client: the data to return, the whole table.
• (Client[EmailAddress]=K2): the row must have the email of the customer.
• (Client[Last Update]=L2): the row must have the latest date.

The * sign between the two conditions means AND. FILTER returns the full customer row with the latest address. Copy the formula down to row 15, one row for each email.

You get 14 rows, one per customer. Tiffany Coon is in Spartanburg, Lydia Pedrosa in Reno, Donna Edmond in Danville.

💡 FILTER does not return the headers. To add them, write this formula in cell N1:

=Client[#Headers]

The same result in one single formula

You prefer one formula that returns all the rows at once? Put MAXIFS directly inside FILTER:

=FILTER(Client, Client[Last Update]=MAXIFS(Client[Last Update],Client[EmailAddress],Client[EmailAddress]))

The trick is in MAXIFS's last argument. We give it the whole EmailAddress column instead of one email. MAXIFS then calculates the latest date for each row in the table, and FILTER keeps the rows where the date matches it.

Why this method is better

• Nothing is deleted. Your original table keeps the full history of each customer. The clean list is a separate result.
• The result updates itself. Add a new row sfor a customer who moved, and the FILTER result shows the new address immediately. No sort, no filter, no rows to delete.
• No sorting needed. The rows can be in any order: MAXIFS finds the latest date wherever it is.

Two things to know

• Excel version: FILTER requires Microsoft 365 or Excel 2021 and later. MAXIFS works from Excel 2019. You can also use Excel online for free, both functions are available.
• Two rows with the same date: if a customer has two rows updated on the same day, both are returned because both have the latest date.