sorting in pivot table?

[lars]

I have a pivot table with air cites as rows and when i sort in alphabetic order two cities stay at the top

SAT
JAN

I think Excel is looking at these two cities as dates SAT - Saturday and Jan - January

I tried copying a 0 and special paste/add
and text to columns but to no avail.

Any thoughts?

[Chris Davison]

Me neither....

I'll get slaughtered for this suggestion, but hey :

highlight the column and replace JAN with JAN_

then replace SAT with SAT_

where the _ is just a blank space

this may very well stuff up any formulae that relate to those cells though if they are looking for JAN or SAT (precedents or dependants, I can never remember which is which)

Someone else suggest something please, this solution deserves a big wooden spoon

[lars]

I'll give it a shot, but I hate to add anything to the download I'm using.

Hmmm.....

Thanks

Big spoon indeed

[Chris Davison]

I hear you...!

(for what it's worth, I did spend 1/2 hour trying other options but the pivot still did the same as yours..)

[Mark W.]

lars, when you sort using the Autosort PivotTield Field option on the sort will be governed by matching entries in the Custom sort lists created and displayed using the Tools | Options... menu command. As I see it you have 3 options...

1. Add a trailing space to each of your codes
2. Delete the customs lists beginning with "Sun, Mon, Tues,..." and "Jan, Feb, Mar,...".
3. Manually sort the field using the Data | Sort... menu command.

[ This Message was edited by: Mark W. on 2002-05-15 07:25 ]

[lars]

Chris thanks for the help

Mark,
I tried adding a space and it didn't work, when I sorted using the pivot table sort it put Jan and Sat at the bottom now. Still not in alphabetic order. I was aware of sorting it using the data/sort but was hoping it would be possible through the pivot itself. I have some very wide pivots.

Thanks for the help!

[Chris Davison]

Lars, did you put the space at the END or the BEGINNING ?

if you want to add a space, put it at the end

[lars]

chris, I put it at the end, then refreshed and it put Jan and SAT at the end of the list. So it went from the top to the bottom.

[Chris Davison]

there's something else wrong here then

can you do a quick =LEN(cell)

where "cell" is the cell that houses Jan_ in your data (ie not the pivot table)

the answer should be 4

[lars]

Chris, I got 4.