Forum Discussion

NotSoFastEddie's avatar
NotSoFastEddie
Copper Contributor
Apr 18, 2024
Solved

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...
excel
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    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.

NotSoFastEddie's avatar
NotSoFastEddie
Copper Contributor
Apr 19, 2024

SergeiBaklan thanks very much.  I am always amazed at how rich Excel functions are.  I will give these a review.  Thanks for the time you took to help.

  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Apr 19, 2024

    NotSoFastEddie , you are welcome.

    That was attempt to play with different techniques, skipping for the moment VBA, Office Script and Python in Excel.

    No doubt in each technique (formulae, Power Query, PivotTable) we may generate few different solutions.

    What to use depends on concrete scenario.

Resources