watch?v=6c3oYe6TYp0

원시데이터

Name	ID	Product
AAA	11111	Bible
BBB	22222	Water
AAA	11111	NoteBook
BBB	22222	Pc
CCC	33333	Mouse
AAA	11111	Dictionary
DDD	44444	Keyboard

'-------------------------------------------'
Sub get_key_values()
'-------------------------------------------
'https://www.youtube.com/watch?v=6c3oYe6TYp0
'도구-참조-Microsoft Scripting Runtime
    Dim varX As Variant: varX = Range("B2").CurrentRegion.Value '1부터 시작
    Dim i As Long
    Dim oDic As New Scripting.Dictionary ' key, value
    Dim sKey As String ' key = name+id
    Dim oList As Object
    
    '-------------------------------------------
    For i = 2 To UBound(varX, 1)
    '-------------------------------------------
    
        ' Name, Id를 키로 사용
        sKey = varX(i, 1) & "-" & varX(i, 2)
        
        '-------------------------------------------
        If oDic.Exists(sKey) Then ' 키가 있으면
        '-------------------------------------------
             oDic.Item(sKey).Add varX(i, 3)
             
        Else ' 키가 없으면
            '
            Set oList = CreateObject("System.collections.ArrayList")
            oList.Add varX(i, 3)
            
            oDic.Add sKey, oList 'key, value(arraylist)
            
        End If
        
        
    Next i
    
    ' 데이타 붙여 넣을 시작 셀
    Dim rngX As Range: Set rngX = [B11]

    Dim vKey As Variant, vValue As Variant
  
    ' Dictionary Key를 돌며
    For Each Key In oDic.Keys
    
        ' "-"구분자로 배열로 변환
        vKey = Split(Key, "-")
        ' Name, Id입력
        rngX.Resize(1, 2).Value = vKey ' vKey(0) AAA, vKey(1) 11111
        ' 소트
        oDic.Item(Key).Sort
        vValue = oDic.Item(Key).toArray ' ArrayList의 요소를 배열로 변환
        rngX.Offset(0, 2).Value = Join(vValue, ",")
'        rngX.Offset(0, 2).Resize(1, UBound(vValue, 1) + 1).Value = vValue
        Set rngX = rngX.Offset(1)
    
    Next
    
    
End Sub

Dim varX As Variant: varX = Range("B2").CurrentRegion.Value