Forum Discussion

Deleted's avatar
Deleted
Apr 12, 2018
Solved

Transposing problem

I have a dataset of more than 2000 mutual funds, for these funds I have data over time for different characteristics like return and certain industry weights in their portfolio.   The problem here ...
Formulas & Functions
Requests
  • SergeiBaklan's avatar
    SergeiBaklan
    Apr 12, 2018

    Hi Jur,

     

    That could be done with Power Query (aka Get&Transform). Convert source to the Excel table, with one query form left side with chronological data, another query for static one, append second to first and generate final table.

     

    Please see attached. That's done without cosmetic, consider as prototype.

Tomasz Kocur's avatar
Tomasz Kocur
Brass Contributor
Apr 12, 2018

Hi Jur

Try this :

 

1 on your worksheet click in order (not all together) Alt D P,  it will open the PivotTable Wizard

2 on the window choose Multiple consolidations ranges 

3 on the window choose I will Create the page fields 

4 choose the range you are interested (Sheet1!$A$1:$BTA$2) click Add then Next

5 choose New worksheet

6 on the new worksheet with the pivot table level only Count of Value (see the file)

 

Count of Value

727

 

7 double click on the number it will create a new worksheet with data transposed to kind of database table records 

 

What do you think about that? 

 

it's working great when you have a few worksheets transpose to database 

 

 

Resources