Often you may want to remove duplicate rows in Excel but keep the row that contains the newest date in a particular column.
For example, suppose you have the following dataset that contains information about sales made on various dates by employees at some company:
Suppose you’d like to remove rows with duplicate values in the Employee column but keep the row with the newest date in the Date column:
The following example shows exactly how to do so.
Example: Remove Duplicates but Keep Row with Newest Date in Excel
First, type the following formula into cell D2:
=UNIQUE(A2:A13)
This produces a list of unique employee names from the dataset:
Next, type the following formula into cell E2 to find the newest date in the Date column associated with each unique employee name:
=MAX(IF($A$2:$A$13=D2,$B$2:$B$13))
Then click and drag this formula down to the remaining cells in column E:
By default, Excel displays these dates as serial numbers.
To convert these numbers to dates, highlight the range E2:E5, then click the Number Format dropdown menu in the Number group of the Home tab, then click Short Date:
The numbers in column E will now be formatted as dates:
Columns D and E now show the original dataset with the rows removed that had duplicate values in the Employee column and only kept the rows with the newest date in the Date column.
For example, there are four rows with a duplicate “Andy” value in the Employee column but the row with the newest date among these four rows is the one with 6/1/2023:
This is the one row that is kept among all possible rows with “Andy” in the filtered dataset.
The same process is repeated for every other unique employee name.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Excel: How to Count Duplicates
Excel: How to Highlight Duplicate Rows
Excel: How to Find Duplicates Using VLOOKUP
Excel: How to Remove Duplicate Rows Based on Two Columns
