MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Sorting problem

  Asked By: Rodolfo    Date: Dec 11    Category: MS Office    Views: 1377

There appears to be a conflict between Excel and VBA in sorting

Try typing the following into A1,A2,A3,A4 cells:
ABC-- 123

Sort the list via the Data menu, it sorts it into the following order:
ABC-- 123

Go to the VBA immediate pane...
?cells(1,1)<cells(2,1) returns False
?cells(3,1)<cells(4,1) returns False

That's got to be wrong!?



3 Answers Found

Answer #1    Answered By: Myrna Brown     Answered On: Dec 11

On the ASCII chart, space is 32, minus is 45 and one is 49, so a straight
ASCII comparison will indeed give the results you are getting from the VBA
immediate pane.

(Note that your statements in the immediate pane  shouldn't really be called
sorting policy, they are simply comparisons of two ASCII strings.)

The following sort order  description is from a web page, but indicates that
it's a direct quote from Excel help:

a.. Numbers: Excel sorts numbers from the smallest negative number to the
largest positive number.
b.. Dates: Excel sorts dates from earliest to latest.
c.. Alphanumeric: Excel sorts text from left to right, character by
character. For example, if you have the contents "H310" in a cell, Excel
would sort that cell after another cell that contains "H3", but before a
cell that contains the entry "H32." Excel sorts text in the following order:
0 1 2 3 4 5 6 7 8 9 (space) ! " # $ % & ( ) * , . / : ; ? @ [ \ ] ^ _ ` {
| } ~ + < = > A B C D E F G H I J K L M N O P Q R S T U V W X Y Z. Special
case: Excel ignores apostrophes (') and hyphens (-) unless two text strings
are the same except for a hyphen, in which case the text with the hyphen is
sorted last.
d.. Blanks: Blanks always come at the end.
(I haven't tried to find the help information to verify the quote.)

So, Excel has a quite complex sorting  policy.

This is not uncommon. Sort policies in other systems are frequently not
just a strict ASCII comparison.

Answer #2    Answered By: Reginald Thomas     Answered On: Dec 11

Very inconvenient for Excel to treat hyphens
in this way, they're unfortunately very common in the data  I'm
working with, and I have numerous look-up routines that rely on
string comparison to optimise performance. I've introduced my own
sorting proc that seems to resolve the problem:

Private Sub MySort(rngRange As Range, intColSort As Integer)

Dim strFormula As String
Dim intColTemp As Integer
Dim blnScreenUpdating As Boolean

'Register current screen updating status
blnScreenUpdating = Application.ScreenUpdating

'Turn off screen updating
Application.ScreenUpdating = False

'Initialise temporary column
intColTemp = intColSort + 1
rngRange.Worksheet.Columns(intColTemp).Insert xlShiftToRight

'Apply formula to force upper case and substitute - with $
strFormula = "=UPPER(SUBSTITUTE(RC[-1], "
strFormula = strFormula & Chr(34) & "-" & Chr(34)
strFormula = strFormula & ", "
strFormula = strFormula & Chr(34) & "$" & Chr(34)
strFormula = strFormula & "))"
intColTemp).FormulaR1C1 = strFormula
intColTemp).AutoFill rngRange.Worksheet.Range(rngRange.Worksheet.Cells
(rngRange.Rows(1).Row, intColTemp), rngRange.Worksheet.Cells
(rngRange.Rows(rngRange.Rows.Count).Row, intColTemp))

'Perform sort using temporary column
rngRange.Sort rngRange.Worksheet.Cells(rngRange.Rows(1).Row,

'Delete temporary column
rngRange.Worksheet.Columns(intColTemp).Delete xlShiftToLeft

'Reset screen updating
Application.ScreenUpdating = blnScreenUpdating

End Sub

Answer #3    Answered By: Seth Anderson     Answered On: Dec 11

I imagine that the sorting  policy Excel uses does result in a more "natural"
feel to its sorting in general, but there are occasions like this when it
isn't useful. I've occasionally noticed this myself, although I don't
recall it ever being more than a curiosity in my case.

What should be there (hint to the developers) is a sort control that
optionally forces it back to a true ASCII sort.

Didn't find what you were looking for? Find more on Sorting problem Or get search suggestion and latest updates.