Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sorry for the vague question. Let me explain.

I am pulling data from my company's database. One of the columns is the line number on a Sales Order. However, the way our system works is that if an SO line item is composed of multiple pieces (an assembly) then each of those pieces are a sub-set with their own numbering scheme.

The numbering scheme for these sub-items appears to Excel (and a normal human being for that matter) as a decimal. In other words, sub-item #12 within SO line #38 is stored in the column as "38.12". So when I sort by this column I'm getting sequential results like:

38.10 38.1 38.11 38.12 38.13

In actuality, the sequence should be 38.1, 38.2, 38.3, ..., 38.10, 38.11, 38.12 etc. I'm definitely not a programmer, but I think this may be an easy fix for some of you that know programming. How do I order these rows correctly?

share|improve this question
    
Auch this is nasty, I don't think Excel's sorting order will take into account that it should sort the numbers after the dot... –  Ivo Flipse Feb 9 '10 at 15:02
1  
Some notes on Sorting in Excel: mvps.org/dmcritchie/excel/sorting.htm –  Mehper C. Palavuzlar Feb 9 '10 at 15:23
1  
I was reading that article and I really have no idea how to make it work, but you have to create a new column with the suffix part only to sort it correctly (and have that sorting order expanded over the entire workbook) –  Ivo Flipse Feb 9 '10 at 15:26

4 Answers 4

up vote 1 down vote accepted

Split the column into two and sort on the decimals column. This explains how - http://excel.tips.net/Pages/T003836_Sorting_Decimal_Values.html

share|improve this answer

You can hack it by using multiple columns in Excel.

Take the RAW string, use the SEARCH function to find the placement of the decimal point. Then use the LEFT and RIGHT functions to split the string content before & after the decimal point into separate cells.

Finally, sort based on LEFT, and RIGHT:

RAW   LEN   SEARCH LEFT RIGHT
31.1   4     3      31     1
31.2   4     3      31     2
31.3   4     3      31     3
31.4   4     3      31     4
31.5   4     3      31     5
31.11  5     3      31    11
31.12  5     3      31    12
31.14  5     3      31    14
31.25  5     3      31    25

Formula for SEARCH column: SEARCH(".",[RAW])

Formula for LEFT column: LEFT([RAW],[SEARCH]-1)

Formula for RIGHT column: RIGHT([RAW],[LEN]-[SEARCH])

share|improve this answer
    
I think I saw the len and search part in a formula, so you only got your right two columns, any idea how to write it as 1 formula? –  Ivo Flipse Feb 9 '10 at 17:35
    
I split it out in my answer for illustration purposes. The formulas can certainly be combined to reduce the number of columns needed. –  DWong Feb 9 '10 at 17:41
    
@Ivo, you mean like this: LEFT([RAW], SEARCH(".", [RAW])-1) and this: RIGHT([RAW], (LEN([RAW]) - (SEARCH(".", [RAW])))) –  DWong Feb 9 '10 at 17:55
    
Voila, excellent! Though I'd expect he only needs the last column in order to sort +1 –  Ivo Flipse Feb 9 '10 at 18:14
    
Well, he might have values in LEFT that are 32, 33, etc. In that situation both columns would be necessary. –  DWong Feb 9 '10 at 18:51
  1. Make sure they are stored as text, not numbers (select cells, right click, Format Cells, select "Text" in Number tab.)
  2. Data > Sort, make sure Header row is set correctly (look at selected cells), OK
  3. You should now have a dialog asking "The following sort key may not sort as expected because it contains some numbers formatted as text:", select "Sort numbers and numbers stored as text separately"

Once you've done this the sort button on the toolbar/ribbon should work as you want it.

share|improve this answer

You'd want to create your own custom list within Excel. After creating your own custom list, you can highlight your selection, click on the sort button, and choose your custom list to sort the objects.

Use this as your custom list:

.1
.2
.3
.4
.5
.6
.7
.8
.9
.10
.11
.12
.* 

Follow these steps to create your own custom lists:

To create a custom list, follow these steps if you are using Excel 2007:

1.Click the Office button and then click Excel Options. Excel displays the Excel Options dialog box.

2.Make sure Popular is selected at the left of the dialog box.

3.Click Edit Custom Lists. Excel displays the Custom Lists dialog box and hides the Excel Options dialog box. (Click here to see a related figure.)

4.Select the NEW LIST option from the Custom Lists list at the left of the dialog box.

5.In the List Entries portion of the dialog box, start typing the order in which you want the elements sorted.

6.When you are done, click the Add button.

7.Click OK to finish.

If you are using a version of Excel prior to Excel 2007 follow these steps instead:

1.Select Options from the Tools menu. Excel displays the Options dialog box.

2.Make sure the Custom Lists tab is selected. (Click here to see a related figure.)

3.Select the NEW LIST option from the Custom Lists list.

4.In the List Entries portion of the dialog box, start typing the order in which you want the elements sorted.

5.When you are done, click the Add button.

6.Click OK to finish.

share|improve this answer
    
I was almost really excited, then I realized I would have to type every number from 0.0 to (infinity).(infinity). Is there a wild card I can use such that my list looks like #, #.1, #.2, #.3, #.4, #.5, #.6, #.7, #.8, #.9, #.10, etc? –  NoCatharsis Feb 9 '10 at 16:18
    
Try this: .1 .2 .3 .4 .5 .6 .7 .8 .9 .10 .11 .12 .* –  Mark Feb 9 '10 at 16:58

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.