Function Problems

How to Check if Duplicate Excel Values Have Matching Related Values

Steve KSteve K Nov 11, 2026 868 views

Question details

The user wants to verify whether duplicate values in one column (e.g., PLU codes) consistently correspond to a single unique value in another related column, outputting TRUE or FALSE.

How to Check if Duplicate Excel Values Have Matching Related Values
Product
Excel
Device & OS
not provided
Scenario
Performing data validation and deduplication checks within a structured table to ensure related data is consistent.
Observed behavior
The user needs a formula that accurately maps identical entries in a primary column to test for conflicting or matching information in a secondary column.
Before you start

Ensure your data is formatted as a structured table (Ctrl+T) so you can use column names easily, and verify your software version supports modern dynamic array functions like UNIQUE and FILTER.

Solution 1Recommended

Use UNIQUE and FILTER Functions to Verify Related Values

Combine the COUNTA, UNIQUE, and FILTER functions to determine if duplicates map to exactly one distinct value in a corresponding column.

This formula uses the FILTER function to isolate all related values associated with a specific duplicate. The UNIQUE function then removes any repeated values from that filtered list. Finally, COUNTA checks how many distinct values remain. If only 1 remains, it outputs TRUE (meaning all duplicates share the exact same related value).

1
Select the target column

Click on an empty cell in a new column next to your structured data table where you want the TRUE/FALSE results to appear.

2
Enter the combination formula

Type the formula `=COUNTA(UNIQUE(FILTER(Table1[Related_Column],Table1[Duplicate_Column]=[@Duplicate_Column])))=1`. Replace 'Table1' with your table name, 'Related_Column' with the column you are testing for consistency, and 'Duplicate_Column' with your primary column (like PLU).

3
Apply the formula

Press Enter to execute the formula. Because you are working in a structured table, the formula should automatically spill down to the rest of the rows.

4
Review the TRUE/FALSE results

Examine the output. A 'TRUE' result confirms that all instances of that duplicate have the exact same related value. A 'FALSE' result indicates there are conflicting related values for that duplicate.

Use UNIQUE and FILTER Functions to Verify Related Values
Tip for Standard Ranges: If you are not using a structured table, you can replace the table references with absolute cell references, such as `=COUNTA(UNIQUE(FILTER($B$2:$B$100,$A$2:$A$100=A2)))=1`.
Advanced Data Analysis in WPS Spreadsheet

Analyze Data Effortlessly with WPS Office

WPS Spreadsheet provides robust, out-of-the-box support for modern dynamic array formulas, making it easy to cross-reference duplicates and analyze complex datasets.

  1. 1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your workbook containing the duplicate data.
  2. 2. Format as a Table: Select your data and press Ctrl+T to convert it into a structured table for easier formula referencing.
  3. 3. Apply the dynamic formula: Paste your `=COUNTA(UNIQUE(FILTER(...)))` formula into a blank column next to your data.
  4. 4. Instantly view validation results: Hit Enter to evaluate the formula and seamlessly pinpoint inconsistent duplicate values across your entire dataset.
Fully compatible with Microsoft Excel formulas and table structures.Native support for advanced array functions including UNIQUE, FILTER, and XLOOKUP.Free, fast, and lightweight alternative for everyday spreadsheet analysis.
microsoft office alternative - wps office

Frequently Asked Questions

Why does my formula return a #NAME? error?

The #NAME? error occurs if your spreadsheet software does not support dynamic array functions like UNIQUE and FILTER. Upgrading to the latest version of WPS Office or Microsoft 365 will resolve this compatibility issue.

How do I visually highlight duplicates with mismatched related values?

You can use Conditional Formatting. Select your data range, click Conditional Formatting > New Rule > 'Use a formula to determine which cells to format', and enter the same formula but ending in `>1` instead of `=1`. Set a fill color to highlight the inconsistent rows.

Does this formula work across different worksheets?

Yes. You can reference data on another worksheet by prefixing the table or range references with the sheet name (e.g., `Sheet2!Table1[PORTION]`). The underlying logic of the UNIQUE and FILTER functions remains exactly the same.