Results 1 to 14 of 14
  1. #1
    Star Lounger
    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

  2. #2
    Plutonium Lounger
    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.

  3. #3
    Star Lounger
    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

  4. #4
    Plutonium Lounger
    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.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    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

  6. #6
    Star Lounger
    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!

  7. #7
    Plutonium Lounger
    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 string-prefixed 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...

  8. #8
    3 Star Lounger Jim Cone's Avatar
    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 add-in "Special Sort".
    It should do what you want as it will sort "decimal" numbers into strict numerical order.
    The add-in 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

  9. #9
    Star Lounger
    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".

  10. #10
    WS Lounge VIP sdckapr's Avatar
    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

  11. #11
    3 Star Lounger Jim Cone's Avatar
    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

  12. #12
    Star Lounger
    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

  13. #13
    Star Lounger
    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>

  14. #14
    Star Lounger
    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 add-in. 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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •