Numerical sorting question

Topic

New Reply

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

Reply | Quote

Replies

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.

42810-BHSorting

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Group "L" (Linux Mint)
with Windows 10 running in a remote session on my file server

Reply | Quote

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

Reply | Quote

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.

Group "L" (Linux Mint)
with Windows 10 running in a remote session on my file server

Reply | Quote

42811-Clip0001

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?

Reply | Quote

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

42818-bhdavis

Place in a Standard Module:

Code:

Option Base 1

Public Sub SortSelected()
‘On Error GoTo Errorhandler
[COLOR=”#008000″]’—————————-
‘DECLARE AND SET VARIABLES[/COLOR]
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
[COLOR=”#0000FF”]DestinationCol = 2[/COLOR] [COLOR=”#008000″]’CHANGE TO THE COLUMN TO WRITE TO[/COLOR]
[COLOR=”#008000″]’—————————-
‘SPLIT AND SET SLECTED VALUES INTO ARRAY[/COLOR]
For Each cell In Selection
    s = Split(cell, ” “)
    t(index, 1) = s(0)
    t(index, 2) = Val(s(1))
    index = index + 1
Next cell
[COLOR=”#008000”]’—————————-
‘SORT ARRAY[/COLOR]
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
[COLOR=”#008000″]’—————————-
‘OVERWRITE WITH SORTED VALUES[/COLOR]
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
[COLOR=”#008000″]’—————————-
‘CLEANUP[/COLOR]
Erase t()
Exit Sub
[COLOR=”#008000″]’—————————-
‘HANDLES ERROR IF USER DOES NOT MAKE PROPER SELECTION[/COLOR]
Errorhandler:
    MsgBox “Please select values in one column to sort”
End Sub

Reply | Quote

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

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

42818-bhdavis

Place in a Standard Module:

Code:

Option Base 1

Public Sub SortSelected()
‘On Error GoTo Errorhandler
[COLOR=”#008000″]’—————————-
‘DECLARE AND SET VARIABLES[/COLOR]
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
[COLOR=”#0000FF”]DestinationCol = 2[/COLOR] [COLOR=”#008000″]’CHANGE TO THE COLUMN TO WRITE TO[/COLOR]
[COLOR=”#008000″]’—————————-
‘SPLIT AND SET SLECTED VALUES INTO ARRAY[/COLOR]
For Each cell In Selection
    s = Split(cell, ” “)
    t(index, 1) = s(0)
    t(index, 2) = Val(s(1))
    index = index + 1
Next cell
[COLOR=”#008000”]’—————————-
‘SORT ARRAY[/COLOR]
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
[COLOR=”#008000″]’—————————-
‘OVERWRITE WITH SORTED VALUES[/COLOR]
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
[COLOR=”#008000″]’—————————-
‘CLEANUP[/COLOR]
Erase t()
Exit Sub
[COLOR=”#008000″]’—————————-
‘HANDLES ERROR IF USER DOES NOT MAKE PROPER SELECTION[/COLOR]
Errorhandler:
    MsgBox “Please select values in one column to sort”
End Sub

Reply | Quote

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.

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.

Reply | Quote

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

Reply | Quote