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

[KentWard]

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

[JohnBF]

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).

[sdckapr]

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