Pivot table setup.

[jontyoc]

Hi All

Only used pivot tables for very basic things and never had an issue, and its been a while since I created one myself (about 2005!). I am using Excel 2010.
I've tried google help and swapped the fields all over the four possible options but it wont do what I want it to do.
So I am coming to you wonderful people who have never let me down before.

The spreadsheet I am using the data for contains about 50 different accounts with various information in regarding these accounts.
When I build the pivot table I can only seem to get a sum of data and not the information in the various columns.


Original Data:


[TABLE="width: 1256"]
<tbody>[TR]
[TD]Account Info
[/TD]
[TD]Profile's
[/TD]
[TD]PTA
[/TD]
[TD]Telephone No
[/TD]
[TD]Email Address
[/TD]
[TD]Online Pushback
[/TD]
[TD]Ticket Policy
[/TD]
[TD]Unprofiled
[/TD]
[TD]Route Deals
[/TD]
[/TR]
[TR]
[TD]ACT
[/TD]
[TD]Web ID
[/TD]
[TD]Unprofiled
[/TD]
[TD]No
[/TD]
[TD]abc@aol.com
[/TD]
[TD]yes
[/TD]
[TD]Instant Ticket
[/TD]
[TD]Corporate Cards
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Ado
[/TD]
[TD]WEB ID
[/TD]
[TD]Unprofiled
[/TD]
[TD]NO
[/TD]
[TD]
[/TD]
[TD]yes
[/TD]
[TD]Auto Ticket
[/TD]
[TD]Corporate Card
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]AN
[/TD]
[TD]
[/TD]
[TD]Yes
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]yes
[/TD]
[TD]Auto Cancel
[/TD]
[TD]Unprofile Permitted
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]AM
[/TD]
[TD]Web ID
[/TD]
[TD]Unprofiled OK
[/TD]
[TD]No
[/TD]
[TD]
[/TD]
[TD]yes
[/TD]
[TD]Auto Ticket
[/TD]
[TD]Corporate Cards
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]AMST
[/TD]
[TD]
[/TD]
[TD]No profile no travel
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]no
[/TD]
[TD]Instant Ticket
[/TD]
[TD]Numerous all over the globe
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]ARA
[/TD]
[TD]WEB ID
[/TD]
[TD]yes
[/TD]
[TD]tbc
[/TD]
[TD]
[/TD]
[TD]no
[/TD]
[TD]Instant Ticket
[/TD]
[TD]US
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]***
[/TD]
[TD]No
[/TD]
[TD]YES
[/TD]
[TD]800
[/TD]
[TD]
[/TD]
[TD]no
[/TD]
[TD]Permitted
[/TD]
[TD]None
[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]


I have set the account info to be the filter. I want the row labels to show the top lables, and then values to be displayed.
All I seem to get is this:


[TABLE="width: 259"]
<tbody>[TR]
[TD]Account Info
[/TD]
[TD]ACT
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row Labels
[/TD]
[TD]Count of Profile's
[/TD]
[/TR]
[TR]
[TD]Web ID
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]Unprofiled
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]No
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]abc@aol.com
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]Grand Total
[/TD]
[TD]1



[/TD]
[/TR]
</tbody>[/TABLE]

This is what I would like (with one selected as example)
[TABLE="width: 259"]
<tbody>[TR]
[TD]Account Info
[/TD]
[TD]ACT
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row Labels
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Profiles
[/TD]
[TD]Web ID
[/TD]
[/TR]
[TR]
[TD]PTA
[/TD]
[TD]Unprofiled
[/TD]
[/TR]
[TR]
[TD]Telephone No
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Email address
[/TD]
[TD]abc@aol.com
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Jerry Sullivan]

Hi jontyoc,

A PivotTable isn't a good fit for this purpose. The Values area of the PivotTable is used to aggregate data by Sum, Count, Average,...etc. and you aren't trying to use it for that purpose.

Except for a few scenarios that aren't applicable here, the Values area only returns numerical data or errors.

You could possibly cobble together something like the desired result you show using Row Labels, but because each of the Row Fields represents a hierarchy level, it would be clunky at best.

Better alternatives would be:

1. A drop down list of accounts using Data Validation and Index-Match or Vlookup lookup formulas to return the values.

2. If you have an external data source, perhaps parameter query returned to an Excel Table.

 

[jontyoc]

Thanks Jerry.
Will have another think about it then.