alphabetically sorting an array
 

Hello!
I want to sort alphabetically a multi-column, bi-dimentional array. I would
like to do it all through VBA, not sending the array to a worksheet. Also, if
it were possible to specify what column I want to sort, it would be fantastic.
Thanking you in advance,
Albert

alphabetically sorting an array
 

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


' sample code that calls it.

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

--
Regards,
Tom Ogilvy


"Albert" wrote:

Hello!
I want to sort alphabetically a multi-column, bi-dimentional array. I would
like to do it all through VBA, not sending the array to a worksheet. Also, if
it were possible to specify what column I want to sort, it would be fantastic.
Thanking you in advance,
Albert

alphabetically sorting an array
 

Yes sir, it works fine, except for some strange glitch. The sort seems to
consider uppercase and lowercase as different. What to do?
Thanks,
Albert

alphabetically sorting an array
 

Albert wrote:
Yes sir, it works fine, except for some strange glitch. The sort seems to
consider uppercase and lowercase as different. What to do?
Thanks,
Albert

I don't know whether this response will advance or retard your efforts,
but the following code snippet may suggest an approach for you, or for
someone else who is inclined to help. Or it may prompt someone to
suggest an obvious and simple approach that makes this response seem silly.

In any event, the code snippet below is from a function that has an
optional Boolean parameter, "Match Case", whose default value is False.
I believe that if similar such snippets were sprinkled into Tom
Ogilvey's code at appropriate places, the sort could be case sensitive
or not, as desired, but I'm not currently up to studying my old code to
figure it out.

If you were to download the freely downloadable file at
http://home.pacbell.net/beban and scroll through the functions in it by
finding the term "MatchCase", you might get a feel for how to apply such
a snippet.

For i = LBound(arr) To UBound(arr)
If MatchCase Then
If arr(i, FilterColumn) < FilterValue Then
outputArrayRFilter(w) = RowVector(arr, i)
w = w + 1
End If
Else
If StrComp(arr(i, FilterColumn), FilterValue, vbTextCompare) < 0 Then
outputArrayRFilter(w) = RowVector(arr, i)
w = w + 1
End If
End If
Next

Alan Beban

alphabetically sorting an array
 

Just to chime in, the code is generalized to handle both text and numbers.
If you want to tailor it to Text and make it case insensitive, you could use
the strcomp function with vbTextCompare.

Easier might be to put the unchanged routine in a separate module with

Option Compare Text

at the top. I haven't tested it, but perhaps that will achieve your
required output.

--
Regards,
Tom Ogilvy


"Alan Beban" wrote:

Albert wrote:
Yes sir, it works fine, except for some strange glitch. The sort seems to
consider uppercase and lowercase as different. What to do?
Thanks,
Albert

I don't know whether this response will advance or retard your efforts,
but the following code snippet may suggest an approach for you, or for
someone else who is inclined to help. Or it may prompt someone to
suggest an obvious and simple approach that makes this response seem silly.

In any event, the code snippet below is from a function that has an
optional Boolean parameter, "Match Case", whose default value is False.
I believe that if similar such snippets were sprinkled into Tom
Ogilvey's code at appropriate places, the sort could be case sensitive
or not, as desired, but I'm not currently up to studying my old code to
figure it out.

If you were to download the freely downloadable file at
http://home.pacbell.net/beban and scroll through the functions in it by
finding the term "MatchCase", you might get a feel for how to apply such
a snippet.

For i = LBound(arr) To UBound(arr)
If MatchCase Then
If arr(i, FilterColumn) < FilterValue Then
outputArrayRFilter(w) = RowVector(arr, i)
w = w + 1
End If
Else
If StrComp(arr(i, FilterColumn), FilterValue, vbTextCompare) < 0 Then
outputArrayRFilter(w) = RowVector(arr, i)
w = w + 1
End If
End If
Next

Alan Beban

alphabetically sorting an array
 

This is kinda weird: my array has 13 columns. The sort works fine for columns
1 to 4, but when I ask it to sort column 5 or more, it sorts column 4
again... What to do?
Thanx in advance,
Albert

alphabetically sorting an array
 

I did a 26 column array sorting on column 26 and it worked fine for me both
ascending and descending with all numbers or all text.

Tried with all text on column 13. Again, no problem.

--
Regards,
Tom Ogilvy

"Albert" wrote in message
...
This is kinda weird: my array has 13 columns. The sort works fine for
columns
1 to 4, but when I ask it to sort column 5 or more, it sorts column 4
again... What to do?
Thanx in advance,
Albert

alphabetically sorting an array
 

yep, my bad... was calling it wrong.
another question: is there a way to do a sub-sort for a given column? (or
second sort criteria)?
Thanx,
Albert

alphabetically sorting an array
 

First, I haven't had a need to do this with the code I posted, but as I see
it,
If you mean can you sort on two columns (two Keys), the routine accepts a
start and end row argument, so you could sort on the higher order key, then
loop through the sorted column and have it sort each primary key subsection
where a subsection is defined by all the primary keys matching in value.

Quick Sort doesn't do a static sort, so you would need to use the above
approach.

An alternative would be to take a static sort routine and sort from lowest
key to highest key.

http://www.devx.com/vb2themax/Article/19900 David Ring


http://www.visualbasicforum.com/t78889.html


http://www.standards.com/Sorting/Sor...scription.html
has some sorting algorithms

--
Regards,
Tom Ogilvy


"Albert" wrote in message
...
yep, my bad... was calling it wrong.
another question: is there a way to do a sub-sort for a given column? (or
second sort criteria)?
Thanx,
Albert