CODEWITHSUNDEEP
excelexcel-formulapowerquery
antnewbee
antnewbee

Reputation: 1949

Consolidate rows in Excel sheet and show as comma separated

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.

Current data

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

enter image description here

Upvotes: 0

Views: 4048

Answers (3)

shrivallabha.redij
shrivallabha.redij

Reputation: 5902

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

Upvotes: 1

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

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

enter image description here

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

enter image description here

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

enter image description here

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"

Upvotes: 3

JvdV
JvdV

Reputation: 75860

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

enter image description here

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.

Upvotes: 3

Related Questions