simplest way in Excel to combine (merge)

[ValentinCVE]

What is the simplest way in Excel to combine (merge) multiple rows data corresponding to a unique value in large dataset? output should be: x=a,b,c,d. Already tried power pivot, pivots.

 

[Kaper]

Probably the easiest method will be by using Power Query.

So:
1) Data-> Get& Transform ... -> From Table/Range (or any other source) and show the source

2) Then PQ editor opens and if the column with data which you want to merge is tot text type click on Content Type icon left to column name on icon header and select text type. if it is already a text (icon Reads ABC) skip this step.

3) Use Transform->Group by -> Basic and use column with identifiers and your data to be concatenated, then you may write your desired column name (I used "Concatenated") than (it's a trick) use Max as operation and your data to be concatenated as Column

4) in advanced editor or just in formula bar change List.Max into Text.Combine and after column with text to be combined add comma and the separator (I used comma and space, but of course coma only is also fine.

5) From Home use Close and Load or Load To


This is the m code which was registered (much shorten than the written description):

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"value", type text}}),
//    this is how it was registered with Max: #"Grouped Rows" = Table.Group(Source, {"id"}, {{"Concatenated", each List.Max([value]), type any}})
    #"Grouped Rows" = Table.Group(#"Changed Type", {"id"}, {{"Concatenated", each Text.Combine([value], ", "), type any}})
in
    #"Grouped Rows"

I'd say this method is 2 steps only, because only steps 3 and 4 above are "real actions" The whole thing is done with about dozen of clicks of mouse and writing 3 words. So really simple.

 

[PeteWright]

Try the Excel Add-on called ASAP Utilities for Excel

 

[ValentinCVE]

Thank you very much for the answers. Kaper, your method helped me the most. I asked this question on 3 forums and a better solution than the one given by you was not offered to me. Thank you very much for your help.

 

[Fluff]

Welcome to the MrExcel Message Board!

For future reference.
Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.