0

I have a table similar to the image below. Column A has repeated values, while column B has values corresponding to column A. e.g. a single customer can have multiple orders.

enter image description here

What I would like to do is combine the rows, so that there is only one row per unique column A value (customer) and in the column B for that row, all column B values (orders) are gathered together in a comma separated list.

2 Answers 2

1

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
enter image description here

1
  • Thank you. Worked like a charm for combining customer data.
    – Mike
    Jul 20, 2022 at 14:42
0

If you are using Office 2019/365, the TEXTJOIN function provides convenience for this.

=TEXTJOIN(",",1,IF(A:A=D2,B:B,""))

enter image description here

1
  • according to his tags, he's using 2013. But this might be good for another user searching for a solution. Jul 12, 2021 at 12:29

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .