Pivot Tables in Excel 2007

[warren66]

In Excel 2003 and prior versions, if I wanted to convert a table (with headers along the top of the columns and down the left-hand side) to a list suitable for importing into another application, I could use the PT wizard, and by choosing the "Multiple consolidation ranges" option, then "I will create the page fields" and then by amending the layout, remove the "Row" & "Column" buttons and Finish I would be left with a single value PT. Double-clicking that cell then displays the data in a list headed by the labels Row, Column & Value.

In Excel 2007 ,the PT looks very pretty but the wizard has gone so I can't choose the alternative option of using "Multiple consoldation ranges", because most PTs are used to convert lists into tables.

Any ideas how I can achieve what I want in Excel 2007?

Thanks


Warren

 

[SkipVought]

Hi,

You could use MS Query, using mutiple UNIONs.

Using MS Query to get data from Excel faq68-5829

Select RowFld1, RowFld2, ColFld1, 'Name Of Column'
From TheSheet$
UNION
Select RowFld1, RowFld2, ColFld2, 'Name Of Other Column'
From TheSheet$
.....

where your source data is...
[tt]
RowFld1 RowFld2 ColFld1 ColFld2.....

[/tt]



Skip,
[sub]
[red][/red]
[/sub]