Sorting arrays that contain multiple values
 

How do I go about sorting an array with 3 to 4 pieces of information via
descending order of the first variable?


--
greenglide
------------------------------------------------------------------------
greenglide's Profile: http://www.excelforum.com/member.php...o&userid=29943
View this thread: http://www.excelforum.com/showthread...hreadid=496435

Sorting arrays that contain multiple values
 

Here is some code I have modified to do it.

The second procedure shows how to call the quicksort.

Sub QuickSort(SortArray, col, L, R, bAscending)
'
'Originally Posted by Jim Rech 10/20/98 Excel.Programming
'Modified to sort on first column of a two dimensional array
'Modified to handle a second dimension greater than 1 (or zero)
'Modified to sort on a specified column in a 2D array
'Modified to do Ascending or Descending
Dim i, j, X, Y, mm

i = L
j = R
X = SortArray((L + R) / 2, col)
If bAscending Then
While (i <= j)
While (SortArray(i, col) < X And i < R)
i = i + 1
Wend
While (X < SortArray(j, col) And j L)
j = j - 1
Wend
If (i <= j) Then
For mm = LBound(SortArray, 2) To UBound(SortArray, 2)
Y = SortArray(i, mm)
SortArray(i, mm) = SortArray(j, mm)
SortArray(j, mm) = Y
Next mm
i = i + 1
j = j - 1
End If
Wend
Else
While (i <= j)
While (SortArray(i, col) X And i < R)
i = i + 1
Wend
While (X SortArray(j, col) And j L)
j = j - 1
Wend
If (i <= j) Then
For mm = LBound(SortArray, 2) To UBound(SortArray, 2)
Y = SortArray(i, mm)
SortArray(i, mm) = SortArray(j, mm)
SortArray(j, mm) = Y
Next mm
i = i + 1
j = j - 1
End If
Wend
End If
If (L < j) Then Call QuickSort(SortArray, col, L, j, bAscending)
If (i < R) Then Call QuickSort(SortArray, col, i, R, bAscending)
End Sub




Sub aaTesterSort()
Dim bAscending As Boolean
Set rng = Range("I7").CurrentRegion
vArr = rng.Value
bAscending = False
QuickSort vArr, 1, LBound(vArr, 1), UBound(vArr, 1), bAscending
Range("I26").Resize(UBound(vArr, 1), UBound(vArr, 2)).Value = vArr
End Sub

--
Regards,
Tom Ogilvy

"greenglide" wrote
in message ...

How do I go about sorting an array with 3 to 4 pieces of information via
descending order of the first variable?


--
greenglide
------------------------------------------------------------------------
greenglide's Profile:
http://www.excelforum.com/member.php...o&userid=29943
View this thread: http://www.excelforum.com/showthread...hreadid=496435

Sorting arrays that contain multiple values
 

Hi guys,

The said function is useful. But let me post an additional requirement though.

What would be the code to delete unnecessary rows in a particular worksheet?

It would be unnecessary if the entry of the first cell in a row is the same
to the first cell of the next row.
It would be best to retain just one row for the said first cell entry.


"Tom Ogilvy" wrote:

Here is some code I have modified to do it.

The second procedure shows how to call the quicksort.

Sub QuickSort(SortArray, col, L, R, bAscending)
'
'Originally Posted by Jim Rech 10/20/98 Excel.Programming
'Modified to sort on first column of a two dimensional array
'Modified to handle a second dimension greater than 1 (or zero)
'Modified to sort on a specified column in a 2D array
'Modified to do Ascending or Descending
Dim i, j, X, Y, mm

i = L
j = R
X = SortArray((L + R) / 2, col)
If bAscending Then
While (i <= j)
While (SortArray(i, col) < X And i < R)
i = i + 1
Wend
While (X < SortArray(j, col) And j L)
j = j - 1
Wend
If (i <= j) Then
For mm = LBound(SortArray, 2) To UBound(SortArray, 2)
Y = SortArray(i, mm)
SortArray(i, mm) = SortArray(j, mm)
SortArray(j, mm) = Y
Next mm
i = i + 1
j = j - 1
End If
Wend
Else
While (i <= j)
While (SortArray(i, col) X And i < R)
i = i + 1
Wend
While (X SortArray(j, col) And j L)
j = j - 1
Wend
If (i <= j) Then
For mm = LBound(SortArray, 2) To UBound(SortArray, 2)
Y = SortArray(i, mm)
SortArray(i, mm) = SortArray(j, mm)
SortArray(j, mm) = Y
Next mm
i = i + 1
j = j - 1
End If
Wend
End If
If (L < j) Then Call QuickSort(SortArray, col, L, j, bAscending)
If (i < R) Then Call QuickSort(SortArray, col, i, R, bAscending)
End Sub




Sub aaTesterSort()
Dim bAscending As Boolean
Set rng = Range("I7").CurrentRegion
vArr = rng.Value
bAscending = False
QuickSort vArr, 1, LBound(vArr, 1), UBound(vArr, 1), bAscending
Range("I26").Resize(UBound(vArr, 1), UBound(vArr, 2)).Value = vArr
End Sub

--
Regards,
Tom Ogilvy

"greenglide" wrote
in message ...

How do I go about sorting an array with 3 to 4 pieces of information via
descending order of the first variable?


--
greenglide
------------------------------------------------------------------------
greenglide's Profile:
http://www.excelforum.com/member.php...o&userid=29943
View this thread: http://www.excelforum.com/showthread...hreadid=496435

Sorting arrays that contain multiple values
 

lastrow = cells(rows.count,1).End(xlup).row
for i = lastrow to 2 step -1
if cells(i,1).Value = cells(i-1,1).Value then
rows(i).Delete
end if
Next

--
Regards,
Tom Ogilvy


"aspiringlawtechie0615"
wrote in message ...
Hi guys,

The said function is useful. But let me post an additional requirement
though.

What would be the code to delete unnecessary rows in a particular
worksheet?

It would be unnecessary if the entry of the first cell in a row is the
same
to the first cell of the next row.
It would be best to retain just one row for the said first cell entry.


"Tom Ogilvy" wrote:

Here is some code I have modified to do it.

The second procedure shows how to call the quicksort.

Sub QuickSort(SortArray, col, L, R, bAscending)
'
'Originally Posted by Jim Rech 10/20/98 Excel.Programming
'Modified to sort on first column of a two dimensional array
'Modified to handle a second dimension greater than 1 (or zero)
'Modified to sort on a specified column in a 2D array
'Modified to do Ascending or Descending
Dim i, j, X, Y, mm

i = L
j = R
X = SortArray((L + R) / 2, col)
If bAscending Then
While (i <= j)
While (SortArray(i, col) < X And i < R)
i = i + 1
Wend
While (X < SortArray(j, col) And j L)
j = j - 1
Wend
If (i <= j) Then
For mm = LBound(SortArray, 2) To UBound(SortArray, 2)
Y = SortArray(i, mm)
SortArray(i, mm) = SortArray(j, mm)
SortArray(j, mm) = Y
Next mm
i = i + 1
j = j - 1
End If
Wend
Else
While (i <= j)
While (SortArray(i, col) X And i < R)
i = i + 1
Wend
While (X SortArray(j, col) And j L)
j = j - 1
Wend
If (i <= j) Then
For mm = LBound(SortArray, 2) To UBound(SortArray, 2)
Y = SortArray(i, mm)
SortArray(i, mm) = SortArray(j, mm)
SortArray(j, mm) = Y
Next mm
i = i + 1
j = j - 1
End If
Wend
End If
If (L < j) Then Call QuickSort(SortArray, col, L, j, bAscending)
If (i < R) Then Call QuickSort(SortArray, col, i, R, bAscending)
End Sub




Sub aaTesterSort()
Dim bAscending As Boolean
Set rng = Range("I7").CurrentRegion
vArr = rng.Value
bAscending = False
QuickSort vArr, 1, LBound(vArr, 1), UBound(vArr, 1), bAscending
Range("I26").Resize(UBound(vArr, 1), UBound(vArr, 2)).Value = vArr
End Sub

--
Regards,
Tom Ogilvy

"greenglide"
wrote
in message
...

How do I go about sorting an array with 3 to 4 pieces of information
via
descending order of the first variable?


--
greenglide

------------------------------------------------------------------------
greenglide's Profile:
http://www.excelforum.com/member.php...o&userid=29943
View this thread:
http://www.excelforum.com/showthread...hreadid=496435