Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting Alphabetically | Excel Worksheet Functions | |||
sorting using letters , but not alphabetically. | Excel Worksheet Functions | |||
sorting multiple columns alphabetically | Excel Discussion (Misc queries) | |||
Sorting columns alphabetically | Excel Worksheet Functions | |||
Sorting Alphabetically | Excel Discussion (Misc queries) |