MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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.