Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
sort multi-dimensional array on numeric data?
How do I sort a multi-dimensional array on numeric data?
Say I have a 0-based array of 30 rows and 5 columns and want to sort on the first column which contains integer values. I could dump the data in a sheet, do a sort and transfer back to the array, but there should be a nicer way. This is a quicksort I found on Stephen Bullen's site, but I just cannot to get it to sort on numeric data. Sub procSort2D(avArray, sOrder As String, iKey As Integer, iLow1 As Integer, iHigh1 As Integer) On Error Resume Next 'Dimension variables Dim iLow2 As Integer Dim iHigh2 As Integer Dim i As Integer Dim vItem1 As Variant Dim vItem2 As Variant 'Set new extremes to old extremes iLow2 = iLow1 iHigh2 = iHigh1 'Get value of array item in middle of new extremes vItem1 = avArray((iLow1 + iHigh1) \ 2, iKey) 'Loop for all the items in the array between the extremes While iLow2 < iHigh2 If sOrder = "A" Then 'Find the first item that is greater than the mid-point item While avArray(iLow2, iKey) < vItem1 And iLow2 < iHigh1 iLow2 = iLow2 + 1 Wend 'Find the last item that is less than the mid-point item While avArray(iHigh2, iKey) vItem1 And iHigh2 iLow1 iHigh2 = iHigh2 - 1 Wend Else 'Find the first item that is less than the mid-point item While avArray(iLow2, iKey) vItem1 And iLow2 < iHigh1 iLow2 = iLow2 + 1 Wend 'Find the last item that is greater than the mid-point item While avArray(iHigh2, iKey) < vItem1 And iHigh2 iLow1 iHigh2 = iHigh2 - 1 Wend End If 'If the two items are in the wrong order, swap the rows If iLow2 < iHigh2 Then For i = 1 To UBound(avArray, 2) vItem2 = avArray(iLow2, i) avArray(iLow2, i) = avArray(iHigh2, i) avArray(iHigh2, i) = vItem2 Next End If 'If the pointers are not together, advance to the next item If iLow2 <= iHigh2 Then iLow2 = iLow2 + 1 iHigh2 = iHigh2 - 1 End If Wend 'Recurse to sort the lower half of the extremes If iHigh2 iLow1 Then procSort2D avArray, sOrder, iKey, iLow1, iHigh2 'Recurse to sort the upper half of the extremes If iLow2 < iHigh1 Then procSort2D avArray, sOrder, iKey, iLow2, iHigh1 End Sub Thanks for any advice. RBS |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multi Dimensional Lookup | Excel Discussion (Misc queries) | |||
Conditional IF formula using multi-dimensional arrays | Excel Worksheet Functions | |||
newbie question on multi-dimensional array | New Users to Excel | |||
How to generate formula from two dimensional array of data | Excel Worksheet Functions | |||
Multi-dimensional VLOOKUP / PivotTable ? | Excel Discussion (Misc queries) |