Multi-Dimensional, Multi-Column array sort

# Thread: Multi-Dimensional, Multi-Column array sort

1. Senior Member
Join Date
Dec 1969
Posts
1,843

## Multi-Dimensional, Multi-Column array sort

Ok, I have a little function I&#039;ve used before to sort a small multi-dimensional array (between 10 and 30 items). It also sorts on multiple columns. The way I accomplish the multiple-column sort is very simple and inefficient: I just loop the sort function for each column. <BR><BR>Now I have to sort a large multi-dimensional array on more then one column. It will have between 250 and 1000 rows. My existing code is too slow. <BR><BR>Are there any suggestions on ways to improve this? I&#039;m guessing I&#039;ll probably have to scrap it and write a whole new sort function but I really don&#039;t want to do that. If I have to do that, any pointers? <BR><BR>Here&#039;s my existing code: <BR><BR>Function SortArray(arrArray(),MultiSortCol)<BR> &#039;PASS IN ARRAY TO BE SORTED AND A COLUMN NUMBER (OR COMMA-DELIMITED LIST OF COLUMN NUMBERS) T0 S0RT ON<BR> &#039;EXAMPLE: CALL SORTARRAY(MyArray,"0,1,2,3")<BR> SortOrder = Split(MultiSortCol,",")<BR> <BR> For iiii = 0 to UBound(SortOrder)<BR> For i = UBound(arrArray,2) - 1 To 0 Step -1<BR> For ii= 0 to i<BR> If arrArray(SortOrder(iiii),ii) &#062; arrArray(SortOrder(iiii),ii+1) Then<BR> For iii = 0 to ubound(arrArray)<BR> Temp = arrArray(iii,ii+1)<BR> arrArray(iii,ii+1) = arrArray(iii,ii)<BR> arrArray(iii,ii) = Temp<BR> Next<BR> End If<BR> Next<BR> Next<BR> Next<BR> SortArray = arrArray<BR>End Function

2. oli
Senior Member
Join Date
Dec 1969
Posts
3,961

## RE: Multi-Dimensional, Multi-Column array sort

Nesting the 4 loops is probably the issue.<BR><BR>I&#039;ve done something similar before, but do it at the point of creating the array, when you add a new entry, you just flip the last two entries if the new one is less than the previous (keep going until you hit an entry that is less).<BR><BR>The allows you to get away with one loop, and an additional loop which only loops until it hits the right value.<BR><BR>i.e.:<BR><BR>Keep adding values to end of array testing last two values:<BR>1<BR>2<BR>4<BR>5<BR><BR>Add 3 to array<BR><BR>1<BR>2<BR>4<BR>5<BR>3 &#060; is less than last?<BR><BR>1<BR>2<BR>4<BR>3 &#060; yes, flip<BR>5 <BR><BR>1<BR>2<BR>4 <BR>3 &#060; is less than last?<BR>5 <BR><BR>1<BR>2<BR>3 &#060; yes, Flip<BR>4 <BR>5 <BR><BR>Keep repeating until entry is in right place, then add next value to array.<BR><BR>As for it being multi-dimensional, just use multiple values to temporary store vars during the swap.<BR><BR><BR>

3. Senior Member
Join Date
Dec 1969
Posts
7,686

## Bubble Sort

that&#039;s what this is called.<BR><BR>I believe there&#039;s a faq on this

4. Senior Member
Join Date
Dec 1969
Posts
1,843

## Yes....

in fact, my sort is a bubble sort.. <BR><BR>Here&#039;s my primary question: <BR><BR>What do you think the best way would be to create sort that will sort based on multiple columns? The issue isn&#039;t so-much it being a multi-dimensional array, it&#039;s the multi-column thing.

5. Senior Member
Join Date
Dec 1969
Posts
2,854

## HUH?

&#062;&#062;The issue isn&#039;t so-much it being a multi-dimensional array, it&#039;s the multi-column thing. <BR><BR>thats the same thing? Having multiple columns is what makes an array 2-dimensional!<BR><BR>Anyway, hope this link helps.<BR><BR>http://www.flws.com.au/showusyourcode/codeLib/code/sortArray1.asp?CatId=1

6. Senior Member
Join Date
Dec 1969
Posts
1,843

## No No.... you mis-understood

What I meant by "Multi column thing" was the fact that I have to be able to sort this by multiple columns at once. Think of it as an order by statement in SQL: <BR><BR>Order By Col1, Col2, Col3 <BR><BR>I need to achieve that with an array.

7. Senior Member
Join Date
Dec 1969
Posts
2,854

## RE: No No.... you mis-understood

right......<BR><BR>sorry, I did misunderstand.<BR><BR>How are you creating the Array? where do the values come from? <BR>can you not sort the data before it becomes an Array?<BR><BR>

8. Senior Member
Join Date
Dec 1969
Posts
96,118

## Blech...

For starters, I&#039;d add an extra column to the array and then build a *single* sortable field in there.<BR><BR>In other words, if you are sorting on (example) LastName and Age, then put something like:<BR> arr( extraColumn, row ) = arr( lastNameCol, row ) & Right("0000" & arr( ageCol,row ), 4 )<BR><BR>And now you only have to sort on one column.<BR><BR>Then the other thing I would do is use an "indirection" array and do all my element swapping in it, leaving the original array untouched (becuz all that column swapping is horribly expensive).<BR><BR>Something like:<BR><BR>maxRow = UBound(arrArray,2)<BR>Dim order()<BR>ReDim order( maxRow )<BR>For i = 0 To maxRow : order(i) = i : Next<BR><BR>And now your tests and swaps become:<BR> If arrArray( extraColumn, order(row) ) &#062; arrArray( extraColumn, order(row+1) ) Then<BR> temp = order(row)<BR> order(row) = order(row+1)<BR> order(row+1) = temp<BR> End If<BR><BR>Then, finally, to display stuff in sorted order:<BR><BR>For i = 0 To rowMax<BR> row = order(i)<BR> Response.Write arrArray( col, row )<BR> ...<BR>Next<BR><BR>Pull both those tricks and sorting the ugly 2D array will be almost as fast as sorting a simple 1D array.<BR><BR>

9. Senior Member
Join Date
Dec 1969
Posts
1,843

## I love it

I can&#039;t believe I didn&#039;t think about combining the sort columns before. Wow. <BR><BR>Here&#039;s what this script does: It&#039;s basically a scientific calculator. It reads in a formula(a really, really, really long formula used to calculate bonuses) and it has to evaluate it. I have one sub that reads through the equation to make a "plan" on how to evaluate it according to the rules of the order of operations. <BR><BR>It has 5 steps 1 - Parens 2 - exponents 3 - Mult. & Div 4 - Add & Subt. <BR><BR>It loops through the forumla and identifies the location of an operator and it put it in an array. Another column in that array is the step-number from above. <BR><BR>I then sort it first on the step order second on the position of the operator from left to right. Then I do the math. <BR><BR>Let me tell you: This is 100 lines of code and it&#039;s some of the most complicated VBSCRIPT I&#039;ve ever written. But i love it... <BR><BR>Why do you care? You don&#039;t. I just wanted to share, and you&#039;re my only audience :)

10. Senior Member
Join Date
Dec 1969
Posts
96,118

## RETHINK TIME!

This is one case where it might be worth using VBScripts EVAL function!<BR><BR>Basically, you would create your formula as a string, using named VBS variables, and then you&#039;d just ask VBS to EVAL it.<BR><BR>You&#039;d have to use proper VBS syntax (e.g., ^ for exponentiation), but other than that...<BR><BR>Silly example:<BR><BR>&#060;HTML&#062;&#060;BODY&#062;<B R>&#060;%<BR>expr = "pmt * ( 1 - (1 + rate)^(-per) ) / rate"<BR><BR>pmt = 100 &#039; amount of payment<BR>rate = 0.01 &#039; monthly interest rate (i.e., 12% per year)<BR>per = 24 &#039; number of months<BR><BR>pv = Eval( expr )<BR>%&#062;<BR><BR>So now you don&#039;t have to do that "plan" crap. Let VBS do it for you.<BR><BR><BR><BR><BR><BR>%

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•