VBA实现将excel按照某列值相同行合并另一列值为逗号分隔

使用vba实现按照A列值相同的行，将B列值合并为逗号分隔的字符串，将合并后的结果写入D和E两列，即A->D ,,B->E列

vba代码：

Sub MergeAndDistinct()
    Dim dict As Object
    Dim lastRow As Long
    Dim i As Long
    Dim key As Variant
    Dim cellValue As String
    
    Set dict = CreateObject("Scripting.Dictionary")
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    ' 收集数据
    For i = 2 To lastRow
        key = Cells(i, 1).Value
        cellValue = Cells(i, 2).Value
        
        If key <> "" Then
            If dict.exists(key) Then
                If InStr(1, dict(key), cellValue) = 0 Then
                    dict(key) = dict(key) & ", " & cellValue
                End If
            Else
                dict(key) = cellValue
            End If
        End If
    Next i
    
    ' 输出结果
    Range("D2").Resize(dict.Count, 2).ClearContents
    i = 2
    For Each key In dict.keys
        Cells(i, 4).Value = key
        Cells(i, 5).Value = dict(key)
        i = i + 1
    Next key
End Sub
 

实现效果，如图：