Excel公式需求：按Col1唯一值校验Col2重复与Col3唯一性规则

DevOps 小满

2026-5-26

Formula Logic Requirement & Test Data

Let’s start by clarifying the core rule we need to implement, then organize your test data into a clean, readable format.

Core Logic

For each unique value in Col1 (which maps to the Bill column in your dataset):

If Col2 (the Ref column) contains duplicate values within the Col1 group, then Col3 (the Ref2 column) should not be unique (meaning duplicates in Col3 are acceptable/expected in this scenario).

Test Data

Here’s your test data structured into a Markdown table for clarity (note: the duplicate Form column is preserved as provided):

Bill	Ref	Ref2	HelpText	Form	Form	Excptns	Expected Output
123	557	123	Scenario1	1	1	FALSE	FALSE
123	589	123-1	Scenario1	1	1	FALSE	FALSE
123	591	123-2	Scenario1	2	1	TRUE	FALSE
123	591	123-3	Scenario2	2	1	TRUE	TRUE
124	432	124	Scenario1	1	1	FALSE	FALSE
124	433	...	...	...	...	...	...

Quick Scenario Notes

Scenario1 (Bill=123, Ref=557/589): These Ref values are unique in the group, so Ref2 must stay unique. The expected FALSE output confirms no rule violation here.
Scenario1/2 (Bill=123, Ref=591): This Ref is duplicated. The third row’s Ref2 is still unique in the duplicate Ref subgroup, hence the expected FALSE (violation). The fourth row’s Ref2 pairs with the third to create a non-unique set in the subgroup, so expected TRUE (compliance).