Complicated Transpose Macro

**ebdmbfan** · 04-19-2013, 03:05 PM

Hello Geniuses,

I have a set of data that is set up like a matrix, I want that data to be converted with a macro so I can make a proper pivot table.

Please see attachment, this is just a sample, the real file has 300 dealers and 50 item numbers roughly, so I need to be able to manipulate the macro.

Any help would be greatly appreciated!

**Pepe Le Mokko** · 04-19-2013, 03:16 PM

Here is a description of how to do it ( don't know if it works for MAc)

As for translating it to VBA, maybe recording a macro will do?

1) Start the wizard -- Data/PivotTable & PivotChart Report...
If you have Excel Version 2007+ use alt+D P to activate the PT Wisard

2) Step 1 of 3 - Select Option Button: Multiple Consolidation Ranges -- [Next]

3) Step 2a of 3 - Select Option Button: I will create the page fields -- [Next]

4) Step 2b of 3 - With your cursor in the Range Textbox, select the data range on your sheet that you want to normalize -- [Add] -- [Next]

5) Step 3 of 3 - [Finish]

6) Drag the Row and Column buttons OFF the Layout -- [OK] -- [Finish]

7) Mysteriously, you are on another sheet that has a 4-cell pivot table. Double click the BOTTOM RIGHT CELL

8) Again, on another sheet -- This is you data normalized, or at least closer to it. You'll need to change headings at least.