I have a problem sorting pivot table data. The data is the result of a formula that returns "Week #<number> <Month> <Year>. Something like Week #5 Jan 2015.
These results are listed in the Column Labels & Values. The cells are General format, but I've tried changing it to Number format & still doesn't sort the way I'd like.
When pivotized it shows Week #1, 10, 11, 12... 2, 20, 21, 22... 3, 30 etc... Eventually when the two digit weeks runs out it shows the single digits again at the end of the table.
I need it to sort numerically Week #1, 2, 3, 4, 5, 6.
I've tried Sort Options....
Tools & Filters >> Unchecking the "Use Custom Lists When Sorting" ... Didn't work
Display >> Field List "Sort A to Z ... Didn't work
Display >> Fielf List "Sort in Data Source Order" ... Didn't work
I can get it to sort correctly if I format the week # as a 2-digit # (01, 02 etc) but I would prefer not to if there's another way to get it to sort properly.
This Pivot table is large & I'd like to keep the formula results together if possible instead of breaking it up to 2 different results.
Bookmarks