Forum Discussion
Transposing problem
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.
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
Thanks for your reply, I have a more precise description of the problem now and I am curious if this works for this particular problem as well:
Hello everybody,
I am having problems transposing data in Excel. I have a large dataset of around 2000 mutual funds, for these funds I have cross sectional data and time series data for four different variables: Returns, concentration, active share and fund size.
Per row there is one fund with all the accompanying data in the columns right from that cell for all the time series data and other variables.
The problem here is that I cannot find a solution for how to transpose the dataset to a panel format. In the attachment you find an example of the data for three mutual funds and what the desired sollution is to the problem.
That is the best way I can describe it to you I figured.
Thanks in advance,
All pointers/tips/tricks are very welcome!
Hoekstraat
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.
- The example you send with the reply is exactly what I mean, thank you so much.
Can you maybe be more specific in which steps you took to get to this final result?
If you have a step by step walk-through I can apply this to the full dataset.
Thanks in advanceNow land results into the excel sheet. Open right pane and right click on first query name, select Load to, here is Table and location where you'd like to keep it
Right click on any cell within the table and select External table properties
within it deselect Adjust column width.
Now you may add bit cosmetic to your table - change design and format of columns / headers.
With changing of source data click Refresh in right click menu of the table or in Data section of ribbon.
File with above steps is attached.
As a comment, resulting data is bit incorrect. You have
- !DIV/0 errors within the source data,
- repeating column names (e.g. Return 5-2012 and some else repeated two times). It shall be corrected in source (all names are to be unique) or processed additionally within the query.
- empty columns in the middle;
- perhaps something else
But that better test on copy of real data.