excel - 检测 Excel 单元格中分隔值内的重复

Question

我有一些表格数据如下。

|   | A        | B            | C                | D                                                 |
|---|----------|--------------|------------------|---------------------------------------------------|
|   |          | p1           | p2               | pn                                                |
| 1 | Lanterns | Bruce Wayne  | Jean-Paul Valley | Dick Grayson; Terry McGinnis; Jean-Paul Valley    |
| 2 | Bats     | Alan Scott   | Hal Jordan       | Guy Gardner; John Stewart; Kyle Rayner; Simon Baz |
| 3 | Fates    | Kent Nelson  | Khalid Nassour   | Hector Hall; Khalid Nassour; Khalid Ben-Hassin    |
| 4 | Supes    | Clark Kent   | John Henry Irons | Conner Kent; Hank Henshaw; Kong Kenan             |
| 5 | Spideys  | Peter Parker | Peter Parker     | Ben Reilly; Miles Morales                         |
| 6 | Irons    | Tony Stark   | Happy Hogan      | James Rhodes; Eddie March; James Rhodes           |

对于每一行，我想查找 B 列、C 列以及 D 列的分号分隔值之间是否存在重复。

如何在 Excel 中执行此操作？

所需的输出如下。

| X | A        | B            | C                | D                                                 | E     |
|---|----------|--------------|------------------|---------------------------------------------------|-------|
|   |          | p1           | p2               | pn                                                |       |
| 1 | Lanterns | Bruce Wayne  | Jean-Paul Valley | Dick Grayson; Terry McGinnis; Jean-Paul Valley    | TRUE  |
| 2 | Bats     | Alan Scott   | Hal Jordan       | Guy Gardner; John Stewart; Kyle Rayner; Simon Baz | FALSE |
| 3 | Fates    | Kent Nelson  | Khalid Nassour   | Hector Hall; Khalid Nassour; Khalid Ben-Hassin    | TRUE  |
| 4 | Supes    | Clark Kent   | John Henry Irons | Conner Kent; Hank Henshaw; Kong Kenan             | FALSE |
| 5 | Spideys  | Peter Parker | Peter Parker     | Ben Reilly; Miles Morales                         | TRUE  |
| 6 | Irons    | Tony Stark   | Happy Hogan      | James Rhodes; Eddie March; James Rhodes           | TRUE  |

编辑问题中的列名称存在错误，导致不清楚。立即修复。

更新

这是我按照@Foxfire And Burns And Burns的建议使用VBA进行的尝试。改编自https://superuser.com/a/1005497/460054

Public Function HasDuplicates(list As String, delimiter As String) As String
Dim arrSplit As Variant, i As Long, tmpDict As Object, tmpOutput As Boolean
Set tmpDict = CreateObject("Scripting.Dictionary")
arrSplit = Split(list, delimiter)
tmpOutput = False
For i = LBound(arrSplit) To UBound(arrSplit)
    If tmpDict.Exists(Trim(arrSplit(i))) Then
        tmpOutput = True
        Exit For
    Else
    tmpDict.Add Trim(arrSplit(i)), Trim(arrSplit(i))
    End If
Next i
HasDuplicates = tmpOutput
'housekeeping
Set tmpDict = Nothing
End Function

这里又是 @Foxfire And Burns And Burns 建议的所有可能的用例.

+---+-----+----+-----------+--------------------+-------+
|   |  A  | B  |     C     |         D          |   E   |
+---+-----+----+-----------+--------------------+-------+
| 1 | A   | B  |           | A; B;              | False |
| 2 | A   |    |           | A; ;               | True  |
| 3 |     |    |           | ; ;                | True  |
| 4 | G   | K  | G         | G; K; G            | True  |
| 5 | N   | M  | O         | N; M; O            | False |
| 6 | N   | N  | O         | N; N; O            | True  |
| 7 | V   | U  | X; Y; X   | V; U; X; Y; X      | True  |
| 8 | P J | VK | P; J; V K | P J; VK; P; J; V K | False |
| 9 | VK  | O  | R; VK     | VK; O; R; VK       | True  |
+---+-----+----+-----------+--------------------+-------+

ColumnD 的公式为 =CONCATENATE(B2,"; ",C2, "; ",D2)对于 E 来说是 =HasDuplicates(E2, ";") .

但这里它不处理空单元格。第 2 行和第 3 行也应为 False .

Answer 1

如果您有带有 TEXTJOIN 函数的 O365 或 Excel 2016:

=NOT(ISERROR(FILTERXML("<t><s>" &TEXTJOIN("</s><s>",TRUE,TRIM(B2),TRIM(C2),SUBSTITUTE(TRIM(D2),"; ","</s><s>"))& "</s></t>","//s[.=./following-sibling::*]")))

如果您没有 TEXTJOIN，但有 FILTERXML，则可以使用:

=NOT(ISERROR(FILTERXML("<t><s>"&TRIM(B2)&"</s><s>"&TRIM(C2)&"</s><s>"&SUBSTITUTE(TRIM(D2),"; ","</s><s>")&"</s></t>","//s[.=./following-sibling::*]")))

我们构建一个包含各个节点中所有名称的 XML，然后查找重复项。

如果没有 NOT(ISERROR(... 部分)，公式将返回重复项的名称(如果有多个重复项，则返回名称数组)。

注意:该公式取决于 D 列中的分隔符 ;(分号-空格)。如果空格并不总是存在，则需要修改公式以将其删除(如果存在)(嵌套替换或 TRIM 可以做到这一点)。

例如

=NOT(ISERROR(FILTERXML("<t><s>"&TRIM(B11)&"</s><s>"&TRIM(C11)&"</s><s>"&SUBSTITUTE(SUBSTITUTE(TRIM(D11),"; ",";"),";","</s><s>")&"</s></t>","//s[.=./following-sibling::*]")))

第二次测试结果

如果您有早期版本的 Excel，并且可以使用 VBA 解决方案，请尝试:

Option Explicit
Function hasDups(rg As Range, Optional sDelim As String = ";") As Boolean
    Dim myDict As Object
    Dim x, y, s As String, i As Long, c As Range

Set myDict = CreateObject("scripting.dictionary")

For Each c In rg
    x = Split(c.Value2, sDelim)
    For Each y In x
      If Len(Trim(y)) > 0 Then
        If Not myDict.exists(Trim(y)) Then
            myDict.Add Trim(y), y
        Else
            hasDups = True
            Exit Function
        End If
      End If
    Next y
Next c

End Function