Problems with Bubble Sort on 2D Array

[ExcelMonkey]

I have a 2D array which I am attempting to sort. The print-out below shows
the data in its original form and then what it looks like after the sort. I
am trying to sort based on the 5th column. But as you can see, the sorted
range suffers from 2 errors. Firstly, the first item in column 5 is 22.
Secondly, the strings in the first column are in their original order.

Note that each unit is repeated twice in the array. Is the problem based on
the fact that I have repeated data? Or have I slipped a digit in my bubble
sort?

Unsorted Data
Unit 1 C E 241 22
Unit 2 D D 313 13
Unit 3 C B 207 31
Unit 1 C E 61 33
Unit 2 D D 87 5
Unit 3 C B 87 13
************************
Sorted Data
Unit 1 C E 241 22
Unit 2 D D 87 5
Unit 3 D D 313 13
Unit 1 C B 87 13
Unit 2 C B 207 31
Unit 3 C E 61 33

The array with the data is called "HourData". The code I use to sort the
array is as follows:

BubbleSort2D HourData, 4 '5th column = 4th column in 0 based array

For Counter = 1 To TotalUnitBids
Debug.Print HourData(Counter - 1, 0) & " " & HourData(Counter - 1,
1) & " " & HourData(Counter - 1, 2) & " " & HourData(Counter - 1,
3) & " " & HourData(Counter - 1, 4)
Next
********************************************************
Function BubbleSort2D(PassedArray As Variant, col As Long)
' Sorts an array using bubble sort algorithm in descending order using
'column as sort criteria
Dim First As Integer, Last As Integer
Dim i As Integer, j As Integer, k As Integer
Dim Temp As Variant

First = LBound(PassedArray, 1)
Last = UBound(PassedArray, 1)
For i = 1 To Last - 1
For j = i + 1 To Last
If PassedArray(i, col) > PassedArray(j, col) Then
For k = 1 To UBound(PassedArray, 2)
Temp = PassedArray(j, k)
PassedArray(j, k) = PassedArray(i, k)
PassedArray(i, k) = Temp
Next k
End If
Next j
Next i
End Function


Thanks

EM

 

[Charlie]

Try comparing the column elements as numbers:

If Val(PassedArray(i, col)) > Val(PassedArray(j, col)) Then

 

[ExcelMonkey]

No that did not work:

Unit 1 C E 241 22
Unit 2 D D 313 13
Unit 3 C B 207 31
Unit 1 C E 61 33
Unit 2 D D 87 5
Unit 3 C B 87 13
***************************************************
Unit 1 C E 241 22
Unit 2 D D 87 5
Unit 3 D D 313 13
Unit 1 C B 87 13
Unit 2 C B 207 31
Unit 3 C E 61 33

 

[Charlie]

Oh, sorry I didn't pay attention to the zero-based arry part. Maybe it is
this:

For k = 1 To UBound(PassedArray, 2) <--one-based??

I tested you sort just fine.

P.S. (this statement may get some flack, but...)

I NEVER use zero-based arrays (unless required by a function that requires
it). Too much room for error.

Use:
Option Base 1

 

[Tom Ogilvy]

Your code assumes the lower bound of the array is 1. It probably isn't 1,
but zero, so you skip you first column when you do your swap.

You also skip your first row when sorting if the lower bound is 0

 

[ExcelMonkey]

Sorry Tom, can you elaborate where this happening. You are a correct in
stating that my arrays lower bounds is not 1. The following creates 0 and 5:

First = LBound(PassedArray, 1)
Last = UBound(PassedArray, 1)

Is it the lines:

For i = 1 To Last - 1
For j = i + 1 To Last

Should these be:

For i = 0 To Last
For j = i To Last

Thanks

EM

 

[ExcelMonkey]

Sorry. I changed:

For i = 1 To Last - 1
For j = i + 1 To Last

to:

For i = First To Last - 1
For j = i + 1 To Last

 

[Bill Martin]

What you're doing is not quite a bubble sort. With a bubble sort you
always compare adjacent elements and you need to keep rolling through the
data repeatedly (while the small numbers "bubble" to the top) until it's in
proper order. You can use a While/Wend loop like the following to cut
short the sort once it's found to be in order and speed it up a tad:

Dim Swapped as Boolean
Swapped = true
while Swapped
Swapped = false
for I = 1 to Last-1
If PassedArray(i, col) > PassedArray(i+1, col) Then
Swapped = true
For k = 1 To UBound(PassedArray, 2)
Temp = PassedArray(i+1, k)
PassedArray(i+1, k) = PassedArray(i, k)
PassedArray(i, k) = Temp
next k
End If
next I
wend


Good luck.

Bill
---------------------

 

[ExcelMonkey]

When changing:

For i = 1 To Last - 1
For j = i + 1 To Last

to:

For i = First To Last - 1
For j = i + 1 To Last

I get the sorted array below. You can see that its sorted correctly in
column 5. But the first column entries are not correct. The first entry for
the sorted array is:

Unit 1 D D 87 5

Yet in the unsorted array this is not Unit 1 but Unit 2. I am still missing
something here.


Unit 1 C E 241 22
Unit 2 D D 313 13
Unit 3 C B 207 31
Unit 1 C E 61 33
Unit 2 D D 87 5
Unit 3 C B 87 13
***************************************************
Unit 1 D D 87 5
Unit 2 D D 313 13
Unit 3 C B 87 13
Unit 1 C E 241 22
Unit 2 C B 207 31
Unit 3 C E 61 33

 

[ExcelMonkey]

Got it. Sorry for the obvious. Revised Function is below:

Function BubbleSort2D(PassedArray As Variant, col As Long)
' Sorts an array using bubble sort algorithm in descending order using
'column as sort criteria
Dim First As Integer, Last As Integer
Dim i As Integer, j As Integer, k As Integer
Dim Temp As Variant

First = LBound(PassedArray, 1)
Last = UBound(PassedArray, 1)
For i = First To Last - 1
For j = i + 1 To Last
If PassedArray(i, col) > PassedArray(j, col) Then
For k = First To UBound(PassedArray, 2)
Temp = PassedArray(j, k)
PassedArray(j, k) = PassedArray(i, k)
PassedArray(i, k) = Temp
Next k
End If
Next j
Next i
End Function

 

[Charlie]

Function BubbleSort2D(PassedArray As Variant, col As Long)
'
Dim First As Integer, Last As Integer
Dim i As Integer, j As Integer, k As Integer
Dim Temp As Variant

First = LBound(PassedArray, 1)
Last = UBound(PassedArray, 1)
For i = First To Last - 1
For j = i + 1 To Last
If Val(PassedArray(i, col)) > Val(PassedArray(j, col)) Then
For k = LBound(PassedArray, 2) To UBound(PassedArray, 2)
Temp = PassedArray(j, k)
PassedArray(j, k) = PassedArray(i, k)
PassedArray(i, k) = Temp
Next k
End If
Next j
Next i
'
End Function

 

[Charlie]

The line:

For k = First To UBound(PassedArray, 2)

works but is coincidental. You really mean:

For k = LBound(PassedArray, 2) To UBound(PassedArray, 2)

....and, I believe you still need Val()

If Val(PassedArray(i, col)) > Val(PassedArray(j, col)) Then