Need to combine rows in excel if one of the cells is the same in VBA

Question

I have the table below:

A	B	C
a	one	apple
b	two	orange
b	three	banana
c	four	grape
c	five	berry
a	six	apple
c	seven	berry

I want the output to be:

A	B	C
a	one, six	apple
b	two, three	orange, banana
c	four, five, seven	grape, berry

I have this code below:

Sub CombineRows()
   Dim lngRow As Long

    With ActiveSheet
        Dim columnToMatch As Integer: columnToMatch = 1
        Dim columnToConcatenate As Integer: columnToConcatenate = 2
        Dim columnToConcatenate2 As Integer: columnToConcatenate2 = 3
        
        lngRow = .Cells(65536, columnToMatch).End(xlUp).Row
        .Cells(columnToMatch).CurrentRegion.Sort key1:=.Cells(columnToMatch), Header:=xlYes

        Do
            If .Cells(lngRow, columnToMatch) = .Cells(lngRow - 1, columnToMatch) Then
                .Cells(lngRow - 1, columnToConcatenate) = .Cells(lngRow - 1, columnToConcatenate) & ", " & .Cells(lngRow, columnToConcatenate)
                .Cells(lngRow - 1, columnToConcatenate2) = .Cells(lngRow - 1, columnToConcatenate2) & ", " & .Cells(lngRow, columnToConcatenate2)
                .Rows(lngRow).Delete
            End If

            lngRow = lngRow - 1
        Loop Until lngRow = 1
    End With

I get this result:

A	B	C
a	one, six	apple, apple
b	two, three	orange, banana
c	four, five, seven	grape, berry, berry

Answer 1

If your version supports dynamics arrays and the table is called Table1, you can do the following.

E2=UNIQUE(Table1[A])
F2=TEXTJOIN(", ",,UNIQUE(FILTER(Table1[B],Table1[A]=E2)))
G2=TEXTJOIN(", ",,UNIQUE(FILTER(Table1[C],Table1[A]=E2)))

Then copy the formula in F2 and G2 down to rows 3 & 4.

Answer 2

You can do this pretty easily with Power Query (available in Excel 2010+)

• With your cursor in the data table, Data => Get&Transfrom => From Table/Range
• When the PQ Editor opens, Home => Advanced Editor
  • Note the Table name on line 2
  • Replace the M Code with code below
  • Change table name on line 2 to your actual table name
• The code Groups By column A, then performs a custom operation to create a unique list of the grouped items in B and C; and concatenates them.

M Code

Note: code simplified from initial post

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"A", type text}, {"B", type text}, {"C", type text}}),
    
    #"Grouped Rows" = Table.Group(#"Changed Type", {"A"}, {
        {"B", each Text.Combine(List.Distinct(_[B]),", "), type text},
        {"C", each Text.Combine(List.Distinct(_[C]),", "), type text}              
        })
in
    #"Grouped Rows"