Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Quicksort for multi-columns
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 8
Default Quicksort for multi-columns
I want to sort a 2D-array. I have found Quicksort code but only for a
one-dimensional array. Do anyone know where to find a sort routine for
2D-arrays? I want a quick method. Not Bubble sort.
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,452
Default Quicksort for multi-columns
Try this one:

Function QuickSort2DArray(VarArray As Variant, _
lSortColumn As Long, _
Optional sOrder As String = "A", _
Optional lngFirst As Long = -1, _
Optional lngLast As Long = -1) As Variant

Dim lngLow As Long
Dim lngHigh As Long
Dim lngMiddle As Long
Dim varTempVal As Variant
Dim varTestVal As Variant

If lngFirst = -1 Then lngFirst = LBound(VarArray)
If lngLast = -1 Then lngLast = UBound(VarArray)

lngMiddle = (lngFirst + lngLast) / 2
varTestVal = VarArray(lngMiddle, lSortColumn)
lngLow = lngFirst
lngHigh = lngLast

Do
If sOrder = "A" Then
Do While VarArray(lngLow, lSortColumn) < varTestVal
lngLow = lngLow + 1
Loop
Do While VarArray(lngHigh, lSortColumn) varTestVal
lngHigh = lngHigh - 1
Loop
Else
Do While VarArray(lngLow, lSortColumn) varTestVal
lngLow = lngLow + 1
Loop
Do While VarArray(lngHigh, lSortColumn) < varTestVal
lngHigh = lngHigh - 1
Loop
End If

If (lngLow <= lngHigh) Then
varTempVal = VarArray(lngLow, lSortColumn)
VarArray(lngLow, 1) = VarArray(lngHigh, lSortColumn)
VarArray(lngHigh, 1) = varTempVal
lngLow = lngLow + 1
lngHigh = lngHigh - 1
End If
Loop While (lngLow <= lngHigh)

If lngFirst < lngHigh Then QuickSort2DArray VarArray, lSortColumn, sOrder,
lngFirst, lngHigh
If lngLow < lngLast Then QuickSort2DArray VarArray, lSortColumn, sOrder,
lngLow, lngLast

End Function


RBS


"Joakim Norrman" wrote in message
...
I want to sort a 2D-array. I have found Quicksort code but only for a
one-dimensional array. Do anyone know where to find a sort routine for
2D-arrays? I want a quick method. Not Bubble sort.

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,452
Default Quicksort for multi-columns
I had quickly edited a bit of code and forgot to alter something else.
This is how it should be:

Function QuickSort2DArray(VarArray As Variant, _
lSortColumn As Long, _
Optional sOrder As String = "A", _
Optional lngFirst As Long = -1, _
Optional lngLast As Long = -1) As Variant

Dim c As Long
Dim lngLow As Long
Dim lngHigh As Long
Dim lngMiddle As Long
Dim varTempVal As Variant
Dim varTestVal As Variant

If lngFirst = -1 Then lngFirst = LBound(VarArray)
If lngLast = -1 Then lngLast = UBound(VarArray)

lngMiddle = (lngFirst + lngLast) / 2
varTestVal = VarArray(lngMiddle, lSortColumn)
lngLow = lngFirst
lngHigh = lngLast

Do
If sOrder = "A" Then
Do While VarArray(lngLow, lSortColumn) < varTestVal
lngLow = lngLow + 1
Loop
Do While VarArray(lngHigh, lSortColumn) varTestVal
lngHigh = lngHigh - 1
Loop
Else
Do While VarArray(lngLow, lSortColumn) varTestVal
lngLow = lngLow + 1
Loop
Do While VarArray(lngHigh, lSortColumn) < varTestVal
lngHigh = lngHigh - 1
Loop
End If

If (lngLow <= lngHigh) Then
'swap the array rows
'-------------------
For c = LBound(VarArray, 2) To UBound(VarArray, 2)
varTempVal = VarArray(lngLow, c)
VarArray(lngLow, c) = VarArray(lngHigh, c)
VarArray(lngHigh, c) = varTempVal
Next c

lngLow = lngLow + 1
lngHigh = lngHigh - 1
End If

Loop While (lngLow <= lngHigh)

If lngFirst < lngHigh Then
QuickSort2DArray VarArray, _
lSortColumn, _
sOrder, _
lngFirst, _
lngHigh
End If

If lngLow < lngLast Then
QuickSort2DArray VarArray, _
lSortColumn, _
sOrder, _
lngLow, _
lngLast
End If

End Function


RBS



"RB Smissaert" wrote in message
...
Try this one:

Function QuickSort2DArray(VarArray As Variant, _
lSortColumn As Long, _
Optional sOrder As String = "A", _
Optional lngFirst As Long = -1, _
Optional lngLast As Long = -1) As Variant

Dim lngLow As Long
Dim lngHigh As Long
Dim lngMiddle As Long
Dim varTempVal As Variant
Dim varTestVal As Variant

If lngFirst = -1 Then lngFirst = LBound(VarArray)
If lngLast = -1 Then lngLast = UBound(VarArray)

lngMiddle = (lngFirst + lngLast) / 2
varTestVal = VarArray(lngMiddle, lSortColumn)
lngLow = lngFirst
lngHigh = lngLast

Do
If sOrder = "A" Then
Do While VarArray(lngLow, lSortColumn) < varTestVal
lngLow = lngLow + 1
Loop
Do While VarArray(lngHigh, lSortColumn) varTestVal
lngHigh = lngHigh - 1
Loop
Else
Do While VarArray(lngLow, lSortColumn) varTestVal
lngLow = lngLow + 1
Loop
Do While VarArray(lngHigh, lSortColumn) < varTestVal
lngHigh = lngHigh - 1
Loop
End If

If (lngLow <= lngHigh) Then
varTempVal = VarArray(lngLow, lSortColumn)
VarArray(lngLow, 1) = VarArray(lngHigh, lSortColumn)
VarArray(lngHigh, 1) = varTempVal
lngLow = lngLow + 1
lngHigh = lngHigh - 1
End If
Loop While (lngLow <= lngHigh)

If lngFirst < lngHigh Then QuickSort2DArray VarArray, lSortColumn,
sOrder, lngFirst, lngHigh
If lngLow < lngLast Then QuickSort2DArray VarArray, lSortColumn, sOrder,
lngLow, lngLast

End Function


RBS


"Joakim Norrman" wrote in
message ...
I want to sort a 2D-array. I have found Quicksort code but only for a
one-dimensional array. Do anyone know where to find a sort routine for
2D-arrays? I want a quick method. Not Bubble sort.


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 8
Default Quicksort for multi-columns
Thanks RB. Appreciate your help.

"RB Smissaert" wrote:

I had quickly edited a bit of code and forgot to alter something else.
This is how it should be:

Function QuickSort2DArray(VarArray As Variant, _
lSortColumn As Long, _
Optional sOrder As String = "A", _
Optional lngFirst As Long = -1, _
Optional lngLast As Long = -1) As Variant

Dim c As Long
Dim lngLow As Long
Dim lngHigh As Long
Dim lngMiddle As Long
Dim varTempVal As Variant
Dim varTestVal As Variant

If lngFirst = -1 Then lngFirst = LBound(VarArray)
If lngLast = -1 Then lngLast = UBound(VarArray)

lngMiddle = (lngFirst + lngLast) / 2
varTestVal = VarArray(lngMiddle, lSortColumn)
lngLow = lngFirst
lngHigh = lngLast

Do
If sOrder = "A" Then
Do While VarArray(lngLow, lSortColumn) < varTestVal
lngLow = lngLow + 1
Loop
Do While VarArray(lngHigh, lSortColumn) varTestVal
lngHigh = lngHigh - 1
Loop
Else
Do While VarArray(lngLow, lSortColumn) varTestVal
lngLow = lngLow + 1
Loop
Do While VarArray(lngHigh, lSortColumn) < varTestVal
lngHigh = lngHigh - 1
Loop
End If

If (lngLow <= lngHigh) Then
'swap the array rows
'-------------------
For c = LBound(VarArray, 2) To UBound(VarArray, 2)
varTempVal = VarArray(lngLow, c)
VarArray(lngLow, c) = VarArray(lngHigh, c)
VarArray(lngHigh, c) = varTempVal
Next c

lngLow = lngLow + 1
lngHigh = lngHigh - 1
End If

Loop While (lngLow <= lngHigh)

If lngFirst < lngHigh Then
QuickSort2DArray VarArray, _
lSortColumn, _
sOrder, _
lngFirst, _
lngHigh
End If

If lngLow < lngLast Then
QuickSort2DArray VarArray, _
lSortColumn, _
sOrder, _
lngLow, _
lngLast
End If

End Function


RBS



"RB Smissaert" wrote in message
...
Try this one:

Function QuickSort2DArray(VarArray As Variant, _
lSortColumn As Long, _
Optional sOrder As String = "A", _
Optional lngFirst As Long = -1, _
Optional lngLast As Long = -1) As Variant

Dim lngLow As Long
Dim lngHigh As Long
Dim lngMiddle As Long
Dim varTempVal As Variant
Dim varTestVal As Variant

If lngFirst = -1 Then lngFirst = LBound(VarArray)
If lngLast = -1 Then lngLast = UBound(VarArray)

lngMiddle = (lngFirst + lngLast) / 2
varTestVal = VarArray(lngMiddle, lSortColumn)
lngLow = lngFirst
lngHigh = lngLast

Do
If sOrder = "A" Then
Do While VarArray(lngLow, lSortColumn) < varTestVal
lngLow = lngLow + 1
Loop
Do While VarArray(lngHigh, lSortColumn) varTestVal
lngHigh = lngHigh - 1
Loop
Else
Do While VarArray(lngLow, lSortColumn) varTestVal
lngLow = lngLow + 1
Loop
Do While VarArray(lngHigh, lSortColumn) < varTestVal
lngHigh = lngHigh - 1
Loop
End If

If (lngLow <= lngHigh) Then
varTempVal = VarArray(lngLow, lSortColumn)
VarArray(lngLow, 1) = VarArray(lngHigh, lSortColumn)
VarArray(lngHigh, 1) = varTempVal
lngLow = lngLow + 1
lngHigh = lngHigh - 1
End If
Loop While (lngLow <= lngHigh)

If lngFirst < lngHigh Then QuickSort2DArray VarArray, lSortColumn,
sOrder, lngFirst, lngHigh
If lngLow < lngLast Then QuickSort2DArray VarArray, lSortColumn, sOrder,
lngLow, lngLast

End Function


RBS


"Joakim Norrman" wrote in
message ...
I want to sort a 2D-array. I have found Quicksort code but only for a
one-dimensional array. Do anyone know where to find a sort routine for
2D-arrays? I want a quick method. Not Bubble sort.



Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 806
Default Quicksort for multi-columns
Hello,

Please note that quicksort is not stable.

Maybe you would like to get mergesort or some other stable n * log(n)
algorithm...

Regards,
Bernd
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

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
Can this QuickSort work? RB Smissaert Excel Programming 24 June 27th 06 11:57 AM
If statement using multi columns KReese Excel Worksheet Functions 5 June 2nd 06 02:57 PM
Question for Alan on QuickSort Marston Excel Programming 3 August 31st 04 05:20 PM
recursion depth, 'Out of stack space' in Quicksort marcel Excel Programming 0 April 21st 04 09:11 PM
Multi-columns in a ListBox Tom Atkisson Excel Programming 1 October 5th 03 10:27 PM


All times are GMT +1. The time now is 08:52 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"

 

Contact Us ExcelBanter forum home Privacy Statement