Sorting Array

[plans]

Hi,
I have an Array which has 3 columns:
Data1 2 23
Data2 5 11
Data3 1 15
Data4 1 17

I have declared the Array as MyArray ( 0 to 500, 0 to 2)

I want to sort the Array by the 3rd column in descending order.
Ho do I do this?
Thanks

[sktneer]

One tricky way to achieve this is, insert a new sheet and write your array onto it and sort the third column and rewrite the sorted values back into array and delete the sheet you inserted like below...

Please Login or Register  to view this content.

[mikerickson]

One way to do this would be to create a one dimensional indexing array and sort that array on the custom LT function.

Please Login or Register  to view this content.

The indexing array is an array of row numbers and LT returns whether the third column of the indicated row is less than the other.

The full routine would look like this.

Please Login or Register  to view this content.

This uses a bubble sort algorithm and for small data sets sktneer's use of Excel's built in sort is faster, but for larger data sets the indexing array approach is faster if one uses a quick sort rather than a bubble sort.

I've generalized this routine quite a bit. The array does not need to be 0-based and the SortDescending variable could be used to sort ascending.

[EssoExplJoe]

Below is some code for a quicker sort than the bubble sort if you can put your data into a userdefined type such as struct_State below. Basic sort code is copied from a posting on the internet and adapted for userdefined types..I'm not good enough to write the code from scratch.

Please Login or Register  to view this content.