Consulting

Results 1 to 9 of 9

Thread: Sorting String Array with mixed Text and Values

  1. #1
    VBAX Newbie
    Joined
    Aug 2007
    Location
    Near Lancaster, PA
    Posts
    4
    Location

    Sorting String Array with mixed Text and Values

    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

  2. #2
    VBAX Newbie
    Joined
    Aug 2007
    Posts
    5
    Location
    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.

  3. #3
    VBAX Newbie
    Joined
    Aug 2007
    Location
    Near Lancaster, PA
    Posts
    4
    Location
    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

  4. #4
    VBAX Master Tommy's Avatar
    Joined
    May 2004
    Location
    Houston, TX
    Posts
    1,168
    Location
    There is also Val(String) this will return 0 if it is not a number and the number if it is a number.

  5. #5
    VBAX Regular fixo's Avatar
    Joined
    Jul 2006
    Location
    Sankt-Petersburg
    Posts
    99
    Location
    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'~

  6. #6
    VBAX Newbie
    Joined
    Aug 2007
    Location
    Near Lancaster, PA
    Posts
    4
    Location
    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
    Last edited by cadcoke4; 08-31-2007 at 05:19 AM.

  7. #7
    VBAX Master Tommy's Avatar
    Joined
    May 2004
    Location
    Houston, TX
    Posts
    1,168
    Location
    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?

  8. #8
    VBAX Newbie
    Joined
    Aug 2007
    Location
    Near Lancaster, PA
    Posts
    4
    Location
    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

  9. #9
    VBAX Master Tommy's Avatar
    Joined
    May 2004
    Location
    Houston, TX
    Posts
    1,168
    Location
    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.

    I had to ask ya know?

Posting Permissions

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