Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Nixa, Missouri, USA
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sorting a Colum of numerical values (XP SP-1)

    I have the, er privilege? of sorting out our maintenance spreadsheets twice a year.
    What is getting old is the hand picking of numbers. For an audit 1/2AASC-5 is first then 1 comes next then 100 then 117 then 2 then 201 then 204c then 296-D then 3.. You get the idea.
    Is there anyway, without programming! <img src=/S/wink.gif border=0 alt=wink width=15 height=15> That I can get Excel to sort this way. Right now it is close of business Monday and the reports are due close of business Tuesday. I require a 10 day lead time before every audit and get 24 to 48 hours.
    Yes, I think they (maintenance) should come up with a better idea however, "that

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Sorting a Colum of numerical values (XP SP-1)

    I hope someone has a better solution, as this suggestion, using Custom Lists, may be still a bit maintenance intensive, depending on how frequently your data changes. (And without seeing all your data it's hard to know if it will work well.)

    You may need to build a set of "classes" of your data, which appear to be the first few characters of your maintenance code system, but you could try without first. Create a columnar list of the "classes" of your items (or just the prefix of the items) in a column, sorted the way you want. Under Tools | Options | Custom Lists | Add | Import them from the column of classes.

    To sort the data, you may need to build a column containing the classes, or just use that data that has the prefixes, select the table, and select, Data | Sort, select the field containing the "classes" or prefixes, click Options, click the Drop-Down, select the Custom order, OK out.

    HTH (& HTW).
    -John ... I float in liquid gardens
    UTC -7ąDS

  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: Sorting a Colum of numerical values (XP SP-1)

    Make sure all the values are text. It sounds like some are text (1/2AASC-5, 204c, 296-D, etc) but some MIGHT be numbers (1, 100,117, 2, 201, 3, etc) Excel will sort the NUMBERs first in numerical order followed by the TEXT alphabetically.

    If the numbers are in col A starting in A2 (A1 for header) enter into a blank column:
    <pre>=IF(ISNUMBER(A2),FIXED(A2,0),A2)</pre>


    and this will convert everything to text and this column can be sorted. The only problem is that "1" will come BEFORE "1/2AASC-5" ALPHABETICALLY since "1" with NOTHING after it is < "1/2". All the others should sort OK. You could add a column after you sort the column with the formulas and add FILL the numbers from 1 to whatever. after it is filled you could manually "fix" and switch the "out-of-ordered ones. Once the "fix" is done, this column can be used for sorting and the formula column may be deleted.

    If you do this alot and you have the complete list of sorted values, you could bypass all of that and sort THIS list in another location, then use:
    <pre>=MATCH(A2,SortedList,0)</pre>

    in a blank column where sorted list is the name or range for the list of all sorted names. This column can be sorted and it will be live. If new "numbers are added" that are NOT in the sorted list the formula will give an error until you add it to the appropriate place in the sorted list.

    Steve

Posting Permissions

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