多列重复项识别及完整性规则实现（Google Sheets公式需求）

过去数月分析数据时发现，基于Company Name、First Name、Last Name、Addr1、Phone这五列识别重复项耗时极长，已将这些列标题标黄。

重复项处理规则 #

• 若Company Name重复但First/Last Name不重复，或仅Addr1、Phone重复，不判定为重复行；
• 若同行其他列匹配但存在空单元格，删除该行；
• 五列全匹配时仅保留一行。

输出要求 #

辅助列需生成数字结果：2=保留行，1=删除重复行，0=唯一行。已在Google Sheets中标记示例（绿色行为重复项，最后列标注保留/删除）。

原尝试公式 #

=LET(
  colC,$B$2:$B$10000, colF,$C$2:$C$10000, colL,$D$2:$D$10000, colA,$E$2:$E$10000, colP,$J$2:$J$10000,
  c,B2, f,C2, l,D2, a,E2, p,J2,
  matches,(IF(c="",TRUE,colC=c))*(IF(f="",TRUE,colF=f))*(IF(l="",TRUE,colL=l))*(IF(a="",TRUE,colA=a))*(IF(p="",TRUE,colP=p)),
  scoreAll,(colC<>"")+(colF<>"")+(colL<>"")+(colA<>"")+(colP<>""),
  myScore,(c<>"")+(f<>"")+(l<>"")+(a<>"")+(p<>""),
  maxScore,MAX(IF(matches,scoreAll)),
  hasPhoneAtMax,MAX(IF(matches*(scoreAll=maxScore),--(colP<>""))),
  candidates, matches*(scoreAll=maxScore)*(IF(hasPhoneAtMax=1,colP<>"",TRUE)),
  earliest, MIN(IF(candidates,ROW($B$2:$B$1000))),
  IF(myScore=0,"Delete it", IF(AND(myScore=maxScore, IF(hasPhoneAtMax=1,p<>"",TRUE), ROW()=earliest),"Keep","Delete it"))
)

原公式问题分析 #

1. 匹配逻辑偏差：原公式将空单元格视为匹配任意值，会误判部分仅部分列重复的行，不符合规则1；
2. 输出格式不符：原公式输出文本（Keep/Delete it），未按要求输出数字0/1/2；
3. 范围错误：earliest的行范围$B$2:$B$1000与前面定义的$B$2:$B$10000不一致，导致部分行无法正确识别最早行；
4. 唯一行未标记：原公式未处理「唯一行」场景，无法输出0。

优化后的公式 #

=LET(
  // 定义目标列范围（对应Company Name、First Name、Last Name、Addr1、Phone）
  cols,$B$2:$J$10000,
  compCol,INDEX(cols,,1), firstCol,INDEX(cols,,2), lastCol,INDEX(cols,,3), addrCol,INDEX(cols,,4), phoneCol,INDEX(cols,,9),
  // 当前行的五个字段值
  comp,B2, first,C2, last,D2, addr,E2, phone,J2,
  // 生成唯一匹配键：仅当五个字段全非空且完全匹配时视为同一组
  matchKey, comp&"|"&first&"|"&last&"|"&addr&"|"&phone,
  groupKey, IF((comp<>"")*(first<>"")*(last<>"")*(addr<>"")*(phone<>""), matchKey, ""),
  // 统计当前组的行数
  groupCount, COUNTA(FILTER(groupKey, groupKey=matchKey)),
  // 判断当前行是否为全字段非空的有效行
  isFullRow, (comp<>"")*(first<>"")*(last<>"")*(addr<>"")*(phone<>""),
  // 确定当前组的保留行：全字段非空的最早行
  keepRow, IF(isFullRow, MIN(FILTER(ROW($B$2:$B$10000), groupKey=matchKey)), FALSE),
  // 按规则输出数字结果
  SWITCH(
    TRUE,
    // 规则2：存在空单元格→删除（输出1）
    NOT(isFullRow), 1,
    // 规则3：组内多行，当前行是保留行→输出2；其他重复行→输出1
    groupCount>1, IF(ROW()=keepRow, 2, 1),
    // 唯一行→输出0
    0
  )
)

优化说明 #

1. 匹配逻辑修正：通过matchKey拼接五个字段，仅当全部非空且完全匹配时才视为重复组，严格符合规则1；
2. 规则全覆盖：
   • 非全字段非空的行直接标记为1（删除）；
   • 全匹配组内仅保留最早的全字段行（标记2），其余重复行标记1；
   • 唯一行标记0；
3. 输出符合要求：直接输出数字0/1/2，无需额外转换；
4. 性能优化：使用FILTER替代数组循环判断，减少计算耗时，适配10000行数据集；
5. 范围统一：所有行范围统一为$B$2:$B$10000，避免范围不一致导致的错误。

内容的提问来源于stack exchange，提问作者HSHO