Results 1 to 13 of 13
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Simple sort (index) (ExcelXP)

    I have an array in Excel (about 250 text strings). In the second dimension I want the sort order, strings shouldn't be moved themselves.

    Example with three strings:

    Dim Ar(3,2)
    Ar(x,2) irrelevant
    Ar(1,1) = "Ron"
    Ar(2,1) = "Pete"
    Ar(3,1) = "Jim"

    Call Sort

    Result:

    Ar(x,1) unchanged

    Ar(1,2)=3
    Ar(2,2)=2
    Ar(3,2)=1


    I know that using an Excel range as intermediate with clever use or the LARGE and LOOKUP or INDEX functions might crack this all at once; however I'd like to do this in VBA.

    Of course I can try to write some code myself, but hey... why re-invent wheels???

    Any code snippets would be appreciated...

    Erik Jan

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Simple sort (index) (ExcelXP)

    Here is a simple bubble sort. Very slow for large arrays, but should be OK for arrays of 250 strings.

    Sub SortArray(Ar())
    Dim i As Integer
    Dim j As Integer
    Dim intTemp As Integer

    For i = 1 To UBound(Ar, 1)
    Ar(i, 2) = i
    Next i

    For i = 1 To UBound(Ar, 1) - 1
    For j = i + 1 To UBound(Ar, 1)
    If Ar(Ar(i, 2), 1) > Ar(Ar(j, 2), 1) Then
    intTemp = Ar(i, 2)
    Ar(i, 2) = Ar(j, 2)
    Ar(j, 2) = intTemp
    End If
    Next j
    Next i
    End Sub

    To test it:

    Sub Test()
    Dim Ar(1 To 3, 1 To 2)
    Ar(1, 1) = "Ron"
    Ar(2, 1) = "Pete"
    Ar(3, 1) = "Jim"
    SortArray Ar
    Dim i As Integer
    For i = 1 To 3
    Debug.Print Ar(i, 2)
    Next i
    End Sub

  3. #3
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simple sort (index) (ExcelXP)

    <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

    Thanks

    Erik Jan

  4. #4
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simple sort (index) (ExcelXP)

    Actually... after some tests last night I found out that your code does NOT work (it certainly does for the added example case, but try to add a few more names and see what happens...).

    After I had switched my brain back on <img src=/S/newbrain.gif border=0 alt=newbrain width=21 height=22> I found out what the problem was. As far as I can see, you have taken the standard bubble sort and tweaked that to NOT replace the items but only adjust the indices (which was -and still is- indeed what I was looking for).
    However, swapping the indices as the bubble does is in this case not impacting the surrounding loops and that's what the bubble is all about (the 'bubbling' moves a smaller element upward) now each element remains in-place.

    Don't step into the same pitfalls as I did yesterday, chaning to either:

    <font color=red>If Ar(i, 1) > Ar(j, 1) Then</font color=red>
    intTemp = Ar(i, 2)
    Ar(i, 2) = Ar(j, 2)
    Ar(j, 2) = intTemp
    End If

    or
    If Ar(Ar(i, 2), 1) > Ar(Ar(j, 2), 1) Then
    <font color=red>intTemp = Ar(Ar(i,2), 2)
    Ar(Ar(i,2), 2) = Ar(Ar(j,2), 2)
    Ar(Ar(j,2), 2) = intTemp</font color=red>
    End If

    As these are -for similar reasons- also wrong (also here... test with a larger set to prevent accidental success...).

    Any suggestions... as I can see it now, I can clone the array, then do a normal Bubble-sort on the clone, followed by a loop where I loop-up each sorted element in the original array and add the sorted index then.

    Erik Jan <img src=/S/blackteeth.gif border=0 alt=blackteeth width=20 height=20>

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Simple sort (index) (ExcelXP)

    I don't understand. With exactly the same code as I posted, this test:

    Sub Test()
    Dim Ar(1 To 12, 1 To 2)
    Ar(1, 1) = "Ron"
    Ar(2, 1) = "Pete"
    Ar(3, 1) = "Jim"
    Ar(4, 1) = "Chris"
    Ar(5, 1) = "Adam"
    Ar(6, 1) = "James"
    Ar(7, 1) = "Dave"
    Ar(8, 1) = "Tim"
    Ar(9, 1) = "Bob"
    Ar(10, 1) = "Mary"
    Ar(11, 1) = "Anne"
    Ar(12, 1) = "Cynthia"
    SortArray Ar
    Dim i As Integer
    For i = 1 To 12
    Debug.Print Ar(i, 2), Ar(Ar(i, 2), 1)
    Next i
    End Sub

    results in

    <table border=1><td align=right>5</td><td>Adam</td><td align=right>11</td><td>Anne</td><td align=right>9</td><td>Bob</td><td align=right>4</td><td>Chris</td><td align=right>12</td><td>Cynthia</td><td align=right>7</td><td>Dave</td><td align=right>6</td><td>James</td><td align=right>3</td><td>Jim</td><td align=right>10</td><td>Mary</td><td align=right>2</td><td>Pete</td><td align=right>1</td><td>Ron</td><td align=right>8</td><td>Tim</td></table>
    That looks correct to me.,,

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simple sort (index) (ExcelXP)

    Maybe like this:

    <pre>Sub SortSecond()
    Dim oSh As Worksheet
    Dim vTemp As Variant
    Dim lRows As Long
    Dim lColumns As Long

    vTemp = ThisWorkbook.Worksheets(1).UsedRange.Value
    lRows = UBound(vTemp)
    lColumns = 2
    Set oSh = ThisWorkbook.Worksheets.Add
    With oSh
    .Range(.Cells(1, 1), .Cells(lRows, lColumns)) = vTemp
    .Range(.Cells(1, lColumns), .Cells(lRows, lColumns)).Sort _
    key1:=.Cells(1, lColumns), Order1:=xlAscending, Header:=xlNo
    vTemp = .Range(.Cells(1, 1), .Cells(lRows, lColumns))
    Application.DisplayAlerts=False
    .Delete
    Application.DisplayAlerts=True
    End With
    Set oSh=Nothing
    End Sub
    </pre>

    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simple sort (index) (ExcelXP)

    OK... now I see where the disconnect is...

    What I want is that the individual names are not moved, hence the output I'm looking for is:

    11 Ron
    10 Pete
    8 Jim
    4 Chris
    1 Adam
    7 James
    6 Dave
    12 Tim
    3 Bob
    9 Mary
    2 Anne
    5 Cynthia

    (the indices give the sorted order)

    Erik Jan

    By the way, you did change your output: it was

    For i = 1 To 3
    Debug.Print Ar(i, 2)
    Next i

  8. #8
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simple sort (index) (ExcelXP)

    Yep... that's what I though of initially as well (see my original post), I know this works. I was trying however to pull this off without the need of creating new WB's and/or using the "EXCEL-side" of things.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Simple sort (index) (ExcelXP)

    What I meant is that I used the same SortArray procedure, without any modification. The Test procedure was obviously different.

    The SortArray procedure does NOT move the strings around, it moves the indices around. Ar(1, 2) = 5 in my example means that the first name after sorting can be found in the 5th row of the array.

    What you want is to look at it from the other side: where in the sort order does the first name belong. That is problematic if there are duplicate names - the ranking number will be ambiguous.

  10. #10
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simple sort (index) (ExcelXP)

    OK, I see your pointy now... I even believe this might be part of a key to help me out (somehow).

    Thanks! (by the way: I'm not having any duplicates in my set)

  11. #11
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simple sort (index) (ExcelXP)

    I think I read somewhere, that sometimes using the Excel side can even speed up things compared to programming the whole thing into VBA. I'll leavit to you to test <g>
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Simple sort (index) (ExcelXP)

    If there are no duplicates, this variation will result in the numbers you wanted in <post#=456006>post 456006</post#>:

    Sub SortArray(Ar())
    Dim i As Integer
    Dim j As Integer
    Dim intTemp As Integer

    For i = 1 To UBound(Ar, 1)
    intTemp = 0
    For j = 1 To UBound(Ar, 1)
    If Ar(i, 1) >= Ar(j, 1) Then
    intTemp = intTemp + 1
    End If
    Next j
    Ar(i, 2) = intTemp
    Next i
    End Sub

    (If there were duplicates, it would result in the duplicates getting the same index number)

  13. #13
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simple sort (index) (ExcelXP)

    OK... I don't want to respond too quickly this time but it seems that this is indeed doing the job.

    Busy times are coming but I'll let you know how this ended as soon as I can!

    Thanks again for the great help.. this is an absolutely wonderful forum <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

Posting Permissions

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