Transpose rows and join with row header using formula

Copper Contributor


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

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?

@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: 

best response confirmed by MHulten (Copper Contributor)


That could be

  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<>"") )
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.
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 ( 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.