Reverse Pivot Table

Posted by Ack on April 24, 2001 6:46 AM

Name Proj w/e hours
Paul 123 7/1 40
Jake 456 7/8 35

I know how to take this structure and create pivot table that shows

Name Proj 7/1 7/8
Paul 123 40
Jake 456 35

But I have the second table and want to "reverse pivot" to get the first table structure. Seems like it should be possible. I need in the first structure so I can load into Access and manipulate and compare actuals to forecast. Any ideas???

Posted by Mark W. on April 24, 2001 8:56 AM

Ack, your 2nd data set has a repeating group (hours
indexed by a 'w/e' date). First you should distribute
n-1 rows between your existing data rows where n is
the number of elements (2) in your repeating group.
An easy way to accomplish this is to number your
data records in an unused or inserted column (e.g.,
1 for Paul, 2 for Jake), and then paste these
values directly beneath the newly numbered values.
Next, sort all records by this newly entered value.
And, finally use the TRANSPOSE() function to
populate columns for 'w/e' and 'hours'.

Let's say that your data are in cells A1:D3. Enter
{1;2} in cells E2:E3, and copy these values to cells
E4:E5. Select rows 2:5 and sort on column E. Next,
enter the array formula, {=TRANSPOSE($C$1:$D$1)}, into
cells F2:F3 and copy down to cells F4:F5. Next, enter
the array formula, {TRANSPOSE($C2:$D2)}, into cells
G2:G3 and copy down to cells G4:G5. Finally, select
cells A2:B4 and choose the Edit Go To... Special...
Blanks menu command. Type =A2 and press Control+Enter.

Now, it just a matter of cleaning up. Select all of the
data and perform a Copy/Paste Special Values. Delete
columns C:E, and a column labels for column F:G.