Consolidate rows in Excel sheet and show as comma separated

Question

I have the data in excel as below where combination of Column A and Column C makes a unique combination. As we can see there are duplicate rows(considering Column A & C). How can I consolidate the rows based on Column A & C and show row B as comma separated. Column A is sorted in my current sheet.

I have seen the question here which is similar but it is considering the unique values based on only 1 column.

I want the data to be represented as below. Is this possible?

Answer 1

If you are after formulas, and you got Excel O365, you could try:

Formula in E2, dragged down:

=INDEX(A:A,MATCH(G2,C:C,0)) 

Formula in F2, dragged down:

=TEXTJOIN(",",,FILTER(B2:B9,C2:C9=G2))

Formula in G2:

=UNIQUE(C2:C9)

---

Though I'm pretty sure this can be done through PowerQuery avoiding formulas and VBA alltogether.

Answer 2

Here's VBA based approach.

    Public Sub BuildConsolidatedList()
    Dim srcWks As Worksheet, dstWks As Worksheet
    Dim objDict As Object
    Dim i As Long
    Dim k
    Set srcWks = ThisWorkbook.Sheets("Sheet1") '\\ Define Source Sheet Here
    Set dstWks = ThisWorkbook.Sheets("Sheet2") '\\ Define Destination Sheet Here
    Set objDict = CreateObject("Scripting.Dictionary")
    objDict.CompareMode = vbTextCompare
    For i = 2 To srcWks.Range("A" & srcWks.Rows.Count).End(xlUp).Row '\\ Loop through Source Data and build up unique entries
        If objDict.Exists(srcWks.Range("A" & i).Value & "|" & srcWks.Range("C" & i).Value) Then
            objDict.Item(srcWks.Range("A" & i).Value & "|" & srcWks.Range("C" & i).Value) = _
            objDict.Item(srcWks.Range("A" & i).Value & "|" & srcWks.Range("C" & i).Value) & "," & srcWks.Range("B" & i).Value
        Else
            objDict.Add srcWks.Range("A" & i).Value & "|" & srcWks.Range("C" & i).Value, srcWks.Range("B" & i).Value
        End If
    Next i
    i = 2 '\\ Destination sheet start row
    For Each k In objDict.Keys '\\ Loop through all values in dictionary object
        dstWks.Range("A" & i).Value = Split(k, "|")(0)
        dstWks.Range("B" & i).Value = objDict.Item(k)
        dstWks.Range("C" & i).Value = Split(k, "|")(1)
        i = i + 1
    Next k
    End Sub