Forum Discussion

NotSoFastEddie's avatar
NotSoFastEddie
Brass Contributor
Apr 18, 2024

Concatenate matching list of columns into another cell

I am trying to work through this.  I have a person enters in data row by row.  In the parent row, I need to gather up one of the column entries in the child rows and concatenate them together.  I thi...
  • OliverScheurich's avatar
    Apr 18, 2024

    NotSoFastEddie 

    =LET(

    PCODE_BUNDLEID,

    UNIQUE(FILTER(A2:B14,B2:B14<>"")),

    IFNA(

    REDUCE({"PCODE"."BUNDLEID"."COMPONENTS + QUANTITIES"."Other Stuff"},SEQUENCE(ROWS(PCODE_BUNDLEID)),

    LAMBDA(u,v,

    VSTACK(u,
    HSTACK(

    INDEX(PCODE_BUNDLEID,v,1),

    "",

    "",

    FILTER(E2:E14,(A2:A14=INDEX(PCODE_BUNDLEID,v,1))*(ISBLANK(B2:B14)) ) ),
    HSTACK(

    INDEX(PCODE_BUNDLEID,v,{1.2}),

    TEXTJOIN({"-".","},,FILTER(C2:D14,(A2:A14&B2:B14=INDEX(PCODE_BUNDLEID,v,1)&INDEX(PCODE_BUNDLEID,v,2)))),
    FILTER(E2:E14,(A2:A14=INDEX(PCODE_BUNDLEID,v,1))*(B2:B14=INDEX(PCODE_BUNDLEID,v,2))*(ISBLANK(C2:C14))))))),

    ""))

     

    If you work with Office 365 or Excel for the web you can apply this formula which spills the result.

Resources