Sorting String Array with mixed Text and Values

[cadcoke4]

I have a sort function which will accept a 2-dimensional array, and sort them by any column. The array is currently purely a string array, so even the numbers are entered as strings.

The problem is that when I sort on a column that happens to have numbers in it, they are sorted according to alphabetical rules. Thus, the numbers 1,2,12,9 are sorted as 1,12,2,9 and not as 1,2,9,12 as it should.

I know VBA has functions to convert between strings and numbers, but the problem is that my sort function will not know what the column will contain. It might contain test or numbers. Since I am using AutoCAD, and reading the values of Block Attributes, they will always be read as strings. My sort function will need to determine which kind of data it is, or use some sort of comparison method that doesn't care.

I've tried dimensioning the array as a String and as a Variant, but without any difference.

Is there any function in VBA which can determine if a string value is actually a number?

Joe Dunfee

[JeanS]

There is the IsNumeric function. I'm not sure if I'm allowed to copy the VBA help from Excel into this forum, but if you go to a code window in Excel, type IsNumeric, select it then press F1 and you can see an example.

Hope this helps.

[cadcoke4]

Thank you very much for that info. This is exactly the function I needed, and is available in my VBA for Autocad.

I considered changing my routine that fills the array from the AutoCAD Block Attributes, so that it converts one of the attribute text values into the numeric value. But, then I would have to hard-code it to one specific attirbute.

You've demonstrated why a computer is a poor substitute for a human. I spent hours searching the VBA Help, and searching the internet to no avail.

Joe Dunfee

[Tommy]

There is also Val(String) this will return 0 if it is not a number and the number if it is a number. :)

[fixo]

Try this function



'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
' written by Fatty T.O.H. (c)2006 * all rights removed '
' SourceArr - two dimensional array '
' iPos - "column" number (starting from 1) '
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
Public Function ColSort(sourceArr As Variant, iPos As Integer) As Variant
Dim Check As Boolean
ReDim tmpArr(UBound(sourceArr, 2)) As Variant
Dim iCount As Integer
Dim jCount As Integer
Dim nCount As Integer
iPos = iPos - 1
Check = False
Do Until Check
Check = True
For iCount = LBound(sourceArr, 1) To UBound(sourceArr, 1) - 1
If sourceArr(iCount, iPos) > sourceArr(iCount + 1, iPos) Then
For jCount = LBound(sourceArr, 2) To UBound(sourceArr, 2)
tmpArr(jCount) = sourceArr(iCount, jCount)
sourceArr(iCount, jCount) = sourceArr(iCount + 1, jCount)
sourceArr(iCount + 1, jCount) = tmpArr(jCount)
Check = False
Next
End If
Next
Loop
ColSort = sourceArr
End Function


~'J'~

[cadcoke4]

Tommy, I started out using the Val function, but the issue with the Val funcion is that all my data is in text. Val ("0") and Val(0) with both return a numeric 0, so it can't truely discern if the column is text or not. A numeric value of zero would wrongly be assumed to be a text string.

But, the more I think about it, the more I realize that it may be possible to use Val, as long as you test both of the values. As long as they both evaluate as the same type of data, you can directly compare them. If they are different then they must both be compared as strings. But, the IsNumeric function is certainly a clearer way to do it.

Fatty, I don't see how your routine can discern between types of data. It just compares the fields as the data-type they are. So, a string containing "3" is considered larger than "12". Am I missing something?

Joe Dunfee

[Tommy]

It might contain test or numbers

If it is all numbers or all strings and not a mix, you should be on the right path. Otherwise you would need to test each character ...... but you know what you are dealing with.

Out of curiosity how do you sort the columns when the same column contains both numeric and alpha? Which comes first? or Does this even occur?

[cadcoke4]

In my case, there is never a mix of numbers and letters. So, it is not an issue. But, I've seen other posts on the Autodesk official newsgroup where they had to deal with it.

Do deal with sorting fields that have a mix of numbers and letters (like A-4) the only way seems to be to separate the numbers and letters into a temporary array, then sort that array, and finally re-combine the results.

However, it couldn't be a generic function because the user would need toknow how to separate them out, and which resulting columns in the temporary array to sort on. For example, what would you do with values like "A-4","4b", "4b3" and "A-4b"?

Thinking about that last array of text strings, perhaps it would be possible to sort out every individual character. Create a temporary 2d array, and make enough columns to accomodate each character. As you loop through each entry, If a character is a number, then look at the next character, and if it is also a number, then append it to the character you are looking at now.

Then, sort the temporary array alphabetically by the 1st column, then 2nd column, etc. But, at this point any sequence of entries that have numbers could be in the wrong order, but at least the digits are grooped together. So, you search for the first and last occurance of a number in each column, and just sort that range of entries by the value of the number. For each column that has multiple entires in the prior column, you then look at the next column and perform the same operation.

I doubt anyone can follow that... it was challenging for me to type it and keep the sequence in my head.

In the case of a mix of alpha/numeric characters in a field, I suspect it is most common to just do the comparison as text, and ignore the fact that "A-2" should be after "A-12".

Joe Dunfee

[Tommy]

I was just thinking along the lines of page numbers, room numbers, door schedules, where you would split based on a "-" lets say and sort for "A", "S", "E", then sort on the numbers, the end result being "A-1", "A-2" ...
I've had to sort entries like AZ1, AZ2,..... AZ34, or A1C1, A2C1R, A2C1L...A102C5. But since I made the piece marks I know what is coming and how to break it up to sort it. :yes

I had to ask ya know? :)