Unpivot Macro

I looked around for something to help me unpivot data and couldn't find anything to meet what I needed. So I created this macro and I am sharing it so others can use it and to see if there are any improvements I could make.

This unpivot is specifically made for data that has several columns of information before the date columns start. So I may have a column for name, a column for department, a column for accounting cost center, and then I would start having columns for each month with data filled in for sales dollars for each of those months. While this is a great way to visually present data, it is horrible to try to analyze it in this format.

Some codes I have seen unpivot by looking at each cell and deciding what to do with it. In my case I have to unpivot data that has several thousand rows with several years of date columns off to the right. Looking cell by cell simply doesn't work with that much data to transform. This macro uses arrays to transform the data in larger chunks, looking at whole rows instead of looking cell by cell. I tested it with 2,000 rows of data and 24 columns of dates off to the right and it worked in a flash.

That said I am sure there can be improvements made to the code. If you have ideas to improve it, make a comment, if you need something like this, use it.

Please Login or Register  to view this content.

Hi Nigel,

By your thread title I thought you were going to show how to Drill Down and get the data that made up a cell in your pivot table, like shown in:
https://exceljet.net/lessons/how-to-...-a-pivot-table

Then I discovered a NEW Pivot Table feature for Excel 2013 called Quick Explore that allows Drilling Down and Drilling Up at:
https://support.office.com/en-us/art...rs=en-US&ad=US

I don't understand your code above well enough without having a sample workbook to run it on. Could you please attach a sample with the code included so I can see what it does?

Here is an example sheet. I know there are several understandings of the term unpivot. In my case I have a data dump from a system that gives me data in what looks like a pivot table format. There are several starting columns and then each date has its own column. While this looks nice and looks like a pivot table, it isn't one. It can't be analyzed very easily the way it is. What I needed was a way to get the data back to the format where I could analyze it, a database type format. I need a new row for each date and value. Once it is back in that format I can put a real pivot table on it and make it look that same way with a new column for each date, but it has much more analytical power because the underlying data is in a normal database type format. I can filter and compare by cost center or department or group by year.

Hopefully that explains it a bit better along with the attached sheet.

Hi,

A few years ago I wrote a macro I called MakeCrosstabATable which I think does the same as your Unpivot macro. I call the starting type of structure a Crosstab Table and you call it a Pivot table structure. Mine looks like this.

Please Login or Register  to view this content.

I've included it in the following attachement and have used it to show the differences between your method and mine.

I like your approach. I made chunks for each row, but I hadn't thought of repeating all of the rows for each date. It works well.

An alternative, but some of the columns are nil, but you have filled them with numbers.