Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort 2 dimensional Array, copy a row
Hi All,
At the risk of reinventing the wheel, I'm trying to develop a proc to sort a rectangular 2 dim array on any # of columns, ascending or descending on each desired column. In the design I get to where I have to re-order the rows. Say the array is dim'd (10, 5) is there a "shorthand" way to copy a row "as a whole" rather than something like this: For Col = lbound(InputAy, 2) to ubound(InputAy, 2) NewAy(SortedRow, Col) = InputAy(OldRow, Col) Next Col Thanks, Neal Z. -- Neal Z |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort 2 dimensional Array, copy a row
You could do something like this
hSlice = Application.Index(arr, 2) hSlice will now contain the entire 2nd row. However the Index function is relatively slow and faster to loop each element in the row. However again, don't bother with any of that, keep your array intact and use a helper index array. I'll leave it to you to work into your own requirements but for ideas something like this ' we know LBound will be 1 Redim ax(1 to Ubound(arr)) as long ' ' populate the initial index for i =1 to ubound(ax) ax(i) = i next Now in your sort instead of comparing arr(i,1) arr(i+1,1) do arr(ax(i),1) arr(ax(i+1),1) Swap ax(i) with ax(i+1) instead of the original array while sorting This way the main array stays intact until the sort is complete. Then you can either keep the original array but refer to items with the index array, eg arr(ax(row),col) or make a new Redim'd array for col = 1 to Ubound(arr,2) for rw = 1 to ubound(arr) arrNew(rw,col) = arr(ax(rw),col) etc Of course with your multi-D sort there's more to do but basically the same idea. If you want to get really adventurous you might look into memory swaps and the like, and get close to matching if not beating Excel's built in sort speed ! Regards, Peter T "Neal Zimm" wrote in message ... Hi All, At the risk of reinventing the wheel, I'm trying to develop a proc to sort a rectangular 2 dim array on any # of columns, ascending or descending on each desired column. In the design I get to where I have to re-order the rows. Say the array is dim'd (10, 5) is there a "shorthand" way to copy a row "as a whole" rather than something like this: For Col = lbound(InputAy, 2) to ubound(InputAy, 2) NewAy(SortedRow, Col) = InputAy(OldRow, Col) Next Col Thanks, Neal Z. -- Neal Z |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort 2 dimensional Array, copy a row
Thanks Peter,
I'll study your answer, as I don't understand all of it, but I am gratified that you used the wording "index array". My plan is to use a ParamArray to hold the sort parameters. (this really has no bearing on my posting, but it seems like what a ParamArray was meant to do.) I'm not actually going to sort the array. Rather, I will build a string sort key, append the incoming array row number to the rightmost section of the key. It will take a couple of data passes to build the sort key. After sorting the sortkey, pluck the row number from its spot in the key and re-write the input array rows to their output sequence. My arrays are "small", probably less than 750 rows and the array columns will contain either alphanumeric data or whole numbers. To sort a numeric column in descending sequence I'll use the 9's complement of the input # in the sort key. If any array column has alphanumeric data, I'll build two tables "abcd...wxyz" "zyxw...dcba" sort of an alphabetic "9's" complement concept sort array col# 4 ascend, 7 descend, 2 ascend. input row 128, col 7 has "bbbbbb" put y into sort key input row 129, col 7 has "cccccc" put x into sort key I'll always be sorting the sortkey on ascending values. For example, after the sorting, two keys could look like: 4 7 2 input array column #'s aaaaa,xxxxxx,gggg,129 aaaaa,yyyyyy,ffff,128 It may not be elegant, but it should work. Thanks again. Neal Z Neal Z. -- Neal Z "Peter T" wrote: You could do something like this hSlice = Application.Index(arr, 2) hSlice will now contain the entire 2nd row. However the Index function is relatively slow and faster to loop each element in the row. However again, don't bother with any of that, keep your array intact and use a helper index array. I'll leave it to you to work into your own requirements but for ideas something like this ' we know LBound will be 1 Redim ax(1 to Ubound(arr)) as long ' ' populate the initial index for i =1 to ubound(ax) ax(i) = i next Now in your sort instead of comparing arr(i,1) arr(i+1,1) do arr(ax(i),1) arr(ax(i+1),1) Swap ax(i) with ax(i+1) instead of the original array while sorting This way the main array stays intact until the sort is complete. Then you can either keep the original array but refer to items with the index array, eg arr(ax(row),col) or make a new Redim'd array for col = 1 to Ubound(arr,2) for rw = 1 to ubound(arr) arrNew(rw,col) = arr(ax(rw),col) etc Of course with your multi-D sort there's more to do but basically the same idea. If you want to get really adventurous you might look into memory swaps and the like, and get close to matching if not beating Excel's built in sort speed ! Regards, Peter T "Neal Zimm" wrote in message ... Hi All, At the risk of reinventing the wheel, I'm trying to develop a proc to sort a rectangular 2 dim array on any # of columns, ascending or descending on each desired column. In the design I get to where I have to re-order the rows. Say the array is dim'd (10, 5) is there a "shorthand" way to copy a row "as a whole" rather than something like this: For Col = lbound(InputAy, 2) to ubound(InputAy, 2) NewAy(SortedRow, Col) = InputAy(OldRow, Col) Next Col Thanks, Neal Z. -- Neal Z . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Export 1-dimensional array values to a two-dimensional table? | Excel Programming | |||
Changing a two-dimensional, one row array to one-dimensional | Excel Programming | |||
Create One-Dimensional Array from Two-Dimensional Array | Excel Programming | |||
Sort on two columns in two dimensional array | Excel Programming | |||
sort multi-dimensional array on numeric data? | Excel Programming |