How to Remove Duplicate Names and Keep the Most Recent Date in Excel
Question details
The user wants to eliminate duplicate names in a dataset but ensure the retained record is the one with the latest date.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Cleaning up a dataset containing multiple entries for the same person or item over different dates.
- Observed behavior
- Duplicate names are removed, leaving only one unique name per row associated with its most recent chronological date.
Before proceeding, ensure your date column is formatted properly as Date values in Excel rather than plain text, which is necessary to guarantee accurate chronological sorting.
Sort by Date and Use the Remove Duplicates Tool
Sort your data chronologically first, then use Excel's built-in feature to remove older duplicates.
Excel's Remove Duplicates feature works by scanning from top to bottom. It always keeps the first occurrence it finds and deletes subsequent duplicate rows. By sorting your dates from newest to oldest first, you guarantee the most recent date is at the top and will be retained.
Highlight all the columns in your data range, including the header row containing names and dates.
Navigate to the Data tab on the ribbon and click Sort. Choose to sort by your Date column, sort on Cell Values, and set the order to Newest to Oldest. Click OK.
With the data still highlighted, go back to the Data tab and click the 'Remove Duplicates' button located in the Data Tools group.
In the dialog box that appears, uncheck the Date column and make sure only the Name column (or your identifier column) is checked. Click OK to execute.
Extract Unique Names and Latest Dates Using Formulas
If you want to keep your original data intact and extract the results to a new location, use a combination of UNIQUE and MAXIFS functions.
Remove Duplicates and Organize Data in WPS Office
WPS Spreadsheet provides intuitive data tools highly comparable to Excel, allowing you to easily sort records by date and remove duplicate entries with just a few clicks.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your dataset containing the duplicate names and dates.
- 2. Sort by Date: Highlight your data, click the Data tab, select Sort, and order your date column from Newest to Oldest.
- 3. Remove Duplicates: Under the Data tab, click Remove Duplicates. Check only the Name column in the dialog box and click OK.
Frequently Asked Questions
Why did Excel keep the oldest date instead of the newest?
Excel's Remove Duplicates tool retains the first matching record it scans from top to bottom. If your data was not sorted beforehand, or was sorted from oldest to newest, it will encounter and keep the older date first. Always sort from newest to oldest before removing duplicates.
Can I remove duplicates based on multiple columns?
Yes. In the Remove Duplicates dialog box, you can check multiple columns. Excel will only delete a row if the values in all selected columns exactly match an earlier row in the dataset.
Does the Remove Duplicates feature permanently delete my data?
Yes, it permanently deletes the duplicate rows from your active worksheet. It is highly recommended to save a copy of your workbook or duplicate the worksheet before performing data deletion actions.
