Column transform in excel

Option Explicit

'needs reference to Mictosoft Scripting Runtime library
Sub RevertData()
    Dim srcWsh, dstWsh As Worksheet
    Dim i, k  As Long
    Dim sTmp As String
    Dim sValues As Variant
    Dim myDictionary As Dictionary
    
    'create dictionary object
    Set myDictionary = New Dictionary
    
    'define source sheet
    Set srcWsh = ThisWorkbook.Worksheets("Sheet1")
    'define destinate sheet
    Set dstWsh = ThisWorkbook.Worksheets("Sheet2")
    'start with row no. 2
    'counter - sheet 1
    i = 2
    Do While srcWsh.Range("A" & i) <> ""
        sValues = Split(srcWsh.Range("A" & i), ", ")
        For k = LBound(sValues) To UBound(sValues)
            'Debug.Print CStr(sValues(k))
            sTmp = sValues(k)
            sTmp = Replace(sTmp, "cn=", "")
            sTmp = Replace(sTmp, """", "")
            If Not myDictionary.Exists(sTmp) Then
                myDictionary.Add sTmp, CStr(srcWsh.Range("B" & i)) & ", "
            Else
                myDictionary(sTmp) = myDictionary(sTmp) & CStr(srcWsh.Range("B" & i)) & ", "
            End If
        Next k
        'increase counter
        i = i + 1
    Loop
    
    'counter - sheet 2
    i = 2
    For Each sValues In myDictionary.Keys
        dstWsh.Range("A" & i) = CStr(sValues)
        dstWsh.Range("B" & i) = myDictionary(CStr(sValues))
        i = i + 1
    Next
    
    Set dstWsh = Nothing
    Set srcWsh = Nothing
    
End Sub