"reverse pivot table function"

[z]

I would like take a set of horizontal data (like data
presented in a pivot table) and make it vertical.

For example, I want to take the following horizontal
data....
------------------------
| |1/02 |2/02 |
------------------------
|ABC | 100| 90|
------------------------
|DEF | 500| 600|
------------------------

and make it look like...
------------------------
|ABC |1/02 | 100|
------------------------
|ABC |2/02 | 90|
------------------------
|DEF |1/02 | 500|
------------------------
|DEF |2/02 | 600|
------------------------

Is there a function out there to do this...or, does
anybody have a macro that does this.

Thanks!

[John Walkenbach]

You can actually accomplish that by creating a pivot table.

1. Add a dummy column label to the upper left cell.
2. Select Data * PivotTable Report
3. Select "Multiple consolidation range" option
4. Specify "I will create page fields"
5. Add the data range (A1:C3 in your example), click Next
6. Specify 0 page fields
7. Finish, to create the pivot table
8. Drag the "Column" field to the Row axis
9. Remove the totals
10. Copy the pivot table and paste special as Values
11. Fill in the gaps in the 1st column.

John Walkenbach
For Excel tips, macros, & downloads...
http://j-walk.com/ss

"z" <diggi...@diggitydown.com> wrote in message
news:113bf01c21929$eea06dc0$3bef2ecf@TKMSFTNGXA10...

[adetaylor]

One way:
Assume A1 is top left of your table.
1) Insert a column between "ABC" and "100".
2) To right of "ABC" (in B2, to get date): =$C$1
and copy that down the list.
3) Insert two columns between "100" and "90"
4) To right of "100" (in D2, to get ID): =A2
5) To right of that (in E2, to get date): =$F$1
6) Copy those equations down the list.
7) Use Copy & PasteSpecial>Values to convert equations
to text, pasting the set of columns associated
with new dates to bottom of list.

Repeat this for as many month_columns as you
have and delete the unneeded extra columns.
Sort the records in the first columns.

--Brian Taylor
Refinate (C)2001, 20 free trials.
Click to: Rearrange Cells or Cell Content,
Find Duplicates, Count Items, Import, much more.
www.adetaylor.com

"z" <diggi...@diggitydown.com> wrote in message
news:113bf01c21929$eea06dc0$3bef2ecf@TKMSFTNGXA10...