Results 1 to 14 of 14

20050924, 04:04 #1
 Join Date
 Jun 2001
 Location
 Brisbane Australia
 Posts
 55
 Thanks
 0
 Thanked 0 Times in 0 Posts
Sorting Numbers (n.n.n) (Excel 2000)
Hello
I'm interested in discovering if there is an easier way of doing this than how I've done it to date. We have a number of requirements (a lot actually!) which have numeric identifiers in the format "4A.09.99.99" which are in Excel and also in Word. The numbering schema is from Word and we are stuck with this and cannot change it. The issue is that the third and fourth number pairs contain numbers that do not have leading zeros (i.e. 1, 2, 3,...11, 12, 13,...etc). Consequently when these fields are sorted in Excel we have 4A.01.11.1 sorted before 4A.01.2.1 and so on across the board.
I have managed to reach a workaround by using Excel formulas to insert the leading zeros in the last two fields so that 4A.01.2.1 becomes 4A.01.02.01 and these will then sort properly in ascending sequence but I have issues in these varying from the original and don't really want to keep swapping back and forth. I realise that I could also create multiple cells containing each number group and sort hierarchically across multiple columns but before I continue with either process I am interested in discovering if there is any other solution that leaves the original data untouched.
Has anyone solved this problem in an "elegant" manner without resorting to one of my workarounds?
Thanks

20050924, 09:19 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Sorting Numbers (n.n.n) (Excel 2000)
Say that the original 'numbers' as in Word are in column A. You can use formulas in column B to obtain sortable 'numbers'. You can keep column A, yet sort on column B. You can even hide column B after sorting.

20050924, 09:24 #3
 Join Date
 Jun 2001
 Location
 Brisbane Australia
 Posts
 55
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Sorting Numbers (n.n.n) (Excel 2000)
Thanks Hans
I know that I can do that which is how I've resolved it so far. I was hoping there might be some way to convince Excel to sort it correctly without resorting to that but I expect it can't be done.
Thanks

20050924, 09:33 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Sorting Numbers (n.n.n) (Excel 2000)
Perhaps in a future version. At the moment, you'll have to resort to an extra column for sorting purposes.

20050924, 10:43 #5
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Sorting Numbers (n.n.n) (Excel 2000)
In my opinion, excel is sorting it "correctly".
1) I don't think you will ever get a program to work directly on this. You will always have to create a "temporary column" to put it into a "perceived "correctness"
2) You are sorting text, not a number which is sorted on the priority from left to right regardlesss of length of the string. Numbers are dependent on their length for the value.
3) Even if a portion of this were real "numbers"
01.11 is still less than 01.2
Just my <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>
Steve

20050924, 11:08 #6
 Join Date
 Jun 2001
 Location
 Brisbane Australia
 Posts
 55
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Sorting Numbers (n.n.n) (Excel 2000)
Hi Steve
I guess I have to agree with you on that.
Oh well. I figured it wouldn't hurt to ask. <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>
Thanks guys!

20050924, 12:23 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Sorting Numbers (n.n.n) (Excel 2000)
The Open Office Calc Specifications for version 2.0 state:
<hr>A new sort algorithm known as the "natural sort" will be added to the cell sort options to provide users an option of sorting stringprefixed numbers in the "natural" way i.e. A1, A2, A3, ... , A19, A20 instead of the current behavior of A1, A10, A11, A12, ... ,A19, A2, A20, A3, A4, ... , A8, A9.<hr>So who knows...

20050924, 23:36 #8
 Join Date
 Feb 2002
 Location
 Portland, Oregon, USA
 Posts
 238
 Thanks
 0
 Thanked 3 Times in 3 Posts
Re: Sorting Numbers (n.n.n) (Excel 2000)
John,
You may want to try my Excel addin "Special Sort".
It should do what you want as it will sort "decimal" numbers into strict numerical order.
The addin has a total of 12 different sort methods available including color, prefix/suffix, and date.
It will also sort cell borders (optional) with the data.
In my tests it did 1000 rows with decimals in about 6/10 second.
It looks and reacts similar to the existing Excel sort utility.
Comes with a Word.doc install/use file.
Available free upon email request. Remove XXX from my address.
Regards,
Jim Cone
San Francisco, USA
jim.coneXXX@rcn.comXXX

20050925, 02:24 #9
 Join Date
 Jun 2001
 Location
 Brisbane Australia
 Posts
 55
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Sorting Numbers (n.n.n) (Excel 2000)
Thanks Jim
You should have "mail".

20050925, 03:19 #10
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Sorting Numbers (n.n.n) (Excel 2000)
Perhaps, but that is "simpler" than this. I assume the open office parses into text and numbers and works on those 2 segments. This involves parsing it into several number strings (and complicates it since the period is a delimiter, not a decimal).
I don't imagine you will find too many people working on algorithms to sort: 1.11 before 1.2 as is requested in the above...
Steve

20050925, 03:55 #11
 Join Date
 Feb 2002
 Location
 Portland, Oregon, USA
 Posts
 238
 Thanks
 0
 Thanked 3 Times in 3 Posts
Re: Sorting Numbers (n.n.n) (Excel 2000)
John,
No mail from Australia has been received here in San Francisco.
Currently 8:55 pm Saturday night.
(remove XXX from email address)
Jim Cone
jim.coneXXX@rcn.comXXX

20050925, 05:26 #12
 Join Date
 Jun 2001
 Location
 Brisbane Australia
 Posts
 55
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Sorting Numbers (n.n.n) (Excel 2000)
Hi Jim
I'm not sure why that email hasn't reached you yet. It is currently 10:25 pm San Francisco time as I write this and my email to your [correct] email address left here 3 hours ago. Maybe it is just hitching a slow boat across the Pacific and will arrive later. I'm patient.
John

20050925, 22:37 #13
 Join Date
 Jun 2001
 Location
 Brisbane Australia
 Posts
 55
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Sorting Numbers (n.n.n) (Excel 2000)
Hi Jim
In case my email still hasn't reached you.
You can reach me through john at heidemann dot id dot au
Thanks! <img src=/S/artist.gif border=0 alt=artist width=34 height=29>

20050928, 13:18 #14
 Join Date
 Jun 2001
 Location
 Brisbane Australia
 Posts
 55
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Sorting Numbers (n.n.n) (Excel 2000)
Hello Jim
Thankyou for that addin. It sorts the data exactly the way that I needed. <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
I haven't explored the other sort options that it provides but they all look extremely practical and useful too.
Many thanks!