Forum Discussion
textjoin
Hi. first of all sorry for my english
I have a sheet with 365,000 rows. I need to concatenate the values in column D where the cells in columns A, B and C have the same values.
7 Replies
- paolopadovanCopper Contributor
Thanks to all... now i try
Perhaps with 365000 rows Power Query, as OliverScheurich suggested, will be better. However, formula
=GROUPBY (HSTACK(Tabelle1[Col1],Tabelle1[Col2],Tabelle1[Col3]),Tabelle1[Col4], ARRAYTOTEXT,0,0)
also works.
As for Power Query I'd add first colmns
let Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content], names = Table.ColumnNames( Source ), DeclareType = Table.TransformColumnTypes( Source, List.Transform( names, each {_ ,type text} ) ), CombineTesxt = Table.Group(DeclareType, List.RemoveLastN(names, 1), {{"Text", each Text.Combine( [Col4], ", "), type text}}) in CombineTesxt
Both variants are in attached file.
- paolopadovanCopper Contributor
Thank you
- Patrick2788Silver Contributor
Interestingly, 64-bit Excel can handle the GROUPBY solution without issue for 360,000 rows at about .5 seconds. It's a bit faster without HSTACK (presuming columns are adjacent).
Really intresting, thank you for sharing
- OliverScheurichGold Contributor
Does the screenshot show the intended result? This is done with Power Query and in the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.
I've only made a small sample table however Power Query is the perfect tool for huge databases. It can actually handle transformation of several million rows.
- paolopadovanCopper Contributor
Thank you very much, this is what I need. but I don't know how to apply Power Qury to my table :-) . Now I try