Forum Discussion
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.
Note that the source data may be defined as a table, range, or stay as "normal" data in the sheet based on what would be convenient.
I have appended an example sheet with the source data in A1:D4 and example output in F1:G8 with the textjoin option in I2:I8.
The source data may grow downwards but the number of columns is static.
I'm using Office 365 with an E5 licence.
BR /Mats
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<>"") )
7 Replies
MHulten I would like to suggest PowerQuery, i.e. not a formula. Unpivot the data on the left and merge the two relevant columns to one, with a hyphen as the delimiter.
Example attached.
- I haven’t used PowerQuery.
The intention is to distribute this as a template. I can’t have any inbuilt dependencies other than the version of Excel that is distributed with the E5 license.
Would this work with PowerQuery?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<>"") )
