Sorting 2D-array

[Cainnech]

Hello,

I'm trying to get my 2D-array sorted but for some reason I can't seem to get it sorted in the right order. Anybody that can help me find the solution?

I have an array which has been constructed as follows

array (Name, Number1, Number2, Total, Previous Position, New Position)

I'm using the last two columns as a way to define the order.
The value that needs to be sorted is the 4th column (Total).
But instead of actually changing the indexes, I want to change the value of the column New Position.

So to give you an example:

BEFORE SORT

person(0) ("NAME1", 4, 51, 55,1,1)
person(1) ("NAME2", 12, 41, 53,2,2)
person(2) ("NAME3", 24, 35, 59,3,3)
person(3) ("NAME4", 1, 11, 12,4,4)
person(4) ("NAME5", 0, 22, 22,5,5)

AFTER SORT

person(0) ("NAME1", 4, 51, 55,1,2)
person(1) ("NAME2", 12, 41, 53,2,3)
person(2) ("NAME3", 24, 35, 59,3,1)
person(3) ("NAME4", 1, 11, 12,4,5)
person(4) ("NAME5", 0, 22, 22,5,4)

As you can see, only the value in the last column has changed depending on the value of the 4th column.

I'm using this last column to define the order in which I'm displaying this array. But I need to be able to keep track of the original position (the second to last column).

Right now I'm using the following code:

Dim Pos1 As Integer
Dim Pos2 As Integer
    
    For i = 0 To (UBound(Person) - 1)
        
        'Previous Positions
        Person(i, 4) = Person(i, 5)
        
        For j = i To UBound(Person)
            If CInt(Person(i, 3)) < CInt(Person(j, 3)) Then
                Pos1 = CInt(Person(i, 5))
                Pos2 = CInt(Person(j, 5))
                
                
                ' New Positions
                Person(i, 5) = Pos2
                Person(j, 5) = Pos1
                
            End If
        Next
    Next

Thanks for the help!

[Rabbit]

What are you getting instead of what you want?

[Cainnech]

Right now I'm getting something like this:

person(0) ("NAME1", 4, 51, 55,1,5)
person(1) ("NAME2", 12, 41, 53,2,3)
person(2) ("NAME3", 24, 35, 59,3,1)
person(3) ("NAME4", 1, 11, 12,4,2)
person(4) ("NAME5", 0, 22, 22,5,4)

It's not being sorted well.

[Rabbit]

1) You should set the previous positions for all elements before doing any sorting. Otherwise, it won't come out correct.

2) You are swapping the positions if one value is larger than the other. But you only swap if the position is also larger. You can't swap them everytime otherwise you might swap them into the wrong position.

[Cainnech]

Hi Rabbit,

Before I start sorting I already syncronised the old positions with the new ones.

I've tried your suggestion and added a condition where the compared position must be smaller before I actually swap the positions but still I don't get the right order.

If I try to sort like this using controls, it works like a charm. But I can't seem to make the switch to do the same in an array.

[Rabbit]

1) Actually, if you look in your code above, you're not synchronizing everything before doing the sorting. It's synchronizing in between each element.

2) It would help to see your newly modified code in addition to the new results.

[Cainnech]

Trust me, I'm syncronizing the values :-) The sample-code was indeed wrong, actually in the actual code I'm syncronizing before I start the sort.

But in order for you to check the code I've simplified it to this code:

Dim Persons(4, 3) As String


Private Sub cmdDebug_Click()
    For i = 0 To UBound(Persons)
        Debug.Print Persons(i, 0) & " | " & Persons(i, 1) & " | " & Persons(i, 2) & " | " & Persons(i, 3)
    Next
    
    Debug.Print "-------------------------------------------------------"
End Sub

Private Sub cmdSort_Click()
    SortPersons
End Sub

Private Sub Form_Load()

    ResetPersons

End Sub

Public Sub ResetPersons()
    Persons(0, 0) = "NAME1"
    Persons(0, 1) = 1
    Persons(0, 2) = 1
    Persons(0, 3) = 20

    Persons(1, 0) = "NAME2"
    Persons(1, 1) = 2
    Persons(1, 2) = 2
    Persons(1, 3) = 50

    Persons(2, 0) = "NAME3"
    Persons(2, 1) = 3
    Persons(2, 2) = 3
    Persons(2, 3) = 40

    Persons(3, 0) = "NAME4"
    Persons(3, 1) = 4
    Persons(3, 2) = 4
    Persons(3, 3) = 30

    Persons(4, 0) = "NAME5"
    Persons(4, 1) = 5
    Persons(4, 2) = 5
    Persons(4, 3) = 10

End Sub

Public Sub SortPersons()
Dim TempPos As Integer

    For i = 0 To (UBound(Persons) - 1)
        For n = i To UBound(Persons)
            If Persons(i, 3) > Persons(n, 3) And Persons(i, 2) < Persons(n, 2) Then
                TempPos = Persons(i, 2)
                Persons(i, 2) = Persons(n, 2)
                Persons(n, 2) = TempPos
            End If
        Next
    Next
End Sub

[Rabbit]

On line 53, you need to go all the way to the end of the array, not array -1.

On line 54, you need to start from index 0, not index i.

If you want a descending sort, you need to flip the comparison on your current position.

[Cainnech]

Thanks Rabbit,

After your remarks, it seems to be working right now. Although I don't understand completely why... :-)

But the important thing is that it works!
Thanks Rabbit!

[Cainnech]

It seems I was a bit premature with the solution because I'm still having issues.

When using this code:

Dim Persons(9, 6) As String


Private Sub cmdDebug_Click()
    For i = 0 To UBound(Persons)
        Debug.Print Persons(i, 0) & " | " & Persons(i, 1) & " | " & Persons(i, 2) & " | " & Persons(i, 3) & " | " & Persons(i, 4) & " | " & Persons(i, 5) & " | " & Persons(i, 6)
    Next
    
    Debug.Print "-------------------------------------------------------"
End Sub

Private Sub cmdSort_Click()
    SortPersons
End Sub

Private Sub Form_Load()

    ResetPersons

End Sub

Public Sub ResetPersons()
    Persons(0, 0) = "NAME1"
    Persons(0, 1) = 0
    Persons(0, 2) = 0
    Persons(0, 3) = 0
    Persons(0, 4) = 0
    Persons(0, 5) = 5
    Persons(0, 6) = 5

    Persons(1, 0) = "NAME2"
    Persons(1, 1) = 0
    Persons(1, 2) = 0
    Persons(1, 3) = 0
    Persons(1, 4) = 0
    Persons(1, 5) = 2
    Persons(1, 6) = 2

    Persons(2, 0) = "NAME3"
    Persons(2, 1) = 25
    Persons(2, 2) = 0
    Persons(2, 3) = 0
    Persons(2, 4) = 25
    Persons(2, 5) = 4
    Persons(2, 6) = 4

    Persons(3, 0) = "NAME4"
    Persons(3, 1) = 0
    Persons(3, 2) = 0
    Persons(3, 3) = 0
    Persons(3, 4) = 0
    Persons(3, 5) = 1
    Persons(3, 6) = 1

    Persons(4, 0) = "NAME5"
    Persons(4, 1) = 0
    Persons(4, 2) = 0
    Persons(4, 3) = 0
    Persons(4, 4) = 0
    Persons(4, 5) = 10
    Persons(4, 6) = 10

    Persons(5, 0) = "NAME6"
    Persons(5, 1) = 0
    Persons(5, 2) = 0
    Persons(5, 3) = 0
    Persons(5, 4) = 0
    Persons(5, 5) = 6
    Persons(5, 6) = 6

    Persons(6, 0) = "NAME7"
    Persons(6, 1) = 0
    Persons(6, 2) = 0
    Persons(6, 3) = 0
    Persons(6, 4) = 0
    Persons(6, 5) = 8
    Persons(6, 6) = 8

    Persons(7, 0) = "NAME8"
    Persons(7, 1) = 32
    Persons(7, 2) = 0
    Persons(7, 3) = 0
    Persons(7, 4) = 32
    Persons(7, 5) = 9
    Persons(7, 6) = 9

    Persons(8, 0) = "NAME9"
    Persons(8, 1) = 20
    Persons(8, 2) = 0
    Persons(8, 3) = 0
    Persons(8, 4) = 20
    Persons(8, 5) = 7
    Persons(8, 6) = 7

    Persons(9, 0) = "NAME10"
    Persons(9, 1) = 0
    Persons(9, 2) = 0
    Persons(9, 3) = 0
    Persons(9, 4) = 0
    Persons(9, 5) = 3
    Persons(9, 6) = 3

End Sub

Public Sub SortPersons()
Dim TempPos As Integer

    For i = 0 To UBound(Persons)
        For n = 0 To UBound(Persons)
            If Persons(i, 4) > Persons(n, 4) And CInt(Persons(i, 6)) > CInt(Persons(n, 6)) Then
                TempPos = Persons(i, 6)
                Persons(i, 6) = Persons(n, 6)
                Persons(n, 6) = TempPos
            End If
        Next
    Next
End Sub

The sort isn't being carried correctly.

The result I'm getting is:

NAME1 | 0 | 0 | 0 | 0 | 5 | 9
NAME2 | 0 | 0 | 0 | 0 | 2 | 7
NAME3 | 25 | 0 | 0 | 25 | 4 | 4
NAME4 | 0 | 0 | 0 | 0 | 1 | 5
NAME5 | 0 | 0 | 0 | 0 | 10 | 10
NAME6 | 0 | 0 | 0 | 0 | 6 | 6
NAME7 | 0 | 0 | 0 | 0 | 8 | 8
NAME8 | 32 | 0 | 0 | 32 | 9 | 1
NAME9 | 20 | 0 | 0 | 20 | 7 | 2
NAME10 | 0 | 0 | 0 | 0 | 3 | 3

Name10 is being considered as the 3rd highest number while it should actually be Name3.

Any suggestions?

[Rabbit]

I was under the impression that the starting positions matched the index. I got this impression from your sample data in posts #1, #3, #7. The algorithm I gave you only works using that assumption.

If, however, that is not the case, then you can easily make the data conform by setting the positions to the index before doing the sort.

[Cainnech]

Unfortunately, I can't change my index as I need to keep my Persons-array in the same order. The only way I can see that happening is if I add yet another dimension to the array which has the order in which it should appear.

In effect I don't think it will be possible to sort this array directly from itself. The only solution that I see now is to use a control that does the sorting for me.

[Rabbit]

I didn't say you need to change the index. Just change the position to the index after setting the previous position and before the sort.