Sort on two columns in two dimensional array

[Al]

Hi folks,

I am trying to figure out how to sort a two dimensional array by first
element 0, then element 1. I am struggling!! Can someone offer me
some help?

My array is arySort(4, 50)

Cheers,
Al

 

[Tom Ogilvy]

Paste it into a dummy sheet

use excel's sort

pick it back up.

 

[David Fixemer]

Al,

I'm somewhat confused. A two dimensional array would
look something like:

| 1 | 2 | 3 | 4
-+------------+------------+------------+------------
1| Entry(1,1) | Entry(1,2) | Entry(1,3) | Entry (1,4)
-+------------+------------+------------+------------
2| Entry(2,1) | Entry(2,2) | Entry(2,3) | Entry (2,4)
-+------------+------------+------------+------------
3| Entry(3,1) | Entry(3,2) | Entry(3,3) | Entry (3,4)

Another way of thinking about the problem would be:

| 1 | 2 | 3 | 4
-+------------+------------+------------+------------
1| Entry1 | Entry2 | Entry3 | Entry4
-+------------+------------+------------+------------
2| Entry5 | Entry6 | Entry7 | Entry8
-+------------+------------+------------+------------
3| Entry9 | Entry10 | Entry11 | Entry12


All you want to do is sort "Entry1"
through "Entry12"? I'm confused about sorting in one
direction and then the other? What are your entrys
(Numbers, Strings)?

David Fixemer

 

[Al]

Hi David,

My array looks something like this:

| 1 | 2 | 3 | 4
-+------------+------------+------------+------------
1| Fund# | Test# | Currency | Balance
-+------------+------------+------------+------------
2| Fund# | Test# | Currency | Balance
-+------------+------------+------------+------------
3| Fund# | Test# | Currency | Balance

I would like to be able sort on Fund# then Test# so I have the
following:

| 1 | 2 | 3 | 4
-+------------+------------+------------+------------
1| FundA | TestB | Currency | Balance
-+------------+------------+------------+------------
2| FundB | TestA | Currency | Balance
-+------------+------------+------------+------------
3| FundB | TestB | Currency | Balance

The elements are all strings. I know I could dump to a worksheet then
do a sort that way, but I would rather do it via code for speed. And
so I learn how to do it!!

Thanks very much,
Al

 

[Al]

Thanks Tom, I just wondered if there was a way to code this for speed
of execution and neatness.

Cheers,
Al

 

[Tom Ogilvy]

Maybe it isn't that slow:

Dim ary as Variat
ary = Activesheet.Range("A1").CurrentRegion
worksheets.Add
Range("A1").resize(ubound(ary,1)-lbound(ary,1)+1, _
Ubound(ary,2)-lbound(ary,2)+1).Value = Ary
Range("A1").CurrentRegion.Sort _
Key1:=Range("A1"), Order1:=xlAscending, _
Key2:=Range("B1"), Order1:=xlAscending, _
Header:=xlNo
ary = Range("A1").CurrentRegion
Application.DisplayAlerts = False
activesheet.Delete
Application.DisplayAlerts = True

I don't know if what techniques you use, so the above may be old hat to you,
but then again, maybe it isn't.

I have never seen an algorithm posted to do multiple keys. I suspect the
algorithm is to do the sort on the first key, then loop through the array
and identify the rows that are identical in the first key position and pass
in these arguments to the sort routine to sort this subset on the second
key. The quicksort algorithm I have seen has parameters to identify a
subsection of the array.

You can search on http://groups.google.com, go to advanced search, search
on quicksort and Rech, this newsgroup, ogilvy as author. Restrict to May
2003 to present or use this link

http://tinyurl.com/yv6tp

 

[David Fixemer]

Al,

I've read all of Tom's and your comments and it
appears you two either have it worked out, or will get it
worked out. That is greate considering I didn't even
understand the problem. Overnight (Drive Home) I
considered your problem a little more. Your reply simply
reinforced my conculsions. My previous post had the
elements already in order. If it actually looked like
this:

| 1 | 2 | 3 | 4
-+------------+------------+------------+------------
1| Entry1 | Entry11 | Entry10 | Entry4
-+------------+------------+------------+------------
2| Entry5 | Entry3 | Entry2 | Entry8
-+------------+------------+------------+------------
3| Entry9 | Entry7 | Entry6 | Entry12


Then you would desire a list like one of the following,
depending on weather you sort by row or column first?

Entry1 Entry1
Entry4 Entry5
Entry10 Entry9
Entry11 -------
------- Entry3
Entry2 Entry7
Entry3 Entry11
Entry5 -------
Entry8 Entry2
------- Entry6
Entry6 Entry10
Entry7 -------
Entry9 Entry4
Entry12 Entry8
Entry12

 

[Peter M]

Sorting algorithms have exercised the minds of programmers for as long as we
have had the ability to programme (and possibly before). There are numerous
books on the subject, but the ones I have found useful are:

Algorithms, by John Sedgewick, this seems to come in a number of flavours
with the examples having been written in different programming languages,
the ISBN numbers I have are:

0-201-88863-7 softback, language C++ (but bought in Phillipines)
0-201-06673-4 hardback, language Pascal (nicer book though)

I'm sure there are many others, and probably more uptodate also.

Not sure whether this helps, but hope so.

Peter

 

[Al]

Peter,

Thanks for that. I can't find the first book ISBN anywhere on the
web, but the second one turned up several books by Robert Sedgewick on
Amazon - might invest in one of those, so thanks for the tip!!

Cheers,
Al

 

[Al]

Hi David,

Thanks for the message - as per Toms suggestion (thanks!), I ended up
dumping the data to a worksheet and doing a data sort on it before
reading it back in.

Cheers,
Al