Function Problems

WPS Spreadsheet: Find Rows with Inconsistent Values for Matching Numbers

Khadija KhanKhadija Khan Nov 12, 2026 868 views

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.
Before you start

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.

Solution 1Recommended

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.

1
Add a helper column

In cell C1, enter a header such as Inconsistent, then select cell C2 for the formula.

2
Enter the inconsistency formula

In C2, enter =COUNTA(UNIQUE(FILTER($B$2:$B$10000,$A$2:$A$10000=A2)))>1 and press Enter.

3
Fill the formula down

Use the fill handle on C2 or copy C2 and paste it through the rows containing data.

4
Filter flagged records

Select the table, open the Data tab, choose Filter, and filter column C to show only TRUE values.

Use UNIQUE and FILTER to flag inconsistent matching values
Interpret the result: TRUE means the matching number in column A is linked to more than one unique value in column B. FALSE means it has only one unique associated value.
WPS Spreadsheet solution

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. 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. 2. Enter the formula: Select C2 and enter =COUNTA(UNIQUE(FILTER($B$2:$B$10000,$A$2:$A$10000=A2)))>1.
  3. 3. Fill the helper column: Copy the formula down the data range using the fill handle or copy-and-paste.
  4. 4. Filter inconsistent rows: On the Data tab, choose Filter and set the helper column to TRUE.
Quickly identifies numbers associated with multiple statuses or valuesUses a filterable TRUE/FALSE helper column for row-level reviewSupports Excel XLSX and XLS file formats for smooth migrationProvides familiar formulas, filters, and PivotTable tools in a lightweight spreadsheet app
microsoft office alternative - wps office

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.