Using custom list to establish sort order in pivot table

[RRinTetons]

I have a pivot table in which the label values need to be sorted according to a custom display order. I've got the display order column in the data that generates that PT, but I don't see a way to get the table sorted by a column that's in the data, but not in the PT.

I tried creating custom lists (Tools -> Options -> Custom Lists) with the desired label ordering, but I don't see a way to tell the PT to use a custom list for the sort.

I can fix it somewhat crudely by just concating the display order onto the front of the label, but that's not the way users are accustomed to seeing the data.

Ideas?

(There are five columns of labels that need to custom sorted.)

-
Richard Ray
Jackson Hole Mountain Resort

 

[SkipVought]

Hi,

Why can't you include in the PT, sort on the column and then hide the column?

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[RRinTetons]

When I tried that each time the data updated it re-exposed the column with the display order in it. OTOH, if I turn off the Auto-Format option in the PT definition maybe it wouldn't do that? I'll try that.

Would hiding the sort column also keep it from showing up in the subtotals, etc.? I guess I can figure that out..., I'll take a look at it tonight.


-
Richard Ray
Jackson Hole Mountain Resort

 

[RRinTetons]

Well, there's the reason, at least in Excel 2003. With 5 columns of data, plus five columns of sort informaition Excel barks at me that it's too many and that someting has to be removed.

Maybe 2007 has increased that limit? At any rate, the concated label does the job and users have only noticed it at first, so I guess it'll be OK.


-
Richard Ray
Jackson Hole Mountain Resort

 

[SkipVought]

Do you have any COLUMN items?

How many aggregation fields?

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[SkipVought]

...and what are the ROW fields and which fields are subtotaled?

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[RRinTetons]

There are 5 row fields with from 5 to 150 unique values. Only one column field, two data fields and one calculated data field. The two actual data fields are aggregated with a sum function, the calculated field divides one of the data fields by the other one with a formula that guards against div 0.

When I start dragging the display order field into the row area to use them to sort then hide I get up to nine fields and Excel barks, telling me:

"Microsoft Office Excel cannot make this change becasue there are too many row or column items. Drag at least one row or column field off the Pivot Table report, or to the page postiion.Alternatively, right-click a field, and then click Hide or Hide Levels on the shortcut menu."

I've tried adding the display order columns incremnetally, sorting on them and hiding them as I go, but that doesn't seem to do it, either.

On another note - the custom list sort strategy works, but there seems to be a limit on how long a custom list can be; when I import the sort order for the columns with more unique values they don't all get added to the custom list, so the sort is only partial.



-
Richard Ray
Jackson Hole Mountain Resort

 

[SkipVought]

Geez, Richard. With THAT kind of view, that you have in Jackson Hole, Wyoming, why would you care?

Just kidding!

There are other ways of summarizing data, other than a PT, but they take more time and effort. The performance can tae a hit, as there may be lots of complex calculations going on.

Here's a technique that I often use.

Use MS Query, via Data > Import External Data... faq68-5829 to generate the data for the ROW FIELDS.

Lay out the Two Column Field Headings.

Realizing that you have 3 calculated data fields, it will either be 3 vertical or 3 horizontal. Vertical will mean that you will need a Union All with 3 queries; one for each date field -- Horizontal, you'll need three columns for each Column Field Heading.

The actual data aggregations & other calculations would be performed with formulas -- probably the SUMPRODUCT function, using criteria in the ROW & COLUMN cells to generate values. The drawback is the setup time the adjustment to changing ROW values as the source changes and the time it takes the formulas to calculate.

Let me know.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}