WPS Spreadsheet: Find Rows with Inconsistent Values for Matching Numbers
Question details
Find records where the same number in one column is associated with different values in another column.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A data table contains matching numbers in column A and statuses or other values in column B. The user needs to identify numbers linked to more than one unique value, such as both Expired and Not Expired.
- Observed behavior
- The user needs a TRUE/FALSE result for each row so inconsistent records can be filtered and reviewed.
Confirm that the matching numbers are in column A and the values to compare are in column B. Check that the data range and starting row match your worksheet before entering the formula.
Use UNIQUE and FILTER to flag inconsistent matching values
Use this method when you have a modern WPS Spreadsheet version with dynamic-array functions and want a TRUE/FALSE flag for each row.
The formula counts the unique values in column B associated with the number in the current row. It returns TRUE when that number has more than one associated value.
In cell C1, enter a header such as Inconsistent, then select cell C2 for the formula.
In C2, enter =COUNTA(UNIQUE(FILTER($B$2:$B$10000,$A$2:$A$10000=A2)))>1 and press Enter.
Use the fill handle on C2 or copy C2 and paste it through the rows containing data.
Select the table, open the Data tab, choose Filter, and filter column C to show only TRUE values.

Review matching groups with a PivotTable
Use a PivotTable when you need a grouped overview of every number and its associated statuses instead of a row-level TRUE/FALSE flag.
Flag inconsistent matching values in WPS Spreadsheet
WPS Spreadsheet can compare repeated numbers against related values with UNIQUE and FILTER, flag inconsistent records, and let you filter the TRUE results for quick review. It also supports familiar spreadsheet workflows for Excel files.
- 1. Open the workbook: Launch WPS Office, open your spreadsheet, and verify that the matching numbers are in column A and related values are in column B.
- 2. Enter the formula: Select C2 and enter =COUNTA(UNIQUE(FILTER($B$2:$B$10000,$A$2:$A$10000=A2)))>1.
- 3. Fill the helper column: Copy the formula down the data range using the fill handle or copy-and-paste.
- 4. Filter inconsistent rows: On the Data tab, choose Filter and set the helper column to TRUE.

Frequently Asked Questions
What does TRUE mean in the inconsistency column?
TRUE means the number in column A is associated with more than one unique value in column B. For example, the same number may appear with both Expired and Not Expired statuses.
Can I use a different data range than rows 2 through 10000?
Yes. Replace $A$2:$A$10000 and $B$2:$B$10000 with the actual ranges in your worksheet. Keep the reference to the current row, such as A2, relative so it changes when filled down.
Why does the formula return an error in my WPS Spreadsheet?
Check that your WPS Spreadsheet version supports FILTER and UNIQUE, that columns A and B contain data, and that the two referenced ranges have the same size. Also verify that the formula uses commas or your installed regional separator.
How can I display only the inconsistent records?
After filling the formula down, select the table, open Data > Filter, and filter the helper column to show TRUE only.




