Forum Discussion
Solved
Transpose rows and join with row header using formula
Hi! I need to use a formula (not macro) to transpose a table and return another table based on the row header and each individual column value. Optionally a textjoin of the combination cold work. ...
That could be
=LET( rws, ROWS(Table1), cls, COLUMNS(Table1), k, SEQUENCE(rws*(cls-1)), i, MOD( k-1, cls-1)+2, j, INT( (k-1)/(cls-1)) +1, postfix, INDEX( Table1, j, i ), all, IF( ISBLANK(postfix), "", INDEX( Table1, j,1 ) & "-" & postfix), FILTER(all, all<>"") )
That could be
=LET(
rws, ROWS(Table1),
cls, COLUMNS(Table1),
k, SEQUENCE(rws*(cls-1)),
i, MOD( k-1, cls-1)+2,
j, INT( (k-1)/(cls-1)) +1,
postfix, INDEX( Table1, j, i ),
all, IF( ISBLANK(postfix), "", INDEX( Table1, j,1 ) & "-" & postfix),
FILTER(all, all<>"") )
Works like a charm! Thank you!
Now... could you please offer an explanation... 🙂
Now... could you please offer an explanation... 🙂
Not sure which kind of explanations are required, it depends on how familiar you are with dynamic array functions.
First one is LET function (microsoft.com) which combines our calculations in one block.
SEQUENCE() generates list of indexes for data part of the table, i and j extracts from it column and row number related to each index.
With INDEX() we generate for every index some value depends on related column/row number, finally FILTER() all values with empty texts.