General discussion

Locked

MS Excel Pivot Table "Reverse"

By renetjie ·
I have information in an Excel spreadsheet in the format of a simple pivot table (it is not actually a pivot table, but the info looks similar to the output you'd get from a pivot table). I need the information in a "list" to be able to import it into a database (similar to what you'd have when starting out & wanting to manipulate the information using a pivot table). Example: Monthly Forecast, with Part Number on the left, and the months across the page, with forecast quantities for every month. Required result: Multiple rows with : Part Number, Month, Quantity.

This conversation is currently closed to new comments.

1 total post (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

MS Excel Pivot Table "Reverse"

by carollong In reply to MS Excel Pivot Table "Rev ...

I saw this done by a VB programmer some years ago - I don't have sample code but I can suggest a method [with examples]. I hope this helps.

Select the data area of the table as a range [B2:J5]
use your programming skills to derive the right mostcolumn [r1=J] and down most row [r2=5]
use your programming skills to derive the left most column [r3=B] and up most row [r4=2]

Select the column where the product labels [l1=A]
select the row with the date labels [l2=1]

select the point at which the list starts
use your programming skills to manage this variable as list start row [s1=100] and list start column [s2=A]

have a variable for current list cell [x=A100]
use your programming skills to manage this variable as current list row [x1=100] and current list column [x2=A]

have a variable for current data cell [c=B2] (derive this from the range selected0
use your programming skills to manage thisvariable as current data row [c1=2] and current data column [c2=B]
have a variable for current product label derived from l2 and c2 [d1=B1]
have a variable for current date label derived from l1 and c1 [d2=A2]

goto current list cell [x=A100]

Then repeat this:
copy contents of d1 to this cell
increase column count on list [x1=B]
goto current list cell [x=B100]

copy contents of d2 to this cell
increase column count on list [x1=c]
goto current list cell [x=C100]

copy contents of c to this cell
increase x2 [x=101] and set x1 to s2 [x1=A]
goto current list cell [x=A101]

increase c1 unless it is bigger than r1 then (set c1 to r3 and increase c2)
If c2 is bigger than r2 then (goto cell s and stop) else (recalculate values for d1 d2 and repeat)

Back to Web Development Forum
1 total post (Page 1 of 1)  

Related Discussions

Related Forums