Transpose rows and join with row header using formula

[MHulten]

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

[#2]

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

[#3]

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?

[#4]

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

https://exceloffthegrid.com/power-query-introduction/ 

[#5 · Accepted solution]

@MHulten 

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

[#6]

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.

[#7]

Works like a charm! Thank you!
Now... could you please offer an explanation... :)

[#8]

@MHulten 

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.