Sorting 3 columns in a 2 dimension array

[John]

Hello ...

I would like to sort a 3-column combo box list; ordered by Column1
then by Column2 then by Column3.

Have looked at Tom Oglviy's dialogue with Tim Zych (Nov 5, 2002), but
am not sure how to scale the VBA code to accomplish my task.

Would appreciate any help.

John

 

[Jim Cone]

John,
Just to make sure of the circumstances...
The sort should be accomplished before loading the combobox.
If you have data on a worksheet, then use the VBA Sort Method
to sort the data and then load the combobox.
If you have your data in an array and not on a worksheet then
an array sort routine would be applicable.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"John" <[email protected]>
wrote in message
Hello ...
I would like to sort a 3-column combo box list; ordered by Column1
then by Column2 then by Column3.
Have looked at Tom Oglviy's dialogue with Tim Zych (Nov 5, 2002), but
am not sure how to scale the VBA code to accomplish my task.
Would appreciate any help.
John

 

[John]

With a little help from the Internet world, I have a working solution.

For those interested, go to
http://www.excelwiki.com/VBA/Arrays-BubbleSort for the following
snippet:

SortColumn1=0
SortColumn2=3 '<--changed to 1
SortColumn3=5 '<--changed to 2
For i = LBound(ArrayName, 1) To UBound(ArrayName, 1) - 1
For j = LBound(ArrayName, 1) To UBound(ArrayName, 1) - 1
Condition1=ArrayName(j, SortColumn1) > ArrayName(j + 1,
SortColumn1)
Condition2=ArrayName(j, SortColumn1) = ArrayName(j + 1,
SortColumn1) and _
ArrayName(j, SortColumn2) > ArrayName(j + 1,
SortColumn2)
Condition3=ArrayName(j, SortColumn1) = ArrayName(j + 1,
SortColumn1) and _
ArrayName(j, SortColumn2) = ArrayName(j + 1,
SortColumn2) and _
ArrayName(j, SortColumn3) > ArrayName(j + 1,
SortColumn3)

If Condition1 or Condition2 or Condition3 Then
For y = LBound(ArrayName, 2) To UBound(ArrayName, 2)
t = ArrayName(j, y)
ArrayName(j, y) = ArrayName(j + 1, y)
ArrayName(j + 1, y) = t
Next y
End If
Next
Next

This seems to be both scalable and flexible.

Thx ... John

 

[Jim Cone]

John,
My modified version of the code below is much faster,
however, it is still very slow on large arrays.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


Function SortArrayLikeXlSheet(ByRef varArray As Variant, _
ByRef Col_1 As Long, ByRef Col_2 As Long, _
ByRef Col_3 As Long) As Variant
Dim blnCondition As Boolean
Dim i As Long
Dim j As Long
Dim Y As Long
Dim vTemp As Variant
Dim lngTop As Long
Dim lngBottom As Long
Dim lngLeft As Long
Dim lngRight As Long

lngTop = LBound(varArray, 1)
lngBottom = UBound(varArray, 1)
lngLeft = LBound(varArray, 2)
lngRight = UBound(varArray, 2)

For i = lngTop To lngBottom - 1
For j = i + 1 To lngBottom
Select Case True
Case varArray(i, Col_1) > varArray(j, Col_1)
blnCondition = True
Case (varArray(i, Col_1) = varArray(j, Col_1)) And _
(varArray(i, Col_2) > varArray(j, Col_2))
blnCondition = True
Case (varArray(i, Col_1) = varArray(j, Col_1)) And _
(varArray(i, Col_2) = varArray(j, Col_2)) And _
(varArray(i, Col_3) > varArray(j, Col_3))
blnCondition = True
Case Else
blnCondition = False
End Select
If blnCondition Then
For Y = lngLeft To lngRight
vTemp = varArray(i, Y)
varArray(i, Y) = varArray(j, Y)
varArray(j, Y) = vTemp
Next 'y
End If
Next 'j
Next 'i
SortArrayLikeXlSheet = varArray
End Function

Sub PutArrayInOrder()
Dim vArry As Variant
vArry = Selection.Value
'Specify order in which columns to be sorted.
Call SortArrayLikeXlSheet(vArry, 2, 3, 1)
Selection.Value = vArry
End Sub
'-----------


"John" <[email protected]>
wrote in message
With a little help from the Internet world, I have a working solution.

For those interested, go to
http://www.excelwiki.com/VBA/Arrays-BubbleSort for the following
snippet:

SortColumn1=0
SortColumn2=3 '<--changed to 1
SortColumn3=5 '<--changed to 2
For i = LBound(ArrayName, 1) To UBound(ArrayName, 1) - 1
For j = LBound(ArrayName, 1) To UBound(ArrayName, 1) - 1
Condition1=ArrayName(j, SortColumn1) > ArrayName(j + 1,
SortColumn1)
Condition2=ArrayName(j, SortColumn1) = ArrayName(j + 1,
SortColumn1) and _
ArrayName(j, SortColumn2) > ArrayName(j + 1,
SortColumn2)
Condition3=ArrayName(j, SortColumn1) = ArrayName(j + 1,
SortColumn1) and _
ArrayName(j, SortColumn2) = ArrayName(j + 1,
SortColumn2) and _
ArrayName(j, SortColumn3) > ArrayName(j + 1,
SortColumn3)

If Condition1 or Condition2 or Condition3 Then
For y = LBound(ArrayName, 2) To UBound(ArrayName, 2)
t = ArrayName(j, y)
ArrayName(j, y) = ArrayName(j + 1, y)
ArrayName(j + 1, y) = t
Next y
End If
Next
Next

This seems to be both scalable and flexible.
Thx ... John