Forum Discussion

paolopadovan's avatar
paolopadovan
Copper Contributor
May 14, 2025

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

  • 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.

  • 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.

    • paolopadovan's avatar
      paolopadovan
      Copper 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

Resources

OSZAR »