Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,452
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Multi Dimensional Lookup coolkat Excel Discussion (Misc queries) 1 June 14th 11 11:48 PM
Conditional IF formula using multi-dimensional arrays iperlovsky Excel Worksheet Functions 2 April 13th 10 05:15 PM
newbie question on multi-dimensional array sammus New Users to Excel 2 April 3rd 06 03:11 AM
How to generate formula from two dimensional array of data KROATA Excel Worksheet Functions 1 December 2nd 05 01:39 PM
Multi-dimensional VLOOKUP / PivotTable ? carlyman Excel Discussion (Misc queries) 6 June 14th 05 12:35 AM


All times are GMT +1. The time now is 03:04 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"