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...


...then do the following:

1. Insert new columns B:C

2. Select cells B1:B7 and enter the array formula,

3. Select cells C1:C7 and enter the array formula,

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.