Sort 2 dimensional Array, copy a row

[Neal Zimm]

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.

 

[Peter T]

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]

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.