Please help! RE: Pivot Tables
Posted by John Beray on March 20, 2001 3:35 PM
What I am trying to accomplish I actually need to do via Access, but I think I need to use a Pivot Table to do this & this being an Excel object that can be implemeted via a PivotTable form in Access I am looking for help here....
I have 2 tables
Data in Table1 I need to transfer to Table2.
Table1 looks like this
Counter Jan Feb March April May June
1 123 456 789 12 45 67
Table2 results need to look like this
Counter Month Number
1 Jan 123
2 Feb 456
3 March 789
4 April 12
5 May 45
6 June 67
I could use a pure Excel solution as well...
Thanks in advance for your help.
John B.
Posted by Mark W. on March 20, 2001 3:51 PM
John, if cells A1:G2 contains...
{"Counter","Jan","Feb","Mar","Apr","May","Jun"
;1,123,456,789,12,45,67}
...then do the following:
1. Insert new columns B:C
2. Select cells B1:B7 and enter the array formula,
{=TRANSPOSE(D1:I1)}.
3. Select cells C1:C7 and enter the array formula,
{TRANSPOSE(D2:I2)}.
4. Type a 2 in cell A3, select cells A2:A3, and
double-click the fill handle (the little black
square in lower right-hand corner of selection).
5. Select column B:C, and perform Copy/Paste
Special Values.
6. Cleanup by adding your column headers and
deleting old columns D:I.
