Pivot table sorting

C

Compass Rose

Excel 2003

I have a column of numbers in a pivot table that is a concatenation of 2
columns.

224003
225012
226009
230101
230205
230807
230904
231016
301013

After sorting, stripping out packed zeros and splitting back into 2 columns,
the columns look like this:

224 3
225 12
226 9
2301 1
2302 5
2308 7
2309 4
231 16
301 13

I would like the columns to look like this:

224 3
225 12
226 9
231 16
301 13
2301 1
2302 5
2308 7
2309 4

Is it possible?

TIA
David
 
G

Gary Brown

Use a cheater column that references the first column with something like...
=value(a1)
Then sort on the cheater column.
FYI, Excel 2003+ will ask if you want it sorted as if the text numbers were
real numbers and you wouldn't need a cheater column.
--
Hope this helps.
If this post was helpfull, please remember to click on the ''''YES''''
button at the bottom of the screen.
Thanks,
Gary Brown
 
J

Jim Thomlinson

Nothing straight forward to do that. The only thing I can think of off the
top of my head would be to add an extra field into your source data that
returns the number of characters in the numbers to be sorted so your pivot
table looks like this...

3 224 3
3 225 12
3 226 9
3 231 16
3 301 13
3 2301 1
3 2302 5
4 2308 7
4 2309 4

(note the 3's and 4's won't repeat)
Obviously the pivot table will be sorted on the 3's and 4's first and then
on the actual numbers. At the end you can just hide the 3's and 4's column.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help!! find two number recurring numbers in row?? 1
Countifs or a pivot 1
Data sorting problem 2
Pivot Table help 0
How to combine queries???? 2
Gears 14
NESTED ANDIF STATEMENTS 1
Multiple table lookup 13

Top