I need a VBA script to transform a large Excel spreadsheet from a horizontal, easy-to-read format to a vertical, easy-to-pivot format. I found an example on Bill Jelen's MrExcel site (Google "Convert Horizontal Data to Vertical Data Using a Macro for Optimal Excel Pivot Table Analysis"), but it's for a much simpler dataset and I couldn't get it to scale.
The data export I'm working from is a listing of task labor estimates that can cover anywhere from a couple of months to 3 years or more. Each task can have one or more subtasks, each with it's own labor estimate. As can be seen in the sample dataset, the period of performance for a particular task may be a contiguous series of months or could be spread over many months with some months of inactivity located throughout. Note that my actual dataset has 18 columns before the Total Hours column instead of 5 and about 1000 rows, but I trimmed it to keep the sample cleaner.
During peak operations, my team has to pull an export from the Oracle-based repository on a daily basis and report updated estimates ordered by Department. Ideally we can use a pivot table to produce reports and charts for each department and summary data for the entire organization. Unfortunately, the Oracle system outputs the export file as an Excel file that's formatted for reading, not optimized for pivoting. I need a clean columnar list of data for pivoting (which I would also use as input to an Access database for custom reports).
The SampleData1.xls file attached here shows the original ExportData on the first tab, the desired format on the Transformed tab, and a pivot table on the Pivot tab. Given the popularity of pivot tables and the way most Excel worksheets are formatted, I'm sure this problem has been solved before, but I couldn't find a solution in the archives here that wasn't Access specific. I sometimes receive Excel files from customers or vendors that are also set up for reading, so what I would like to build is a macro that allows my team to easily change a few key parameters and use it on the other datasets as well.
Any pointers would be greatly appreciated, or if you know of an existing solution you can point me to, that would be great as well. I'm working on it now, but my coding skills don't tend to produce real elegant solutions.