I am trying to creat a pivot table which includes the text 'JAS' (someone's initials) and found that this text always is sorted to appear first. I am beginning to think it is a bug. I have attached a very simple file which illustrates the problem.
Bizarely, as far as I have experimented, this behaviour only occurs with the text 'JAS' and I have tried both Excel 2002 & 2007.
Can anyone explain this behaviour?
sort is manual
right click name button (or double click)
from dropdown
click field
click advanced
check sort ascending
refresh data
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
It appears (to me) that the sort is normally automatic. E.g. if I have a column:-sort is manual
Name
Z
A
JAS
C
B
Then in the pivot table they will be like this:-
Name
JAS
A
B
C
Z
I have tried a manual sort but that doesn't work either.
your not following my instructions,if you do what i said then you will turn on automatic sorting.
err correction that worked fine on my '97 version but not on my 2003 version hmmmmmmmm
Last edited by martindwilson; 07-02-2009 at 05:45 AM.
Sorry Martin, I think I have tried what you suggest! I only have access to Excel 2000 (at work) right now and I right-click Name (in completed pivot table) then:-
Field Settings
Advanced
Ascending
Nothing changes. Have you looked at the spreadsheet I attached & can you sort this?
Thanks.
Martin, I can replicate Dast's issue and I've seen it before...
Previously I've thought it was down to XL being "helpful" and recognising certain strings as dates (ie Jan would be listed prior to Apple as it is interpreted as a date by XL and thus numeric and therefore < text) but I don't think that's the case here.. if JAS entry is altered to Apple it is listed last ! Very odd.
I looked around yesterday on line to see if it was documented but couldn't find anything... am intrigued myself.... the formatting of the source values is identical etc...
Last edited by DonkeyOte; 07-02-2009 at 06:03 AM.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
yep ive looked at the spread sheet and nope i cant do it either!!! i'm stumped!
i've run code to clear out old items and done most of what donkeyote has done
only way was to drag it to the right place, wierd
Last edited by martindwilson; 07-02-2009 at 06:10 AM.
Is JAS mmm code for January in any given locale ?
If you change JAS to Apple it does sort correctly... whereas if you change it to JAN it's still seen by XL as a date and appears first (as number < text)
EDIT: short of appending the names at source to incorporate say a trailing space thereby preventing the default "coercion" on the PT I can't think of a quick fix I'm afraid.
Last edited by DonkeyOte; 07-02-2009 at 06:17 AM.
Thanks for your interest folks. I too was thinking that JAS might be significant for some reason but I can't see what (I am using UK/British locale).
For my purpose I had done what DonkeyOte suggested & added a space at the end of every name - this is enough to force the sorting to behave correctly. I'm just intrigued as to what's happening.
yep i tried jas. no joy but jas.. sorts
It's been a few months, but I hope someone is still monitoring this thread. I was teaching myself pivot tables by using the excel help file, the microsoft training site, http://office.microsoft.com/training...RP010287001033 and the test files they give you to download.
Excel failed to properly sort the downloaded training files!!!!!!!! Not my data, theirs! The task is to sort the sales staff by sales numbers. Peacock is supposed to come out on top, but he never does. Sumaya does. I noticed that no matter how many times I checked 'all' under sales staff, when i went back the check was always by Sumaya. Then I unchecked 'all' and 'Sumaya'. Sure, Peacock is now on top but it is still wrong! Fuller is 4th, although he has 50,000 more in sales than King in 3rd... and that's not the only sorting error.
I am attaching my sample. Any insight would be greatly appreciated.
natcolley, welcome to the board, however please note:
Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.
For anyone stumbling upon this thread looking for an answer: To go Pivot Table Options --> Totals & Filters tab --> un-check "Use Custom Lists when sorting"
I had a similar problem with a pivot table always putting "Phase" above all other row items. Turns out I had a custom sort list that started with "Phase". You can find your custom sort lists by selecting a range of data then Data tab --> Sort --> select "Custom List" from Order dropdown. In my case, I had a macro that at one point created a custom list and it was still sitting there.
Works in Excel 2010, probably 2007.
Follow this post
http://www.excelforum.com/excel-char...wont-sort.html
I changed the column location, refreshed the pivot table and it worked. Not logic ha
I had a problem sorting Area Sales Manager and Document Controller.
Document Controller used to always appear on top of the list. Not anymore.
I am using MS Excel 2016
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks