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<>"") )
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<>"") )- Works like a charm! Thank you!
Now... could you please offer an explanation... 🙂
MHulten Well, as long as your users aren't using a Mac, this could work for all. From Excel 2016, PQ is included in Excel. In Excel 2013 it's an add-in. If you could accept teaching you users to press "Refresh" every now and then it should work. PQ is much more user friendly and flexible than complicated formulae.
A good introduction to PQ in the link below:
- Looks really interesting, but due to time constraints I need to stick to technologies I'm familiar with.
Checking out PQ will definitely be on my to-dos though.
