AnswerBun.com

In excel, how do I turn transpose the whole row after a certain row element while stacking them into one big column?

Super User Asked by CatVI on October 10, 2020

Here is an example of what I want to do.

Area    Y       M   D1  D2  D3  D3  D5  D6
Barisal 1953    11  0   0   4   16  0   2 
Barisal 1953    12  35  14  9   0   0   0 
Barisal 1954    1   0   1   16  10  0   0

I want to turn the table above into this :

Area    Y       M   D
Barisal 1953    11  0
Barisal 1953    11  0
Barisal 1953    11  4
Barisal 1953    11  16
Barisal 1953    11  0
Barisal 1953    11  2
Barisal 1953    12  35
Barisal 1953    12  14
Barisal 1953    12  9
Barisal 1953    12  0
Barisal 1953    12  0
Barisal 1953    12  0
Barisal 1954    1   0
Barisal 1954    1   1
Barisal 1954    1   16
Barisal 1954    1   10
Barisal 1954    1   0
Barisal 1954    1   0

So I want to expand the cell in month (area and year too) column by the number of days behind it. Then transpose and stack all the day cells into one column.

For the love of god I don’t know how to do this other than going through a thousand steps. The dataset I have is rainfall data just like this but has thousands of rowns and 30-31 day columns.

One Answer

This takes only a few clicks with Power Query / Get and Transform.

It will work best if the data has been turned into a Table object with Ctrl-T.

  • Select the data or a cell in the data range.
  • Click Data ribbon > Get and Transform group > From Table / Range
  • The data loads in the Power Query editor.
  • Select the first three columns
  • Click Transform > Unpivot dropdown > Unpivot other columns
  • Rename the columns to your preference, or don't.
  • Save the query and the data will load in the workbook.

If you add more data to the original table, you only need to refresh the query.

enter image description here

enter image description here

Correct answer by teylyn on October 10, 2020

Add your own answers!

Related Questions

Expanding inner Veracrypt volume (FAT)

1  Asked on December 27, 2021

   

Failed to create Menus

2  Asked on December 27, 2021 by user1071368

     

spaceport.sys is corrupt – windows 10

1  Asked on December 27, 2021 by developerharon

 

Duplicate UDP packets using iptable

0  Asked on December 27, 2021 by kapil-gupta

   

Ask a Question

Get help from others!

© 2022 AnswerBun.com. All rights reserved. Sites we Love: PCI Database, MenuIva, UKBizDB, Menu Kuliner, Sharing RPP