Problems with Bubble Sort on 2D Array

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

Try comparing the column elements as numbers:

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


"ExcelMonkey" wrote:

> 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

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" wrote:

> Try comparing the column elements as numbers:
>
> If Val(PassedArray(i, col)) > Val(PassedArray(j, col)) Then
>
>
> "ExcelMonkey" wrote:
>
> > 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

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


"ExcelMonkey" wrote:

> 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" wrote:
>
> > Try comparing the column elements as numbers:
> >
> > If Val(PassedArray(i, col)) > Val(PassedArray(j, col)) Then
> >
> >
> > "ExcelMonkey" wrote:
> >
> > > 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

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

--
Regards,
Tom Ogilvy



"ExcelMonkey" <[email protected]> wrote in message
news:[email protected]...
> 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" wrote:
>
> > Try comparing the column elements as numbers:
> >
> > If Val(PassedArray(i, col)) > Val(PassedArray(j, col)) Then
> >
> >
> > "ExcelMonkey" wrote:
> >
> > > 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

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

"Tom Ogilvy" wrote:

> 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
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "ExcelMonkey" <[email protected]> wrote in message
> news:[email protected]...
> > 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" wrote:
> >
> > > Try comparing the column elements as numbers:
> > >
> > > If Val(PassedArray(i, col)) > Val(PassedArray(j, col)) Then
> > >
> > >
> > > "ExcelMonkey" wrote:
> > >
> > > > 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
>
>
>

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

"ExcelMonkey" wrote:

> 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
>
> "Tom Ogilvy" wrote:
>
> > 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
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> >
> > "ExcelMonkey" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" wrote:
> > >
> > > > Try comparing the column elements as numbers:
> > > >
> > > > If Val(PassedArray(i, col)) > Val(PassedArray(j, col)) Then
> > > >
> > > >
> > > > "ExcelMonkey" wrote:
> > > >
> > > > > 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
> >
> >
> >

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

On Mon, 20 Feb 2006 06:01:31 -0800, ExcelMonkey wrote:

> 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

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" wrote:

> 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
>
> "Tom Ogilvy" wrote:
>
> > 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
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> >
> > "ExcelMonkey" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" wrote:
> > >
> > > > Try comparing the column elements as numbers:
> > > >
> > > > If Val(PassedArray(i, col)) > Val(PassedArray(j, col)) Then
> > > >
> > > >
> > > > "ExcelMonkey" wrote:
> > > >
> > > > > 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
> >
> >
> >

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

"ExcelMonkey" wrote:

> 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
>
> "Tom Ogilvy" wrote:
>
> > 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
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> >
> > "ExcelMonkey" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" wrote:
> > >
> > > > Try comparing the column elements as numbers:
> > > >
> > > > If Val(PassedArray(i, col)) > Val(PassedArray(j, col)) Then
> > > >
> > > >
> > > > "ExcelMonkey" wrote:
> > > >
> > > > > 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
> >
> >
> >

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


"ExcelMonkey" wrote:

> 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
>
> "Tom Ogilvy" wrote:
>
> > 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
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> >
> > "ExcelMonkey" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" wrote:
> > >
> > > > Try comparing the column elements as numbers:
> > > >
> > > > If Val(PassedArray(i, col)) > Val(PassedArray(j, col)) Then
> > > >
> > > >
> > > > "ExcelMonkey" wrote:
> > > >
> > > > > 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
> >
> >
> >

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




"ExcelMonkey" wrote:

> 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
>
> "ExcelMonkey" wrote:
>
> > 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
> >
> > "Tom Ogilvy" wrote:
> >
> > > 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
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > >
> > > "ExcelMonkey" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" wrote:
> > > >
> > > > > Try comparing the column elements as numbers:
> > > > >
> > > > > If Val(PassedArray(i, col)) > Val(PassedArray(j, col)) Then
> > > > >
> > > > >
> > > > > "ExcelMonkey" wrote:
> > > > >
> > > > > > 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
> > >
> > >
> > >