Final Bubble Sorting in Arrays using multi columns for sort values


The post develops ideas to allow extending the function to allow for sorting further using values from other columns for rows where duplicate values were in the column used initially
The start point is the "pseudo" Function
Sub SimpleArraySort4b(ByRef arsRef() ______)

Basic strategy
I will try to develop a recursion routine
It will have two main parts
Rem 1 Bubble Sort
This will be very similar to that code section in all the routines discussed so far. It is intended to sort a set of sequential rows in an array, the order determined by sorting values from a specific column. Initially it will be all rows in the array. There after it will work again but only on those rows which had identical values in the column chosen to sort by. As these duplicate rows will be sequential, it is just a case of the Bubble sort being done at any time over a specific sub set of the original rows.

Rem 3 determine duplicate rows range
This new section will work after every sort to determine the duplicate value rows. It will give us a list like " 4 5 6 " . This is the rows we organise Rem 1 to sort again using a different columns values.


Working Implementation
I propose some arguments , where possible similar to those in the VBA Range.Sort method , to aid in comparison along the lines of the VBA Range.Sort arguments and syntax for the single line case.
I will therefore change the signature line to this form
Code:
Sub SimpleArraySort6(ByVal CpyNo As Long, ByRef arsRef() As Variant, ByVal strRws As String, ByVal strKeys As String)
CpyNo : I want to use a recursion idea. The idea is that I "keep going" sorting by the values in different columns until I have an ordered list , like
A b D
A B e
A x 2
B w 4
B y z
f m h
F m m
f O t
In that simple example, duplicates in the first ordered column, are sorted by the second column, and the remaining duplicates ( the f m 's ) sorted by the final column. ( In this example all sorting is done in Ascending order )
To determine which column I am working on I will need to access the corresponding number in a list. This is the sort of thing that it would be useful to have the variable CpyNo for: In any recursion coding it is almost always useful to know which "level" you are in or "how many levels down" or , in terms of what is actually going on , which copy of the routine you are in.
Recursion is when a routine pauses, then another copy of the routine is started. This could continue for a number of "levels" or copies. I that last example we would have had finally 3 copies open at the same time.
This concept is very difficult to explain easily. Here is a simpler worked example ahdAHDHDLHDLKHLKHL

arsRef() : This is used exactly as in all our codings. It holds our entire array in whichever order it currently is. Each "level" or "recursion routine copy" corresponds to sorting by a specific column, and the array arsRef() past to be updated from one level to the other.

strRws : This looks like this initially " 1 2 3 4 5 6 7 " , but then as we identify duplicate rows in the last sort, it will change to like " 4 5 6 " which will then go through a similar sorting process and then might reveal other rows to further sort , such as in that example it might change to " 5 6 "

strKeys : In the VBA Range.Sort method , we discussed the arguments as being like pseudo_...
Range("A1:F10").Sort Key1:=column 2 , order1:=Ascending, Key2:=column 3 , order2:=Descending, ……
_.. so … I propose a simple string of this form
" 2 Asc 4 Asc 5 Desc ……….."
The number is the column number of the range to use in a sort "level" and the text determines ascending or descending order in the final sorted rows. So in that example arguments, the entire rows of the array are sorted based on the value in the second column, and if there are rows containing the same value in column 2, then those rows will be sorted using the values in column 4 .. etc…

Full routines walkthrough
In any use of the routine, it will need to be Called an initial time from another routine. At the Call line you will need to give the initial unsorted array , all rows for that array as a series of sequential integer numbers, your chosen key parameters , and a copy number of 1 for the recursion "copy" or "level".
Demo coding is given in Rem 1 of Sub TestieSimpleArraySort6() . This is just for demo purposes and is not part of the main recursion procedure issue.

Main recursion routine
Sub SimpleArraySort6(ByVal CpyNo As Long, ByRef arsRef() As Variant, ByVal strRws As String, ByVal strKeys As String)
http://www.excelfox.com/forum/showth...0994#post10994
The "copy number of the recursion" or "level down" should be supplied as 1 by the first Call, as is done in the demo testing code, Sub TestieSimpleArraySort6()
For any subsequent Calls of this recursion routine, the Calls are made by the routine itself. This is what happens in a recursion routine. ( This is why a recursion routine is often described as a routine that Calls itself. I personally find this miss leading. I would prefer to say that the routines pauses whilst it sets off another copy run of the routine. This other copy is then a separate routine, a new copy, which is completed before the original copy resumes. )
For any subsequent Calls of this recursion routine, the value supplied to the routine at the Call line is given as CpyNo +1. This results in the number being taken in the new independent copy variable of CpyNo as one greater for every "new copy" or "next level down" copy of the recursion routine. Hence this variable can be used to indicate "where we are". In our example it tells us which of the columns we are sorting by: This is because, as another copy is set off, it is done so in order to sort some duplicate sequential sub rows which were found by the last sort: When this occurs we want to take the next column in our supplied sort keys to try to order these sub rows.

Rem -1 from the supplied arguments, get all data needed in current bubble sort
I make an array from the supplied string of sort info argument , (strKeys _ = " 2 Asc 5 Desc 6 Asc….." based on a split by " " ( also allowing for any extra spaces like " 2 Asc 5 _ Desc 6 Asc….." ) )
In conjunction with the CpyNo I am then able to pick out the relevant column number and sort order info to suit the copy of the recursion routine currently running.
Finally in this section, I make a 1 D array from the supplied string of sequential row numbers " 3 4 5 6 ". I only need the upper and lower numbers in further calculations and lootings, , which I get later from the upper and lower limits of the array. ( I note that I could just as well do that form some string manipulation )

Rem 1 Simple Bubble Sort
This is almost exactly the code section used in the last few routines. The main difference is that the upper and lower row limits are those supplied in the strRws argument ( like " 3 4 5 6 " ) rather than the first row and last row of the full initial array to be sorted. By the first Call of the recursion routine, these will in fact be the first row and last row of the full initial array to be sorted. By any subsequent copy runs of the recursion procedure these will typically be some sub set of sequential rows that need to be resorted as the first sort had some rows where the value in the column used to base the sort on had the same value.

' Rem 3 Determine any duplicates in sort column values , and re run the routine to sort them by another column
This is the main new part which includes the recursion causing bits, so it is not going to be easy to explain or follow. I will try..
It is not easy to explain as it is all sort of mixed up: it is not easy to separate into bits that can easily be explained on their own.
At any time in this code section our current row is determined by rOuter. This variable had been used for the "outer loop" or "left hand" in the Bubble sort of Rem 1 Here it is doing something similar: keeping track of where we are as we go down the entire rows just sorted. We go down the entire rows just sorted to try to find a sub set of sequential rows with the same value in the column we used to sort by.
We use the variable strRws as we always do to hold our the indicies of a set of sequential rows. So we empty it (strRws = "" ) before we start.

We have a Loop **** for all the rows under consideration, ( actually we only loop to one less than all rows, as we look at the current row and the next one inside this Loop)
At each row we look to see if we have started a string of sequential rows and if not we start one at the current row
Then we look to see if in the sort column the next row is a duplicate, if it is we add it to our strRws. If it is not a duplicate we check to see if we have at this point a set of duplicate values, which will be indicated by if strRws has at least one separating " " in it. If this condition is met then we do the "recursion" ( ' Rec Call 1 ) . VBA recognises what we are trying to do now, so it pauses the current routine, and starts a new copy using the arguments we give it at Rec Call 1
The next copy of the recursion routine will ether sort the rows , or if it can't , the same process will happen again whereby that copy will pause and another will go off to try to sort the unsorted rows using the next column we chose. In any case eventually we should come bck to this original copy, and look further down as there might be another set of rows with the same value in the column used to sort.
That will solve the problem in almost all cases. Unfortunately my logic is a bit messy, and if rows at the end need sorting they got missed. So I had to add a section to take care of that situation. The logic there is similar to that previous


_._________________________________________

Final demo coding is here:

http://www.excelfox.com/forum/showth...0993#post10993
http://www.excelfox.com/forum/showth...0994#post10994
http://www.excelfox.com/forum/showth...0995#post10995



Demo example in next post…