How to Remove Duplicate Records and Keep the Oldest Date in Excel with WPS Office
Question details
The user needs to identify duplicate observations by a unique line-item identifier and keep only the record with the oldest associated date.
- Product
- WPS Office Spreadsheet
- Device & OS
- not provided
- Scenario
- Cleaning a worksheet where multiple rows share the same line item, and the goal is to retain the earliest record for each line item.
- Observed behavior
- The worksheet contains repeated line items with different dates; only the row with the oldest date should remain for each unique line item.
Arrange your worksheet with the line-item identifier in one column and the associated date in another. Add an empty helper column and, if possible, format the data as a table so structured references are easier to use.
Use a MINIFS Helper Column to Identify and Delete Duplicate Rows
Use this method when you need a clear, traceable way to keep only the oldest date for each line item before deleting duplicates.
The MINIFS formula calculates the earliest date for each unique line-item identifier. Comparing that minimum date to the date in the current row returns TRUE only for the record that should be kept, making it easy to filter and delete the FALSE duplicates.
Click the first empty column next to your data and enter a header such as Keep Oldest.
In the first data row of the helper column, enter =MINIFS([Date],[Line Item],[@[Line Item]])=[@Date]. If you are not using a table, use actual ranges instead, for example =MINIFS($B$2:$B$100,$A$2:$A$100,A2)=B2.
Press Enter, then drag the fill handle down or double-click it to apply the formula to all rows in the dataset.
Select the helper column, go to Home > Filter, open the filter arrow, and check only FALSE.
Select the visible FALSE rows, right-click the row headers, and choose Delete Row. Then clear the filter to keep only the TRUE record for each line item.
Keep the Oldest Record for Each Line Item in WPS Spreadsheet
Use WPS Spreadsheet to identify duplicate records with MINIFS, filter FALSE rows, and keep only the oldest date for each line item without losing your Excel-based workflow.
- 1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open the Excel workbook that contains the Line Item and Date columns.
- 2. Add the MINIFS helper column: Enter =MINIFS([Date],[Line Item],[@[Line Item]])=[@Date] in the first helper cell and fill the formula down.
- 3. Filter and delete FALSE rows: Use Home > Filter to show only FALSE, select the visible rows, right-click the row headers, and choose Delete Row.
- 4. Clear the filter and review: Clear the filter and verify that each line item has one remaining TRUE record with the oldest date.
Frequently Asked Questions
Does the MINIFS method work in WPS Spreadsheet?
Yes. WPS Spreadsheet supports the MINIFS function and Excel table structured references. If you prefer, use a range formula such as =MINIFS($B$2:$B$100,$A$2:$A$100,A2)=B2.
What do TRUE and FALSE mean in the helper column?
TRUE marks the row with the oldest date for a line item. FALSE marks later duplicate rows that can be deleted.
Can I remove duplicates without adding a helper column?
You can sort by date from oldest to newest and use Data > Remove Duplicates on the identifier column, but the helper-column method is more transparent and exact.
What should I verify before deleting filtered rows?
Confirm that the filter shows only FALSE rows and that the formula returned TRUE for the oldest date in each group. Deleting visible rows in a filtered range removes only those rows.
