Forum Discussion

Jpey65's avatar
Jpey65
Copper Contributor
Mar 20, 2024
Solved

Maybe an array formula to transpose records?

I have a table of data. Note that the number of rows per index can vary and the total number of unique index values will vary and not be named TestX: Index Concat Test1 DataValue1 Test1 ...
excel
Formulas and Functions
office 365
  • Jpey65's avatar
    Jpey65
    Mar 26, 2024
    https://www.ablebits.com/office-addins-blog/textjoin-function-excel-merge-text-multiple-cells/#:~:text=The%20syntax%20of%20the%20TEXTJOIN,text%20value%20that%20you%20combine.

    =TEXTJOIN(", ", TRUE, IF([Index]=[@Index], [Concat], ""))


Jpey65's avatar
Jpey65
Copper Contributor
Mar 25, 2024

OliverScheurich it does not work. It returns 0. As mentioned above the data is in a table, so I modified the formula: 

=LET(ind,A2:A34,conc,B2:B34,HSTACK(UNIQUE(ind),IFNA(DROP(REDUCE("",UNIQUE(ind),LAMBDA(u,v,VSTACK(u,TOROW(FILTER(conc,ind=v))))),1),"")))

 

OliverScheurich's avatar
OliverScheurich
Gold Contributor
Mar 25, 2024

Jpey65 

=LET(ind;Tabelle4[Index];
conc;Tabelle4[Concat];
HSTACK(UNIQUE(ind);IFNA(DROP(REDUCE("";UNIQUE(ind);LAMBDA(u;v;VSTACK(u;TOROW(FILTER(conc;ind=v)))));1);"")))

 

If you have a dynamic table you can apply this formula. In my example the name of the table is Tabelle4. In your worksheet you have to replace Tabelle4 with the name of your table.

  • Jpey65's avatar
    Jpey65
    Copper Contributor
    Mar 25, 2024
    =LET(ind;Table1[Index];conc;Table1[Concat];HSTACK(UNIQUE(ind);IFNA(DROP(REDUCE("";UNIQUE(ind);LAMBDA(u;v;VSTACK(u;TOROW(FILTER(conc;ind=v)))));1);"")))

    Returns an error:
    The first argument of LET must be a valid name.
    • OliverScheurich's avatar
      OliverScheurich
      Gold Contributor
      Mar 25, 2024

      Jpey65 

      =LET(ind,Table1[Index],
      conc,Table1[Concat],
      HSTACK(UNIQUE(ind),IFNA(DROP(REDUCE("",UNIQUE(ind),LAMBDA(u,v,VSTACK(u,TOROW(FILTER(conc,ind=v))))),1),"")))

       

      Does this work? I forgot to replace the semi-colons with commas in my last reply.

       

      Otherwise you can try the below formula which uses "abcdefg" instead of "ind". However i don't think that "ind" is an invalid name in the english version of Excel.

       

      =LET(abcdefg,Table1[Index],
      conc,Table1[Concat],
      HSTACK(UNIQUE(abcdefg),IFNA(DROP(REDUCE("",UNIQUE(abcdefg),LAMBDA(u,v,VSTACK(u,TOROW(FILTER(conc,abcdefg=v))))),1),"")))

       

       

      If it still doesn't work in your sheet can you attach a screenshot without sensitive data which shows all the relevant data just like in my screenshots?

Resources