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?

Excel VBA macro can be handy in this case. Is it acceptable? — shrivallabha.redij, Jul 02 '20 at 10:57
Is the relationship between "Parent Product" and "Product" guaranteed to be One-to-Many, or is it Many-to-Many? (i.e. if "PP-001 / G-10", "PP-001 / G-11" and "PP-002 / H-20" all exist, is it possible for "PP-002 / G-10" to also exist?) — Chronocidal, Jul 02 '20 at 12:34

score 3 · Answer 1 · answered Jul 02 '20 at 11:05

3

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.

answered Jul 02 '20 at 11:05

JvdV

70,606
8
39
70

Thank you..let me try out. if it works with formula nothing better – antnewbee Jul 02 '20 at 11:16
2

Unique on C2 alone is bit riskier as OP has indicated his unique criteria depends on two columns. It will miss one result in such a case but that part only OP can confirm. – shrivallabha.redij Jul 02 '20 at 11:23

score 3 · Accepted Answer · answered Jul 02 '20 at 11:06

Very easy with Power Query, available in Excel 2010+ In this case, all can be done from the User Interface.

Select some cell in the table
Data --> Get & Transform --> From Table/Range
Select the Parent Product and Product columns and Group By
- Operation:= All Rows

Add Custom Column
- Formula: =Table.Column([Grouped],"Sequence")
- New Column Name: Sequence

Select the double headed arrow at the top of the Sequence Column
- Extract values with comma separated
Delete the extra columns and rearrange.

M-Code

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Parent Product", type text}, {"Sequence", type any}, {"Product", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Parent Product", "Product"}, {{"Grouped", each _, type table [Parent Product=text, Sequence=anynonnull, Product=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Sequence", each Table.Column([Grouped],"Sequence")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Sequence", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Reordered Columns" = Table.ReorderColumns(#"Extracted Values",{"Parent Product", "Sequence", "Product", "Grouped"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Grouped"})
in
    #"Removed Columns"

@antnewbee Depending on your requirements for the output formatting, you could consider adding a sort to the query as the last step. — Ron Rosenfeld, Jul 02 '20 at 12:17

score 1 · Answer 3 · answered Jul 02 '20 at 11:19

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

Consolidate rows in Excel sheet and show as comma separated

3 Answers3