Reverse Pivot

[jomili]

This one may be easier to understand if you open the example first. There are 2 tabs: "The Way it Is", and the "The Way it Should Be".
The table in "the way it is" is structured like a pivot table. It represents a certain class of employees, by region, by function, and within a specific PAC (Expense group). This table is one of about 15 on a page. I would like to be able to organize the data so I can run Pivot Tables on the information, but to do that I need to change the structure of the tables to something similar to what's on the "Way it Should Be" tab.

I can do it with a lot of manual effort, but is there an easier way to do it via VBA, for this and another simiarly structured tables I come across?

Any help is greatly appreciated.

[davegugg]

Nice, it seems you are changing it from "spreadsheet format" to "database format". I'm actually working on something similar right now so I can get a bunch of data from Excel into Access easiest. Anyway, this should do. The only thing it doesn't copy over is the format. If that is a problem let me know and I can fix it, but it would almost be easier just to manually do it anyway.

Please Login or Register  to view this content.

[jomili]

Dave,

You really rock! I tested your macro on my Example, and it worked perfectly.

I went to my source, which has about 15 similar tables all lined up. I had to delete the redundant column headings, blank lines etc., and change sheet names but after that it worked. However, it doesn't seem to have caputured all of the information. Since my source, after the alterations mentioned above, has 146 lines, should I increase the values for i and j? Or, how do I increase the capability to handle more lines?

[davegugg]

That's right, make i the number of rows you are working on and j the number of columns.
For example, if you had data in rows 24 - 108 you would put:

Please Login or Register  to view this content.

[jomili]

Nice! That did the trick, exactly what I needed. Thanks so much for your help!