Your results can be produced using Power Query
, available in Windows Excel 2010+ and Office 365.
To use Power Query
- Select some cell in your Data Table
Data => Get&Transform => from Table/Range
- When the PQ Editor opens:
Home => Advanced Editor
- Make note of the Table Name in Line 2
- Paste the M Code below in place of what you see
- Change the Table name in line 2 back to what was generated originally.
- Read the comments and explore the
Applied Steps
to understand the algorithm
let
Source = Excel.CurrentWorkbook(){[Name="Table29"]}[Content],
//Note that we type "order" as text -- so as to be able to use in next step without converting.
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer", type text}, {"Order", type text}}),
//Group rows and aggregate the text string
#"Grouped Rows" = Table.Group(#"Changed Type", {"Customer"}, {
{"Order", each Text.Combine([Order],","), type text}
})
in
#"Grouped Rows"
Data and Results