Forum Discussion
Transpose rows and join with row header using formula
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.
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... 🙂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.
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.