How to Use an Excel Formula to Detect Duplicate Values in Selected Cells in WPS Spreadsheets
Question details
The user needs an Excel-compatible formula that displays "Error" when two or more selected nonblank cells contain the same value, comparing the number of selected cells with the number of unique values.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is creating a spreadsheet validation rule and wants to flag duplicate entries in selected cells such as A1, A3, A5, A7, A9, and A11.
- Observed behavior
- The formula should return "Error" only when duplicate nonblank values exist in the selected cells; otherwise it returns an empty string.
Check that your WPS Spreadsheets version supports the functions you plan to use. WPS Office 2021 or later can handle LET, FILTER, and UNIQUE, while the COUNTIF method works in all modern WPS and Excel versions.
Use LET, FILTER, and UNIQUE to Detect Duplicates in Non-Contiguous Selected Cells
Use this method when you need to check specific cells such as A1, A3, A5, A7, A9, and A11 without counting intervening blank cells or other rows.
This formula filters the selected cells by row position and ignores blanks, then compares the number of rows in the filtered result with the number of unique values. If the unique count is smaller, at least one duplicate exists.
Click the cell where you want the result to appear, such as B1.
Type =LET(x,FILTER(A1:A11,ISODD(ROW(A1:A11))*NOT(ISBLANK(A1:A11))),IF(ROWS(UNIQUE(x))<ROWS(x),"Error","")) in the formula bar and press Enter.
If two or more of the selected nonblank cells contain the same value, the result cell displays Error; otherwise it remains empty.
Use COUNTIF with MAX to Detect Duplicates in a Contiguous Range
Use this simpler option when the cells are in one continuous range, such as A1:A11.
Detect Duplicate Values in Selected Cells with WPS Spreadsheets
You can complete this directly in WPS Spreadsheets using Excel-compatible formulas. Enter the LET, FILTER, and UNIQUE formula for non-contiguous selected cells, or use COUNTIF for a contiguous range, and WPS will return "Error" when duplicates are found.
- 1. Open WPS Spreadsheets: Launch WPS Office on your computer and open the workbook where you want to detect duplicate values.
- 2. Select the result cell: Click the cell where you want the formula result to appear, for example B1.
- 3. Enter the duplicate detection formula: Type =LET(x,FILTER(A1:A11,ISODD(ROW(A1:A11))*NOT(ISBLANK(A1:A11))),IF(ROWS(UNIQUE(x))<ROWS(x),"Error","")) and press Enter. For a contiguous range, use =IF(MAX(COUNTIF(A1:A11,A1:A11))>1,"Error","").
- 4. Check the result: If any two selected nonblank cells contain the same value, the cell displays Error; otherwise it stays blank.
Frequently Asked Questions
Can I use this formula in WPS Spreadsheets if I don't have Microsoft 365?
Yes. WPS Spreadsheets supports many Excel formulas. The simpler =IF(MAX(COUNTIF(A1:A11,A1:A11))>1,"Error","") works for a contiguous range, while LET, FILTER, and UNIQUE require a version with dynamic array support.
How do I adapt the formula for selected cells that are not every other row?
Replace the ISODD(ROW(A1:A11)) condition with your own condition, or place the specific cells in a helper range. The principle remains the same: filter to the desired nonblank cells and compare ROWS(UNIQUE(x)) with ROWS(x).
Why does the formula return a formula error instead of the word Error?
Check that your function names match your language version of WPS or Excel and that text strings are enclosed in double quotes, such as "Error". In older versions, LET and FILTER may not be available, so use the COUNTIF method instead.
What if I want to highlight duplicates instead of returning Error?
In WPS Spreadsheets, select the range, go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values, and choose a highlight style.
Does the COUNTIF method count blank cells as duplicates?
Yes, if a contiguous range contains multiple blank cells, they can be counted as duplicates. To ignore blanks, combine COUNTIF with COUNTIFS or wrap the formula so blanks are excluded before checking.
