Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Indexing Excel (Excel XP)

    (Edited by HansV - inserted <!t>[pre]<!/t> and <!t>[/pre]<!/t> tags to preserve spaces.)

    I have a list that have the index<pre>1.1 xxx
    1.2 yyy
    1.1.1 xxx
    1.1.2 xxx
    1.2.1 yyy
    1.2.2 yyy</pre>

    I would like to sort it from 1.1 to 1.11 etc

    If I change the value to text it sorts fine till 1.10 and then it goes wrong. If I change it to number it sort 1.1 1.2 and then 1.1.1.

    It is a very detailed & long list and it goes to 1.1.1.1.1.1. So manually is a NO NO & in Word I'm clueless and my superiors would prefir it to be in Excel

    Thanks for any ideas.

    Mario

  2. #2
    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: Indexing Excel (Excel XP)

    Mario,
    Couple of ideas that probably won't hunt...

    Pad all the numbers with zeros so 1.1 becomes 1.1.0.0.0.0
    or
    Change the index numbers to only one decimal place so
    1.1.1 .2 becomes 1.112

    Either way they should sort.

    Regards,
    Jim Cone
    San Francisco, CA

  3. #3
    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: Indexing Excel (Excel XP)

    Does this code do what you want? Highlight your range it sorts based on the index value. The principle is that it parses the string into its separate substrings, stores all in an array and then sorts based on the substrings. I would run it on a copy, I didn't test extensively and it writes over what you select.

    Steve

  4. #4
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Indexing Excel (Excel XP)

    I've given it to the friend who will test it on his problem. Thanks for the code & your time

Posting Permissions

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