Transpose Excel and create new column based on the transposed header values

Question

I have an excel file:

And I am trying to transpose the file and create a new column with values based on the header values. So, I want one column (number) to have all the numbers and the values m0,m1,m2,m3 to be added to a new column with the header Month:

It seems quite difficult to find anything online. Do I need a macros for that? Ideally, I want to do that in excel using the classic functions.

Answer 1

You can always map a 2-D table into a single row or a single column with a formula.

Say your data is in cols A through D, In E1 enter:

=INDEX($A$1:$D$1,ROUNDUP(ROW()/(COUNTA(A:A)-1),0))

and copy downwards. In F1 enter:

=INDEX($A$2:$D$9999,MOD(ROW()-1,COUNTA(A:A)-1)+1,ROUNDUP(ROW()/(COUNTA(A:A)-1),0))

and copy downwards:

Note that in this mapping, we traverse the table downwards and then across. It is equally easy to traverse the table across and then downward.