independent columns in pivot table

[slaytanic]

Hi, I'm wondering if I can have what I need in a single pivot table?
Let's say this is the input table - players scoring against different teams from various conferences:

[TABLE="width: 500"]
<tbody>[TR]
[TD]name[/TD]
[TD]vs team[/TD]
[TD]vs conf[/TD]
[TD]pts[/TD]
[/TR]
[TR]
[TD]player1[/TD]
[TD]team1[/TD]
[TD]east[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]player1[/TD]
[TD]team1[/TD]
[TD]west[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]player1[/TD]
[TD]team2[/TD]
[TD]east[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]player1[/TD]
[TD]team2[/TD]
[TD]west[/TD]
[TD]25[/TD]
[/TR]
</tbody>[/TABLE]

etc

The real table has much more data than this. I would like to have a single pivot table which averages points in columns per different criteria like so:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Avg points total[/TD]
[TD]Avg vs team1[/TD]
[TD]Avg vs Team2[/TD]
[TD]Avg vs East[/TD]
[TD]Avg vs West[/TD]
[/TR]
[TR]
[TD]player1[/TD]
[TD]20[/TD]
[TD]25[/TD]
[TD]15[/TD]
[TD]7.5[/TD]
[TD]22.5[/TD]
[/TR]
</tbody>[/TABLE]




If I use columns, then the second column becomes a subgroup of the first one and I don't want that. Can you point me to the possible answer. Right now I have multiple pivot tables and then paste results from 1 column next to the other pivot table but that is quite awkward. Thanks

 

[sheetspread]

I'm not sure you can put this all in one and selectively remove the subgroups, but 3-4 pivot tables next to each other works fine, why do you say it's awkward?

 

[Fazza]

A query table approach can return the desired cross tab. With SQL like

TRANSFORM AVG(pts)SELECT name
FROM (
SELECT name, [vs team], pts
FROM [YourSheetName$]
UNION ALL
SELECT name, [vs conf], pts
FROM [YourSheetName$])
GROUP BY name
PIVOT [vs team]

Assuming YourSheetName is the name of your worksheet. Use your sheet name instead or otherwise a basic (non-dynamic) defined range. So the SQL would be like such as "FROM definedname" where it currently says "FROM [YourSheetName$]"

So save the file, ALT-D-D-N and follow the wizard. Excel files, etc. If you get a message about no visible tables just acknowledge that and then choose system tables + OK to see worksheet names.
At the end of the wizard choose the option to edit in MS Query and then via the SQL icon edit the SQL to be like above. OK to enter, acknowledge any message about not being able to graphically represent etc. Via the 'open door' icon exit MS Query and create the query table. It is refreshable like a pivot table.

If you prefer a pivot table, start instead from a new workbook & use the pivot table wizard: ALT-D-P. Choose external data source at the first step and it is very similar. Change the SQL so it isn't the cross tab but simply the re-arrangement,

SELECT name, [vs team] AS [Versus], pts
FROM [YourSheetName$]
UNION ALL
SELECT name, [vs conf], pts
FROM [YourSheetName$]

The resultant worksheet containing the pivot table can be moved into your source data workbook if you want.

Another thought, if you want to identify if the data came from the 'vs team' or 'vs conf' field then just add that as a text field within the SQL.

cheers