按顺序比较两列中的逗号分隔值并给出匹配的值 - 需要帮助

Question

你好，我有一个挑战，我必须比较两列（列 A、列 B）中用逗号分隔的值，并找到连续用逗号分隔的匹配值并输出到结果列。

例子：

将“A 列”值（逗号分隔）与“B 列”（逗号分隔）进行比较，并将按顺序逗号分隔的匹配值（逗号分隔）输出到“结果”列

任何帮助是极大的赞赏。

Answer 1

为了生成“按顺序用逗号分隔的匹配值”，我建议您使用 VBA 代码，而不是任何复杂的公式，这样更快更好。

1. 按 ALT+F11 打开 VB 编辑器窗口。
2. 从项目资源管理器窗口中选择工作表。
3. 右键单击并选择插入、模块。
4. 然后复制并粘贴两者。

如何使用： =GetDiffs1(A2,B2)&", "& GetDiffs2(A2,B2)

Function GetDiffs1(Cell1 As Range, Cell2 As Range) As String
Dim Array1, Array2, lLoop As Long
Dim strDiff As String, strDiffs As String
Dim lCheck As Long


Array1 = Split(Replace(Cell1, " ", ""), ",")
Array2 = Split(Replace(Cell2, " ", ""), ",")
On Error Resume Next
With WorksheetFunction
    For lLoop = 0 To UBound(Array1)
        strDiff = vbNullString
        strDiff = .Index(Array2, 1, .Match(Array1(lLoop), Array2, 0))
        If strDiff = vbNullString Then
            lCheck = 0
            lCheck = .Match(Array1(lLoop), Array2, 0)

            If lCheck = 0 Then
                strDiffs = strDiffs & "," & Array1(lLoop)
            End If
        End If

    Next lLoop
End With

GetDiffs1 = Trim(Right(strDiffs, Len(strDiffs) - 1))
End Function

Function GetDiffs2(Cell1 As Range, Cell2 As Range) As String
Dim Array1, Array2, lLoop As Long
Dim strDiff As String, strDiffs As String
Dim lCheck As Long


Array1 = Split(Replace(Cell1, " ", ""), ",")
Array2 = Split(Replace(Cell2, " ", ""), ",")
On Error Resume Next
With WorksheetFunction
    For lLoop = 0 To UBound(Array2)
        strDiff = vbNullString
        strDiff = .Index(Array1, 1, .Match(Array2(lLoop), Array1, 0))
        If strDiff = vbNullString Then
            lCheck = 0
            lCheck = .Match(Array2(lLoop), Array1, 0)

            If lCheck = 0 Then
                strDiffs = strDiffs & "," & Array2(lLoop)
            End If
        End If

    Next lLoop
End With

GetDiffs2 = Trim(Right(strDiffs, Len(strDiffs) - 1))
End Function