Function Problems

How to Find Different Values in Column B for Duplicate IDs in Excel

Amos GikundaAmos Gikunda Nov 13, 2026 868 views

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.

How to Find Different Values in Column B for Duplicate IDs in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Add a results column

Click cell C1 and type CheckResult. Then click cell C2, where the first result will start.

2
Enter the dynamic array formula

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.

3
Adjust the data range

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.

4
Copy the formula down

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 a Dynamic Array Formula to Return Distinct Values for Each Duplicate ID
Spill behavior: Enter the formula in one cell only and keep adjacent cells to the right empty. If you see #SPILL!, clear the blocking cells or use the helper-column method below.
Direct solution in WPS Office Spreadsheets

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. 1. Open the workbook: Open the Excel workbook in WPS Office Spreadsheets.
  2. 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. 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. 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. 5. Save the workbook: Save the file as .xlsx to keep the formulas compatible with Microsoft Excel.
Returns distinct Column B values for duplicate IDs with a Microsoft 365-style formula in WPS Office.Provides a COUNTIFS helper-column method that works in older Excel and WPS Office versions.Maintains .xlsx workbook compatibility for seamless switching between WPS Office and Microsoft Excel.Free, lightweight Office suite with a familiar ribbon layout and no subscription required.
microsoft office alternative - wps office

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.