Transpose Large Data Set with Headers in Excel VBA

Question

The image above shows the current structure of the time series data that I am working with. It has many columns of time series data which are identified by the customer id in the header row. In order to use this data in a pivot table for analysis, I'd like to convert it to a format like the image below:

Here the customer id becomes a dimension that describes the time series data.

Since this is a large data set, it would be a huge time sink to manually transform the data into the desired format. Also, I do not have fancy add-ins like Power Pivot or Power Query...

Using Excel VBA, how can I write a macro to handle this task?

Thanks,

this tutorial worked for me. thanks for sharing. I knew there was a way without writing VBA code. — AME
– AME, Commented Jul 25, 2015 at 5:12

Matt · Accepted Answer · 2015-07-29 20:18:44Z

Solution to problem can be found here: http://www.excel.solutions/2014/03/unpivot-excel-data

Start off by pressing keys ALT > D > P to open the Pivot Table Wizard dialog box:

Choose the ‘Multiple consolidation ranges’ option, then click ‘Next’
- In step 2a of the wizard, choose the ‘I will create the page fields’ option, and click ‘Next’
- Now we need to add our crosstab data range as a data source for this pivot table. Enter / select the appropriate range, then click ‘Add’. Then click ‘Next’.
Choose a location for the intermediate pivot table (it’s a good idea to use a new worksheet, as we can simply delete the entire worksheet when we’re finished). Then click ‘Finish’.
We now have an ‘intermediate’ pivot table, which looks very similar to our raw data, but has some grand totals. Now we want to drill into the source data for this pivot table, by double clicking on the overall Grand Total value – the cell intersection of the Grand Total column, and Grand Total row:
By double clicking to drill into the grand total data source, another worksheet is created, containing a table with our unpivotted data:

Collectives™ on Stack Overflow

Transpose Large Data Set with Headers in Excel VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related