How to Find Different Values in Column B for Duplicate IDs in Excel
Question details
The user needs to compare associated values in Column B for duplicate IDs in Column A and return the different or unusual value when one ID has inconsistent data.

- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a table with IDs in Column A and related values in Column B. Some IDs appear more than once, and the user wants to identify which duplicate IDs have inconsistent values in Column B.
- Observed behavior
- For some duplicate IDs, Column B contains more than one distinct value; the user wants to return or flag the differing value instead of treating the ID as consistent.
Confirm that the duplicate IDs are in Column A and the values to compare are in Column B. If your data has headers, start formulas from row 2 and adjust the $2:$1000 range to match your actual last data row.
Use a Dynamic Array Formula to Return Distinct Values for Each Duplicate ID
Use this method in Microsoft 365, Excel 2021, or a WPS Office version that supports LET, UNIQUE, and FILTER. It returns TRUE when all values for an ID match, or spills the distinct Column B values when they differ.
This formula filters Column B for the current ID, keeps only unique values, and tests whether only one unique value exists. When there are multiple unique values, it spills them horizontally so you can still copy the formula down Column C.
Click cell C1 and type CheckResult. Then click cell C2, where the first result will start.
In C2, enter =LET(id,A2,values,UNIQUE(FILTER($B$2:$B$1000,$A$2:$A$1000=id)),IF(ROWS(values)=1,TRUE,TRANSPOSE(values))) and press Enter.
Change $B$2:$B$1000 and $A$2:$A$1000 to match your actual data range, for example $B$2:$B$5000 and $A$2:$A$5000.
Drag the fill handle down Column C to evaluate each ID row. Keep cells to the right of C2 empty so the distinct values can spill horizontally without being blocked.

Use COUNTIFS Helper Columns for Older Excel and WPS Office Versions
Use this method when LET, UNIQUE, or FILTER is not available. It flags every row that belongs to an ID group with inconsistent Column B values.
Find Different Column B Values for Duplicate IDs with WPS Office
Use WPS Office Spreadsheets to compare duplicate IDs and return inconsistent values with Excel-compatible formulas. You can use the dynamic array formula when supported, or fall back to COUNTIFS helper columns in older versions.
- 1. Open the workbook: Open the Excel workbook in WPS Office Spreadsheets.
- 2. Enter the dynamic formula: In C1, type CheckResult. In C2, enter the LET, UNIQUE, FILTER, and TRANSPOSE formula and adjust the ranges to your data.
- 3. Copy the formula down: Drag the formula down Column C to evaluate each ID row. Keep adjacent cells to the right empty for spill results.
- 4. Use the helper-column fallback if needed: If your WPS version does not support dynamic arrays, add PairCount and IDCount columns with COUNTIFS and COUNTIF, then use the Status formula to flag mismatches.
- 5. Save the workbook: Save the file as .xlsx to keep the formulas compatible with Microsoft Excel.

Frequently Asked Questions
Will this work in older Excel versions?
Yes. The helper-column method uses COUNTIFS and COUNTIF, which are available in Excel 2007 and later. The dynamic LET, UNIQUE, and FILTER formula requires Microsoft 365, Excel 2021, or a WPS Office version that supports dynamic arrays.
What if I see #SPILL! after entering the dynamic formula?
The spill range is blocked by data in adjacent cells. Clear the cells to the right of C2 before entering the formula, or use the COUNTIFS helper-column method instead.
How do I adjust the formula for more than 1,000 rows?
Change $A$2:$A$1000 and $B$2:$B$1000 to your actual last data row, for example $A$2:$A$5000 and $B$2:$B$5000. Keep the absolute references so the ranges stay fixed as you fill down.
What does TRUE mean in the dynamic formula result?
TRUE means every Column B value for that ID is identical, so there is no different or unusual value to return for that duplicate ID.




