Transpose required.

VBA option
- run in attached workbook with {CTRL} k
- Results are based on values in sheet "Data"

Sub ProcessAndTransform()
'variables
    Dim coll As New Collection, i As Long, cel As Range, rStr As String, res As String
    Dim rng As Range: Set rng = Data.Range("A2", Data.Range("A" & Rows.Count).End(3))
'column headers
    Results.Range("A1:B1").Value = Data.Range("A1:B1").Value
'create unique list in collection
    For Each cel In rng
        On Error Resume Next
            coll.Add cel.Value, cel.Value
        On Error GoTo 0
    Next cel
'build string and write to worksheet
    For i = 1 To coll.Count
        For Each cel In rng
            res = cel.Offset(, 1).Value
            If res = vbNullString Then res = Chr(34) & Chr(34)
            If coll(i) = cel.Value Then
               rStr = rStr & "," & res
            End If
        Next cel
            Results.Cells(i + 1, 1) = coll(i)
            Results.Cells(i + 1, 2) = Right(rStr, Len(rStr) - 1)
            rStr = ""
    Next i
End Sub

Explanatory notes
- use of collection to create a unique list of values because collections do not allow duplicate keys
- sheets are referred via their codenames not their tab names (here they happen to be identical)