Keep the Highest-Value Row for Duplicate Criteria in WPS Spreadsheet
Question details
Keep only the row with the highest value in Column C when multiple rows have the same combined values in Columns A and B.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- The worksheet contains duplicate criteria pairs across Columns A and B, and the user wants to retain the highest-value record for each pair.
- Observed behavior
- Multiple rows share the same Column A and Column B combination, with different values in Column C.
Confirm that Columns A and B contain the criteria used to identify duplicates and that Column C contains numeric values. Choose an unused helper column, such as Column D, for the formula result.
Use MAXIFS to mark the highest-value row in each duplicate group
Use this method when you want to review the matching rows before deleting lower-value duplicates.
The formula compares each value in Column C with the maximum Column C value for the same Column A and Column B combination. Rows that contain the maximum value return TRUE.
Insert or select an empty column, such as Column D, and enter a header such as Keep.
In cell D2, enter =C2=MAXIFS($C$2:$C$1000,$A$2:$A$1000,A2,$B$2:$B$1000,B2). Adjust 1000 to cover the last row of your data if needed.
Select D2 and drag the fill handle down, or double-click the fill handle, to copy the formula through all data rows.
Select the table, open Data > Filter, then use the filter arrow in Column D and select FALSE to display rows that do not have the highest value.
Select the visible FALSE rows, right-click the row headers, and choose Delete. Clear the filter, then delete Column D if you no longer need the helper results.
Sort by value and remove duplicates by the two criteria columns
Use this quick method when you only need to keep one highest-value row per Column A and Column B pair.
Keep the Maximum Record for Each Duplicate Criteria Pair with WPS Spreadsheet
WPS Spreadsheet lets you use MAXIFS, filters, sorting, and Remove Duplicates to identify the maximum value for each criteria pair and clean up repeated records without changing your worksheet workflow.
- 1. Open the worksheet in WPS Spreadsheet: Open your XLSX or XLS file in WPS Office and confirm that the criteria are in Columns A and B and the values are in Column C.
- 2. Create a keep-status formula: In an empty helper column, enter =C2=MAXIFS($C$2:$C$1000,$A$2:$A$1000,A2,$B$2:$B$1000,B2) in the first data row.
- 3. Copy the formula through the dataset: Use the fill handle on the selected formula cell to fill the formula down to the final record.
- 4. Filter lower-value records: Choose Data > Filter and filter the helper column for FALSE to show records that should be removed.
- 5. Delete unwanted rows and save: Delete the visible lower-value rows, clear the filter, remove the helper column if desired, and save the cleaned workbook.
Frequently Asked Questions
What does the MAXIFS formula use to identify duplicate groups?
MAXIFS uses the values in Columns A and B as criteria. For each row, it finds the maximum value in Column C among rows with the same A-and-B combination.
Why are multiple rows marked TRUE for one criteria pair?
Multiple rows are marked TRUE when they are tied for the highest value in Column C. The formula keeps every row that equals the group maximum.
How can I keep only one row when the highest values are tied?
Add a tie-breaker, such as the earliest date, lowest ID, or first row number. You can sort by the tie-breaker before using Data > Remove Duplicates, or create an additional helper formula.
Can I remove the helper column after deleting duplicate rows?
Yes. After you filter and delete the FALSE rows, clear the filter and delete the helper column. The remaining data will keep the retained records.
Does sorting before Remove Duplicates preserve the highest value?
Yes, provided you sort the entire table by Column C from largest to smallest first. Then use Data > Remove Duplicates and select only Columns A and B.
