How to Find Duplicate Rows with Matching Excel Entries in WPS Spreadsheets
Question details
Identify rows in WPS Spreadsheets whose complete ten-column selection matches another row, such as people who selected the same ten golfers.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a workbook where people and their selected golfers are stored in rows 3:24, with ten selection columns B:K, and needs to find people who chose the same ten golfers.
- Observed behavior
- The matching selections are not automatically marked, so the user needs a formula or rule that compares all ten columns per row.
Open the workbook in WPS Spreadsheets and verify that each row's selections occupy the same ten columns, for example B3:K24. Remove extra leading or trailing spaces first because COUNTIFS treats values with different spacing as different entries.
Use a COUNTIFS helper column to identify matching selections
Best when you want a clear TRUE/FALSE flag that you can filter, sort, or reuse.
COUNTIFS compares each corresponding column in the current row against the same column in every data row. When every column matches, the count is greater than 1, so the formula returns TRUE.
In WPS Spreadsheets, open your workbook and click cell L3, or the first empty cell to the right of your data.
Type =COUNTIFS($B$3:$B$24,B3,$C$3:$C$24,C3,$D$3:$D$24,D3,$E$3:$E$24,E3,$F$3:$F$24,F3,$G$3:$G$24,G3,$H$3:$H$24,H3,$I$3:$I$24,I3,$J$3:$J$24,J3,$K$3:$K$24,K3)>1 and press Enter.
Click cell L3 again, drag the fill handle down to L24, or double-click the fill handle to apply the formula to all data rows.
Select the header row, go to Home > AutoFilter, click the filter arrow in the helper column, and select TRUE to show only people whose complete selection appears more than once.
Highlight duplicate rows with conditional formatting
Use when you want duplicates to appear directly in the worksheet without adding a helper column.
Flag Duplicate Selections in WPS Spreadsheets with COUNTIFS
With WPS Spreadsheets, you can open the workbook and apply the same COUNTIFS helper-column or conditional-formatting approach directly. All matching rows are flagged, and the file remains fully compatible with Microsoft Excel.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office and open the .xlsx file that contains the golfer selections in columns B:K.
- 2. Add the COUNTIFS helper formula: Click cell L3, enter =COUNTIFS($B$3:$B$24,B3,$C$3:$C$24,C3,$D$3:$D$24,D3,$E$3:$E$24,E3,$F$3:$F$24,F3,$G$3:$G$24,G3,$H$3:$H$24,H3,$I$3:$I$24,I3,$J$3:$J$24,J3,$K$3:$K$24,K3)>1, and press Enter.
- 3. Fill and filter the duplicate flag: Drag the fill handle from L3 to L24, then go to Home > AutoFilter and filter the helper column for TRUE.
- 4. Review or highlight matching rows: Optionally select B3:K24 and apply Home > Conditional Formatting > New Rule with the same COUNTIFS formula to color every matching row.
Frequently Asked Questions
Why does the formula show TRUE for every row that is part of a duplicate set?
Because COUNTIFS counts how many times the complete ten-column selection appears. If three rows have the same ten golfers, each row returns 3, and 3>1 is TRUE.
Can I highlight duplicate rows without adding a helper column in WPS Office?
Yes. Select B3:K24, go to Home > Conditional Formatting > New Rule > Use a formula, and enter the COUNTIFS formula with absolute range references and relative row references. Then choose a fill format.
What if my data range is not rows 3:24 or columns B:K?
Adjust the absolute range references and criteria in the formula to match your actual data range. If you use conditional formatting, write the formula based on the active cell in the selected range.
How do I count how many times each full selection appears?
Remove the >1 test. Use =COUNTIFS($B$3:$B$24,B3,$C$3:$C$24,C3,$D$3:$D$24,D3,$E$3:$E$24,E3,$F$3:$F$24,F3,$G$3:$G$24,G3,$H$3:$H$24,H3,$I$3:$I$24,I3,$J$3:$J$24,J3,$K$3:$K$24,K3). The returned number is the occurrence count for that row's selections.
What if duplicate rows are missed because of leading or trailing spaces?
Use =TRIM() on the selection cells or Find & Replace to remove extra spaces before running COUNTIFS. Values such as "Golfer A" and " Golfer A" are treated as different text.
