Forum Discussion

NotSoFastEddie's avatar
NotSoFastEddie
Brass Contributor
May 01, 2024

Take 2 on concatenating a group of related cells across several rows into one cell

 OliverScheurich was great to help me on the original request.  The request has greatly simplified and I have started to get results but sadly stuck on trying to de-compose the original solution provided.  I think I know what I need to do, but I think the syntax is what I am struggling with or .... maybe wrong formulas.

 

 

excel
Formulas and Functions

4 Replies

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    May 01, 2024

    NotSoFastEddie 

    =LET(rng,A2:E14,PCODE_BUNDLEID,UNIQUE(FILTER(CHOOSECOLS(rng,1,2),CHOOSECOLS(rng,2)<>"")),IFNA(REDUCE({"PCODE"."BUNDLEID"."COMPONENTS + QUANTITIES"."Other Stuff"},SEQUENCE(ROWS(PCODE_BUNDLEID)),LAMBDA(u,v,VSTACK(u,
    HSTACK(INDEX(PCODE_BUNDLEID,v,1),"","",FILTER(CHOOSECOLS(rng,5),(CHOOSECOLS(rng,1)=INDEX(PCODE_BUNDLEID,v,1))*(ISBLANK(CHOOSECOLS(rng,2))))),
    HSTACK(INDEX(PCODE_BUNDLEID,v,{1.2}), TEXTJOIN({"-".","},,FILTER(CHOOSECOLS(rng,3,4),(CHOOSECOLS(rng,1)&CHOOSECOLS(rng,2)=INDEX(PCODE_BUNDLEID,v,1)&INDEX(PCODE_BUNDLEID,v,2)))),
    FILTER(CHOOSECOLS(rng,5),(CHOOSECOLS(rng,1)=INDEX(PCODE_BUNDLEID,v,1))*(CHOOSECOLS(rng,2)=INDEX(PCODE_BUNDLEID,v,2))*(ISBLANK(CHOOSECOLS(rng,3)))))))),""))

     

    For the original requirement i've improved the formula with the help of CHOOSECOLS. Now only the range at the beginning of the formula has to be changed (for example from A2:E14 to A2:E28) and then the formula spills the result.

     

     

    =LET(rng,A3:F15,z,REDUCE({"PCODE"."BUNDLEID"."COMPONENT SKU"."COMPONENT QUANTITY"."COMPONENT Grouping"."Other Stuff"},SEQUENCE(ROWS(rng)),LAMBDA(a,b,VSTACK(a,IF((INDEX(rng,b,2)<>"")*(INDEX(rng,b,3)=""),HSTACK(INDEX(rng,b,{1.2.3.4}),TEXTJOIN({"-".","},,FILTER(CHOOSECOLS(rng,3,4),CHOOSECOLS(rng,1)&CHOOSECOLS(rng,2)=INDEX(rng,b,1)&INDEX(rng,b,2))),INDEX(rng,b,6)),HSTACK(INDEX(rng,b,)))))),IF(z=0,"",z))

     

    This is my suggestion for the new requirement. It returns the intended result in the attached file if i correctly understand what you want to do.

    • NotSoFastEddie's avatar
      NotSoFastEddie
      Brass Contributor
      May 02, 2024

      OliverScheurich 

       

      Thanks again Oliver for your work and quick response.  I don't believe I was clear enough in the previous example spreadsheet.  I need to take the component SKU and quantity from each of the component rows associated to the BUNDLEID and concatenate them in the yellow colored cell in the BUNDLEID row.  No need anymore to generate a separate table.

       

       

      • OliverScheurich's avatar
        OliverScheurich
        Gold Contributor
        May 02, 2024

        NotSoFastEddie 

        =LET(rng,A3:D15,

        BYROW(SEQUENCE(ROWS(rng)),LAMBDA(b,IF((INDEX(rng,b,3)="")*(INDEX(rng,b,2)<>""),TEXTJOIN({"-".","},,FILTER(CHOOSECOLS(rng,3,4),CHOOSECOLS(rng,1)&CHOOSECOLS(rng,2)=INDEX(rng,b,1)&INDEX(rng,b,2))),""))))

         

        You are welcome. This formula should return the intended output. Unfortunately i didn't understand the requirement when i posted my last reply.

Resources