Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post

    Numerical sorting question

    First I apologize for asking what I think is a pretty simple question. I tried searching the archives but probably wasn't typing in a good search phrase as I came up empty.

    I just need to know if there is a setting I can change in Excel 03 that will force it to sort numbers numerically instead of what I'll call alpha numerically.

    By "numerically" I mean that a sort would look like this: 1,2,3,10,11,12,13,101,102, 103. This is the result I would like.

    By "alpha numerically" I mean the same numbers come out like this: 1,10,101,102,103,11,12,13.

    I have no idea if I'm using the correct terms for these two situations......sorry if I'm grossly wrong.

    In a parts list having things come up other than in true numerical order can make someone miss finding the item down the road.


    I know 03 is really old but every time I try to upgrade on this PC I run into huge problems with all my old macros. I'm running newer versions on PCs that aren't affected by this issue.

    Thanks,
    BH

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    BH,

    Excel will sort by the type of data in the cells. That is if the cells contains alpha characters (even numerical characters that are not numbers) it will alpha sort. If the cells contain true numerical characters it will do a numerical sort.

    You can usually tell the type of data in a cell by it's alignment, e.g. if 10 is in the cell and is left aligned it is Text or Alpha! If it is right aligned it is numeric. This, of course, assumes that the user hasn't applied their own formatting and is using the default cell formatting.

    BHSorting.JPG

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    I guessing your "alpha numbers" are text representations of the numbers.
    Can you create a column that has them converted to numbers?

    In 2010 when a sort is attempted on these alpha representations, there's a "warning" that allows the sort to treat them as numbers.

  4. #4
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post
    Thanks guys. Here is a better explanation of what I'm getting vs. what I would like.

    What I'm getting when sorting by Column A:

    Column A
    WH 101
    WH 1014
    WH 102
    WH 103


    What I would like when sorting by Column A:

    Column A
    WH 101
    WH 102
    WH 103
    WH 1014


    I could move the "WH" to another column inserted to the left if that would help. It's the proper numerical placement of the 1014 that I would like to see.

    Thanks again,
    BH

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Clip0001.jpg

    Using WH in column A and the numbers in B, I wrote a concatenate statement in the 3rd column.
    Then, sorted columns A and B by the numbers in B. Does this work for you?

  6. #6
    WS Lounge VIP mrjimphelps's Avatar
    Join Date
    Dec 2009
    Location
    USA
    Posts
    3,401
    Thanks
    447
    Thanked 404 Times in 376 Posts
    Quote Originally Posted by bhdavis View Post
    Thanks guys. Here is a better explanation of what I'm getting vs. what I would like.

    What I'm getting when sorting by Column A:

    Column A
    WH 101
    WH 1014
    WH 102
    WH 103


    What I would like when sorting by Column A:

    Column A
    WH 101
    WH 102
    WH 103
    WH 1014


    I could move the "WH" to another column inserted to the left if that would help. It's the proper numerical placement of the 1014 that I would like to see.

    Thanks again,
    BH
    The first set of data is definitely in text format, because of the way it is sorting the number portion, and because of the fact that it has letters and numbers.

    To expand on what kweaver posted (#5), you can do Text to Columns, to separate the single column into two columns. (kweaver has them divided into "prefix" and "number".)

    (I am assuming that the 2nd part of each cell is only numbers, with no letters ever mixed in.)

    I'm not sure how to access it in Excel 2003, but here's how to access it in Excel 2007: Choose the Data tab, then Text to Columns.

    Here's how it works:

    You will have to choose between "fixed width" and "delimited", to let Excel know how to split the single column into two parts.

    Fixed Width means there will always be a set number of characters before the beginning of column 2. In your case, there appears to be always three characters -- "WH ".

    Delimited means that there will always be a certain character (or characters) between the columns. In your case, there appears to be always a space between the two columns, and no space anywhere else.

    Anyway, you highlight the entire column, you choose Text to Columns, then you choose between Fixed Width or Delimited. You then either set the width (Fixed Width), or you specify the delimiter(s) (Delimited). You then tell it OK.

    Like magic, your data is split into two columns, and the 2nd column will now be Numeric, which means that it will sort by numeric value, not by character value.
    Last edited by mrjimphelps; 2015-12-04 at 16:05.

  7. The Following User Says Thank You to mrjimphelps For This Useful Post:

    garthp (2015-12-10)

  8. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts

    Sorting a String with a Numeric Component

    bhdavis,

    Here is a VBA approach. Select the cells in the column you wish to sort then click the Sort button. The values will be sorted according to the number segment of the value. The code assumes that there is a space between the prefix and the number. The code works by splitting the value between prefix and number, assigning them to a two dimensional array, sorting them, then rewriting them back to the sheet. Note: You can select any contiguous or non-contiguous block as long as they are in the same column. You can also set the destination column to either write the sort to a new column or overwrite the existing values (change value in code line indicated in blue). The number of rows is unlimited. I have supplied the workbook in Excel 2003 and 2007+ formats for those who are interested

    HTH,
    Maud

    bhdavis.png

    Place in a Standard Module:
    Code:
    Option Base 1
    
    Public Sub SortSelected()
    'On Error GoTo Errorhandler
    '----------------------------
    'DECLARE AND SET VARIABLES
    Dim cell As Range, s, t()
    Dim I As Long, J As Long
    Dim row As Long, index As Long
    Dim SourceCol As Long, DestinationCol As Long
    ReDim t(Selection.count, 2)
    index = 1
    row = Selection.row
    DestinationRow = 2
    SourceCol = Selection.Column
    DestinationCol = 2 'CHANGE TO THE COLUMN TO WRITE TO
    '----------------------------
    'SPLIT AND SET SLECTED VALUES INTO ARRAY
    For Each cell In Selection
        s = Split(cell, " ")
        t(index, 1) = s(0)
        t(index, 2) = Val(s(1))
        index = index + 1
    Next cell
    '----------------------------
    'SORT ARRAY
    For J = 1 To UBound(t) - 1
        For I = 1 To UBound(t) - 1
            If t(I, 2) > t(I + 1, 2) Then
                temp1 = t(I, 1)
                temp2 = t(I, 2)
                t(I, 1) = t(I + 1, 1)
                t(I, 2) = t(I + 1, 2)
                t(I + 1, 1) = temp1
                t(I + 1, 2) = temp2
            End If
        Next I
    Next J
    '----------------------------
    'OVERWRITE WITH SORTED VALUES
    For I = 1 To UBound(t)
        Cells(row, DestinationCol) = t(I, 1) & " " & t(I, 2)
        row = row + 1
    Next I
    MsgBox "Selected cells in column " & SourceCol & _
           " were sorted and placed in column " & DestinationCol
    '----------------------------
    'CLEANUP
    Erase t()
    Exit Sub
    '----------------------------
    'HANDLES ERROR IF USER DOES NOT MAKE PROPER SELECTION
    Errorhandler:
        MsgBox "Please select values in one column to sort"
    End Sub
    Attached Files Attached Files
    Last edited by Maudibe; 2015-12-04 at 23:52.

  9. The Following User Says Thank You to Maudibe For This Useful Post:

    XPDiHard (2015-12-07)

  10. #8
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by mrjimphelps View Post

    Like magic, your data is split into two columns, and the 2nd column will now be Numeric, which means that it will sort by numeric value, not by character value.
    Absolutely......perfect !! Thanks.

    On my first try I wasn't sure what it was going to do when I said yes to replacing the data. Turned out it skewered all the data in the next column to the right.....chuckle.

    A little experimentation with inserting a blank column to the right and ........voila..........perfect.

    Thanks again for all your help.
    BH

  11. #9
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post
    Thank you for this as well. The column split with "Text to columns" seemed like it might be a bit less complicated so I took that route. I really do appreciate your time in laying it out this way though. Something to be learned.

    BH


    Quote Originally Posted by Maudibe View Post
    bhdavis,

    Here is a VBA approach. Select the cells in the column you wish to sort then click the Sort button. The values will be sorted according to the number segment of the value. The code assumes that there is a space between the prefix and the number. The code works by splitting the value between prefix and number, assigning them to a two dimensional array, sorting them, then rewriting them back to the sheet. Note: You can select any contiguous or non-contiguous block as long as they are in the same column. You can also set the destination column to either write the sort to a new column or overwrite the existing values (change value in code line indicated in blue). The number of rows is unlimited. I have supplied the workbook in Excel 2003 and 2007+ formats for those who are interested

    HTH,
    Maud

    bhdavis.png

    Place in a Standard Module:
    Code:
    Option Base 1
    
    Public Sub SortSelected()
    'On Error GoTo Errorhandler
    '----------------------------
    'DECLARE AND SET VARIABLES
    Dim cell As Range, s, t()
    Dim I As Long, J As Long
    Dim row As Long, index As Long
    Dim SourceCol As Long, DestinationCol As Long
    ReDim t(Selection.count, 2)
    index = 1
    row = Selection.row
    DestinationRow = 2
    SourceCol = Selection.Column
    DestinationCol = 2 'CHANGE TO THE COLUMN TO WRITE TO
    '----------------------------
    'SPLIT AND SET SLECTED VALUES INTO ARRAY
    For Each cell In Selection
        s = Split(cell, " ")
        t(index, 1) = s(0)
        t(index, 2) = Val(s(1))
        index = index + 1
    Next cell
    '----------------------------
    'SORT ARRAY
    For J = 1 To UBound(t) - 1
        For I = 1 To UBound(t) - 1
            If t(I, 2) > t(I + 1, 2) Then
                temp1 = t(I, 1)
                temp2 = t(I, 2)
                t(I, 1) = t(I + 1, 1)
                t(I, 2) = t(I + 1, 2)
                t(I + 1, 1) = temp1
                t(I + 1, 2) = temp2
            End If
        Next I
    Next J
    '----------------------------
    'OVERWRITE WITH SORTED VALUES
    For I = 1 To UBound(t)
        Cells(row, DestinationCol) = t(I, 1) & " " & t(I, 2)
        row = row + 1
    Next I
    MsgBox "Selected cells in column " & SourceCol & _
           " were sorted and placed in column " & DestinationCol
    '----------------------------
    'CLEANUP
    Erase t()
    Exit Sub
    '----------------------------
    'HANDLES ERROR IF USER DOES NOT MAKE PROPER SELECTION
    Errorhandler:
        MsgBox "Please select values in one column to sort"
    End Sub

  12. #10
    WS Lounge VIP mrjimphelps's Avatar
    Join Date
    Dec 2009
    Location
    USA
    Posts
    3,401
    Thanks
    447
    Thanked 404 Times in 376 Posts
    Quote Originally Posted by bhdavis View Post
    A little experimentation with inserting a blank column to the right and ........voila..........perfect.
    Sorry, I forgot the part about first inserting a blank column to the immediate right of the column you want to split.

    I'm glad it worked out for you.

    Many years ago, when I discovered Text to Columns, I thought that that was the best feature in Excel. I still think so.

  13. #11
    New Lounger
    Join Date
    Jul 2004
    Location
    Minneapolis, Minnesota, USA
    Posts
    16
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Another alternative (there are always several ways to do things) that I haven't seen mentioned.

    This one is particularly useful if your mixed alpha/numeric data is input as a single item and possibly from a source that is not in your control to separate at input time.

    It reverses the process of splitting the data into two columns and recombining it to create the full data.

    Use the full data and use a formula to create the numeric sort column.
    The formula can be anything that will separate the data, similar to the text to columns feature.

    temp.png

    The two formulas I used (giving identical results) are:
    =VALUE(MID(D4,3,20))
    =VALUE(MID(D6,FIND(" ",D6),20))
    where the row matches the cell you put the formula in. This assumes your data is in D

    The Value() function makes sure that it doesn't think you want text.
    the ,20) in the FIND function is personal habit when using the MID function. It is a limit for the number of characters returned but will only return the characters in the string, up to that limit.

  14. #12
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    The column split with "Text to columns" seemed like it might be a bit less complicated so I took that route
    IMHO, I would think that just pressing a button to complete the process, no worries of altering cells formulas, and not dealing with helper columns would be the way to go. To some, pasting some code into a module may seem daunting but it is the same basic computer task you would perform in the GUI. Add the formula to a cell as you would with any formula and your home free.

    Perhaps, working with Excel and VBA every day in my job has made me less understanding of those who don't.

    Sorry for the assumption,
    Maud
    Last edited by Maudibe; 2015-12-11 at 12:27.

Posting Permissions

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