Anyone Bored enough, vbs sorting routine

# Thread: Anyone Bored enough, vbs sorting routine

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

## Anyone Bored enough, vbs sorting routine

Howdy,<BR><BR>Okay, its 1 day until I go on vacation, my mind isn&#039;t with it.<BR><BR>I have been playing with the quick sort algorithm in my spare time (based of article on 4guys). I am attempting to sort the <BR>array on based on the 8th column in my VBS Array (its a date that may/may not be there) <BR><BR>All sorting routines seem to run on the first index, not the second, needless to say they are a touch confusing this week, kinda like women all the time to me.<BR><BR>blah,blah.<BR><BR>I will post what code i have so far, its not necessarily finished, but the for loops i marked come out confusing, i guess i shouldn&#039;t have drank so much and killed all my data structures brain cells. <BR>Thanks - Analog man in binary business.<BR><BR>&#039;*************************** **************<BR>The Swap function used <BR>&#039;***********************************<BR>S ub SwapRows(ary,row1,row2) <BR>dim p,t<BR> for p = 0 to UBound(ary,1)<BR> t = general(p,row1)<BR> general(p,row1) = general(p,row2)<BR> general(p,row2) = t<BR> next<BR>End Sub &#039;SwapRows <BR>&#039;*************************************<BR ><BR>&#039;*************************************** **<BR>Sub QuickSort(vec,loBound,hiBound,SortField) <BR>&#039; Parameters: vec - array to be sorted == <BR>&#039; SortField - The field to sort on (2nd dimension value) == <BR><BR> Dim pivot(),loSwap,hiSwap,temp,counter <BR> Redim pivot (Ubound(vec,2)) <BR><BR> &#039;== Two items to sort <BR><BR> if hiBound - loBound = 1 then <BR> if vec(SortField,loBound) &#062; vec(SortField,hiBound) then Call SwapRows(vec,hiBound,loBound) <BR><BR> End If <BR> <BR> &#039; ********* part i haven&#039;t figured out ********<BR> &#039; ** worst of it is, i knew this stuff once ***<BR> For counter = 0 to Ubound(vec,2) <BR> pivot(counter) = vec(int((loBound + hiBound) / 2),counter) <BR> vec(int((loBound + hiBound) / 2),counter) = vec(loBound,counter) <BR> vec(loBound,counter) = pivot(counter) <BR> Next <BR><BR> loSwap = loBound + 1 <BR> hiSwap = hiBound <BR><BR> do &#039;== Find the right loSwap <BR><BR> while loSwap &#060; hiSwap and vec(SortField,loSwap) &#060;= pivot(loSwap) <BR> loSwap = loSwap + 1 <BR> wend <BR> <BR> while vec(SortField,hiSwap) &#062; pivot(hiSwap) <BR> hiSwap = hiSwap - 1 <BR> wend &#039;== Swap values <BR><BR> if loSwap &#060; hiSwap then Call SwapRows(vec,loSwap,hiSwap) <BR><BR> loop while loSwap &#060; hiSwap <BR><BR> For counter = 0 to Ubound(vec,2) <BR> vec(sortField,loSwap) = vec(sortField,hiSwap) <BR> vec(sortField,hiSwap) = pivot(counter) <BR> Next <BR><BR> if loBound &#060; (hiSwap - 1) then Call QuickSort(vec,loBound,hiSwap-1,SortField) <BR> if hiSwap + 1 &#060; hibound then Call QuickSort(vec,hiSwap+1,hiBound,SortField) <BR>End Sub &#039;QuickSort <BR><BR>&#039;************************************ *********<BR>Cheers,Skol, etc.

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

## This part makes no sense:

For counter = 0 to Ubound(vec,2) <BR> pivot(counter) = vec(int((loBound + hiBound) / 2),counter) <BR> vec(int((loBound + hiBound) / 2),counter) = vec(loBound,counter) <BR> vec(loBound,counter) = pivot(counter) <BR>Next<BR><BR>Elsewhere, you clearly show that vec is a conventional 2D VBS array, presumably obtained from GetRows? Where the first dimension is the FIELD number and the second is the ROW number.<BR><BR>Yet in that code you are indexing into the FIELDs by Int((LoBound+HiBound)/2) ???? That&#039;s obviously nonsense!<BR><BR>*EXCEPT* in the SwapRows subroutine, *EVERY PLACE ELSE* in your code you should *ALWAYS* be using vec(sortField,&#060;expression&#062;). You should *NEVER* have anything except "sortField" as the first subscript of that array! <BR><BR>I think you are making this *WAY* too hard! <BR><BR>Look, *except* for the swap code, *pretend* that you have a simple one-dimensional array. And every place in the original quicksort code where you *had* oneDim(&#060;expression&#062;), just substitute in twoDim(sortField,&#060;expression&#062;) and otherwise DON&#039;T TOUCH THE CODE!<BR><BR><BR><BR>

3. Heaven's Martini Guest

## no kidding

BW,<BR><BR>Ahh, knew you couldn&#039;t resist.<BR>I am 99% sure i am making this too hard, I understood this years ago and i wasn&#039;t near as smart as I am now. anywho.<BR><BR>That loop hasn&#039;t been touched by me, thats from that article,<BR>you are right about the array I hand built it, but its the same structure as getrows would do it. (to keep from confusion in code maintenance time)<BR><BR>&#039;******************************* **************<BR>I believe, according to what you said, this should be correct.<BR>but, its deleting the record in array(8,i)<BR><BR>Sub QuickSort(vec,loBound,hiBound,SortField) <BR> Dim pivot(),loSwap,hiSwap,temp,counter <BR> Redim pivot (Ubound(vec,2)) <BR><BR> &#039;== Two items to sort <BR><BR> if hiBound - loBound = 1 then <BR> if vec(SortField,loBound) &#062; vec(SortField,hiBound) then Call SwapRows(vec,hiBound,loBound) <BR> End If <BR> <BR> For counter = 0 to Ubound(vec,2) <BR> pivot(counter) = vec(sortField,int((loBound + hiBound) / 2)) <BR> vec(sortField,int((loBound + hiBound) / 2)) = vec(sortField,loBound) <BR> vec(sortField,loBound) = pivot(counter) <BR> Next <BR><BR> loSwap = loBound + 1 <BR> hiSwap = hiBound <BR><BR> do &#039;== Find the right loSwap <BR><BR> while loSwap &#060; hiSwap and vec(SortField,loSwap) &#060;= pivot(loSwap) <BR> loSwap = loSwap + 1 <BR> wend <BR> <BR> while vec(SortField,hiSwap) &#062; pivot(hiSwap) <BR> hiSwap = hiSwap - 1 <BR> wend &#039;== Swap values <BR><BR> if loSwap &#060; hiSwap then Call SwapRows(vec,loSwap,hiSwap) <BR><BR> loop while loSwap &#060; hiSwap <BR><BR> For counter = 0 to Ubound(vec,2) <BR> vec(sortField,loSwap) = vec(sortField,hiSwap) <BR> vec(sortField,hiSwap) = pivot(counter) <BR> Next <BR><BR> if loBound &#060; (hiSwap - 1) then Call QuickSort(vec,loBound,hiSwap-1,SortField) <BR> if hiSwap + 1 &#060; hibound then Call QuickSort(vec,hiSwap+1,hiBound,SortField) <BR><BR>End Sub &#039;QuickSort <BR><BR>that portion with the counter is what is screwing me up, the part that makes * no sense *.<BR><BR>Any Help, i will freely except the ridicule that goes along with being stupid today. <BR>

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

## I think I'd give up on this code...

There are just too darned many things that are obviously wrong with it.<BR><BR>For example:<BR><BR>For counter = 0 to Ubound(vec,2) <BR>pivot(counter) = vec(sortField,int((loBound + hiBound) / 2)) <BR>vec(sortField,int((loBound + hiBound) / 2)) = vec(sortField,loBound) <BR>vec(sortField,loBound) = pivot(counter) <BR>Next <BR><BR>No that makes less than zero sense. You are swapping around and mucking with *only* the sortField column of the 2D array! So that means that *ALL THE OTHER COLUMNS* stay in their original places! So *maybe* column 8 (the sortField column) might come out right, but none of the other columns could possibly be right! <BR><BR>Sorry, but I think somehow this code has gotten *so* scrambled that it is now worthless.<BR><BR>Show me the *ORIGINAL* code--for sorting a *ONE* dimensional array--and I&#039;ll show you what I think is the best way to alter it to two dimensional.<BR><BR>Bill<BR><BR>

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

## LOL!! Wonderful!

&#062; i will freely except the ridicule that goes along with being stupid today<BR><BR>Of *course* you will *EXcept* the ridicule? Why should you allow it in? You *certainly* don&#039;t want to *ACcept* it, do you?<BR><BR>Bill-the-exception-proves-the-rule Wilkinson

6. Non Pedantic Guest

## ahh, to be prodded for my poor english

I almost forgot about the psychotic way you pursue the english language. <BR><BR>the worst of it is, i know the difference, i am just so utterly in need of vacation my mind is shot (also known as tired)<BR><BR>* I did change that stupid code , but i need to do this with a fresh mind, which isn&#039;t insight. <BR><BR>

7. Senior Member
Join Date
Dec 1969
Posts
1,362

## would appreciate it, whatever push

you can give, will help, i will take it with on vacation, along with a couple of 100 other things.<BR><BR>*note: performance is not an issue (small data set, seldom accessed)<BR><BR>Sub SingleSorter( byRef arrArray )<BR><BR> For i = 0 To UBound( arrArray ) - 1<BR> &#039;Take a snapshot of the first element<BR> &#039;in the array because if there is a <BR> &#039;smaller value elsewhere in the array <BR> &#039;we&#039;ll need to do a swap.<BR> smallest_value = arrArray ( i )<BR> rememberkey = arrArray ( i )<BR> rememberval = arrArray ( i )<BR> swap_pos = i<BR> <BR> For x = i + 1 to UBound( arrArray )<BR> &#039;Start inner loop.<BR> If arrArray ( x ) &#060; smallest_value Then<BR> &#039;This is now the lowest number - <BR> &#039;remember it&#039;s position.<BR> swap_pos = x<BR> smallest_value = arrArray ( x )<BR> smallest_key = arrArray ( x )<BR> End If<BR> Next<BR> <BR> If swap_pos &#060;&#062; i Then<BR> &#039;If we get here then we are about to do a swap<BR> &#039;within the array. <BR> arrArray ( swap_pos ) = rememberkey<BR> arrArray ( swap_pos ) = rememberval<BR> arrArray ( i ) = smallest_key<BR> arrArray ( i ) = smallest_value<BR> End If <BR> Next<BR>End Sub<BR><BR>...thanks for anything. I haven&#039;t mentally lost it this much for a while. I guess being analog is bad.

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

## Ugh! Rewrite time!

I dunno where you got it from, but toss it out and find another source!<BR><BR>Look at that:<BR>&nbsp; &nbsp; arrArray ( swap_pos ) = rememberkey<BR>&nbsp; &nbsp; arrArray ( swap_pos ) = rememberval<BR>&nbsp; &nbsp; arrArray ( i ) = smallest_key<BR>&nbsp; &nbsp; arrArray ( i ) = smallest_value<BR>What *possible* point could there be in putting TWO DIFFERENT values into the same array element????? Only the second one would be remembered, anyway!<BR><BR>Ummmm...except I think I recognize some of the comments! Like it came from something *I* wrote???<BR><BR>Well, however it got to you, it&#039;s been mangled into trash now!<BR><BR>Oh, hell: Let&#039;s just rewrite it:<BR><BR>&#060;%<BR>&#039; This is a variation on a BUBBLE SORT<BR>&#039; that, instead of swapping on the fly, <BR>&#039; simply always finds the smallest remaining <BR>&#039; value in the array and moves it to the top.<BR>&#039; That is, on the first time through the loop, <BR>&#039; the smallest value ends up in the top slot.<BR>&#039; The next time through, the smallest *remaining*<BR>&#039; value ends up in the next to top slot. Etc., etc.<BR>&#039;<BR>Sub SingleSorter( byRef arrArray )<BR><BR>For i = 0 To UBound( arrArray ) - 1<BR>&nbsp; &nbsp; &#039;Take a snapshot of the first element<BR>&nbsp; &nbsp; &#039;in the array because if there is a <BR>&nbsp; &nbsp; &#039;smaller value elsewhere in the array <BR>&nbsp; &nbsp; &#039;we&#039;ll need to do a swap.<BR>&nbsp; &nbsp; smallest_value = arrArray ( i )<BR>&nbsp; &nbsp; swap_pos = i<BR><BR>&nbsp; &nbsp; For x = i + 1 to UBound( arrArray ) &#039;Start inner loop.<BR>&nbsp; &nbsp; &nbsp; &nbsp; If arrArray ( x ) &#060; smallest_value Then<BR>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &#039;This is now the lowest number - <BR>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &#039;remember its position.<BR>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; swap_pos = x<BR>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; smallest_value = arrArray ( x )<BR>&nbsp; &nbsp; &nbsp; &nbsp; End If<BR>&nbsp; &nbsp; Next<BR><BR>&nbsp; &nbsp; &#039; now, if the smallest_value is *NOT* at the<BR>&nbsp; &nbsp; &#039; current position "i", then move it there!<BR>&nbsp; &nbsp; &#039; NOTE: We *could* simply *ALWAYS* swap...at<BR>&nbsp; &nbsp; &#039; worst, we&#039;d be swapping position "i" with itself!<BR>&nbsp; &nbsp; If swap_pos &#060;&#062; i Then<BR>&nbsp; &nbsp; &nbsp; &nbsp; arrArray ( swap_pos ) = arrArray( i )<BR>&nbsp; &nbsp; &nbsp; &nbsp; arrArray ( i ) = smallest_value<BR>&nbsp; &nbsp; End If <BR>Next<BR>End Sub<BR>%&#062;<BR><BR>See that? I just simplified that mangled version to something both easier to understand *and* hopefully that will work!<BR><BR>NOW...to convert it into a 2D array...and this was the whole reason that I once wrote a sort that looked like that!<BR><BR>&#060;%<BR>&#039; This is a variation on a BUBBLE SORT<BR>&#039; that, instead of swapping on the fly, <BR>&#039; simply always finds the smallest remaining <BR>&#039; value in the array and moves it to the top.<BR>&#039; That is, on the first time through the loop, <BR>&#039; the smallest value ends up in the top slot.<BR>&#039; The next time through, the smallest *remaining*<BR>&#039; value ends up in the next to top slot. Etc., etc.<BR>&#039;<BR>&#039; This is WORTH DOING with a 2D array, because we<BR>&#039; cut down on the number of swaps needed, and swaps<BR>&#039; are *very* time consuming in a 2D array!<BR>&#039;<BR>Sub DoubleSorter( byRef arrArray, byVal keyColumn )<BR><BR>&#039; 2D array, assume that cells are addressed by arrArray(column,row)<BR>For row = 0 To UBound( arrArray, 2 ) - 1<BR>&nbsp; &nbsp; &#039;Take a snapshot of the first element<BR>&nbsp; &nbsp; &#039;in the array because if there is a <BR>&nbsp; &nbsp; &#039;smaller value elsewhere in the array <BR>&nbsp; &nbsp; &#039;we&#039;ll need to do a swap.<BR>&nbsp; &nbsp; smallest_value = arrArray ( keyColumn, row )<BR>&nbsp; &nbsp; swap_pos = row<BR><BR>&nbsp; &nbsp; For x = row + 1 to UBound( arrArray, 2 ) &#039; Start inner loop.<BR>&nbsp; &nbsp; &nbsp; &nbsp; If arrArray ( keyColumn, x ) &#060; smallest_value Then<BR>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &#039;This is now the lowest number - <BR>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &#039;remember its position.<BR>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; swap_pos = x<BR>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; smallest_value = arrArray ( keyColumn, x )<BR>&nbsp; &nbsp; &nbsp; &nbsp; End If<BR>&nbsp; &nbsp; Next<BR><BR>&nbsp; &nbsp; &#039; now, if the smallest_value is *NOT* at the<BR>&nbsp; &nbsp; &#039; current position "row", then move it there!<BR>&nbsp; &nbsp; If swap_pos &#060;&#062; row Then<BR>&nbsp; &nbsp; &nbsp; &nbsp; For col = 0 To UBound( arrArray, 1 ) &#039; do every column!<BR>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; temp = arrArray( col, row )<BR>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; arrArray( col, row ) = arrArray( col, swap_pos )<BR>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; arrArray( col, swap_pos ) = temp<BR>&nbsp; &nbsp; &nbsp; &nbsp; Next<BR>&nbsp; &nbsp; End If <BR>Next<BR><BR>End Sub<BR>%&#062;<BR><BR>Does this now make more sense to you???<BR><BR>

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

## Wasn't me

BW,<BR><BR>Thanks for taking the time. Not sure why I am/was having such a mental block, perhaps vacation will get me back to my usual stupidity. <BR><BR>FYI, that single sort came from<BR>http://www.4guysfromrolla.com/webtech/011001-1.shtml<BR>(it wasn&#039;t you)<BR><BR>I put your code in and ran it, I am losing some of the columns, I will hopefully figure it out this weekend. <BR><BR>Thanks for the time. <BR>I *accept* your knowledge openly.

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

## Ah, Darren!

Well, Darren used some stuff I wrote in a message in one of the newsgroups or boards once upon a time (may have been this one, and was with my permission). But that was a while ago, and he was still learning. I need to get him to go fix it. Wish I&#039;d read it before; I would have straightened him out. He&#039;s got code from two different example kind of mixed up in that. <BR><BR>I&#039;ll see if I can get Darren and Scott to fix the code.<BR><BR>Thanks!<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
•