Forum Discussion

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
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], ""))

OliverScheurich

Gold Contributor

Mar 20, 2024

Jpey65

=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),"")))

Does this return the intended result?

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