How can I have Excel reorder my rows correctly?

Question

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?

Answer 1

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

Answer 2

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

Answer 3

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.

Answer 4

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.