Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot table sorting
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot table sorting
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 "Compass Rose" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot table sorting
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. -- HTH... Jim Thomlinson "Compass Rose" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table Sorting | Excel Worksheet Functions | |||
PIVOT Table Sorting - PLEASE Help!!!!! | Excel Discussion (Misc queries) | |||
Pivot Table Sorting | Excel Worksheet Functions | |||
Sorting / Pivot Table | New Users to Excel | |||
Pivot Table sorting | Excel Discussion (Misc queries) |