Sort order not being matched in pivot table

[Deanery Phil]

Hi

I have a large sheet of data compiled from other spreadsheets containing
data on people. 1 column is a category column based on the number of years
each person on the sheet took to get to a particular point in their career -
anything from 2 to 29 years so the categories go from 2-3 years to 21-29
years.

I initally had a sorting problem in the sheet - the categories "10-12",
"13-15" etc were coming before "2-3", "4-5", etc! I searched through here and
found a work around - formatting them using a custom time format as hours and
minutes, the spreadsheet now sorts perfectly - "2-3" comes before "10-12" etc.

However - I ran a a pivot table to show the number in each category (in
columns) and the sort order on the pivot table (ie the order of the columns)
is "10-12", "13-15" etc and 2-3 etc comes after! Naively I assumed the pivot
would sort in the same way as the spreadsheet, using the same formatting
logic.

Any idea how I get around this please? As an aside, I've tried doing a
crosstab of this data in Crystal Reports and oddly enough, the same thing
happens with the columns...

Thanks in advance for any help.

 

[Dave Peterson]

I'd change my data.

I'd include as many leading 0's as I needed to make all the entries have the
same format:

01-03
or
001-003

 

[Deanery Phil]

Thanks - not ideal for the table I want to present but I can see how it would
be useful for some other instances. Does seem a bit daft that something as
powerful as Excel gets so easily confused though...

I chucked it all into Crystal and customised the column headers from there
in the end - a bit OTT normally but I've been asked to put all the reports
into Crystal so the reports can update in real time as the data updates.

Thanks!

Phil