# Thread: Simple sort (index) (ExcelXP)

1. ## 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. ## 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. ## Re: Simple sort (index) (ExcelXP)

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

Thanks

Erik Jan

4. ## 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. ## 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(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. ## 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
With oSh
.Range(.Cells(1, 1), .Cells(lRows, lColumns)) = vTemp
.Range(.Cells(1, lColumns), .Cells(lRows, lColumns)).Sort _
vTemp = .Range(.Cells(1, 1), .Cells(lRows, lColumns))
.Delete
End With
Set oSh=Nothing
End Sub
</pre>

7. ## 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
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. ## 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. ## 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. ## 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. ## 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>

12. ## 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. ## 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
•