0

I have an excel file:

enter image description here

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:

enter image description here

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.

2
  • 1
    Search for 'UnPivot Data', transposing is different to what you are after
    – PeterH
    Jun 6, 2018 at 10:47
  • And you will be able UNPivot the data, and also sort and rename the columns, using Power Query, a free Microsoft provided add-in for Excel 2010. The query settings will be recorded and can be used repetitively. Jun 6, 2018 at 12:25

1 Answer 1

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:

enter image description here

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .