VBA Range.Sort with arrays. Alternative for simple use.

[DocAElstein]

VBA Range.Sort with arrays. Alternative for simple use.

I am considering a fairly simple VBA Array alternative approach to a simple use of the available VBA Range.Sort Method. https://docs.microsoft.com/en-us/office/vba/api/excel.range.sort

In an important file of mine, I currently use the available VBA Range.Sort Method. https://docs.microsoft.com/en-us/office/vba/api/excel.range.sort
It lets me sort some long rows of data where the order is based on the values in one of the columns , then for any rows which have the same value in that column , those similar rows would be further sorted by another column. For the VBA Range.Sort Method the syntax terminology would say that I am using pseudo code of this sort of form
MyRange.Sort Key1:=use column1 , __ , Key2:=use columnx , __ , __ , __ ,
You can use up to 3 "Key 's" in the simple single code line.
In other words, if you have some identical values in the columns used to define the final list order, you can sort further using the values in a second column to determine the order in the group of identical values in the first column which you used. In the simple single line use of the available VBA Range.Sort Method, you can do that one more time, so 3 times in total .##
In other words, in that single code line it does , pseudo:
Sort by a column, x: (Key1=x)
Sort by a second column, y (Key2:=y), for those rows with duplicates in it in column x
Sort a final time by a third column, z (Key3:=z), for those rows with duplicates in it in both column x and column y

Here is a simple graphical illustration of what I am talking about: Consider this spreadsheet range:
Before:-
_____[/color ( Using Excel 2007 32 bit )
Row\Col
G
H
I
J
K

13Was G13CWas I13Was J13Was K13


14Was G14aWas I14Was J14Was K14


15Was G15gWas I15ce


16Was G16gWas I16bWas K16


17Was G17gWas I17cf


18Was G18GWas I18cWas K18


19Was G19fWas I19Was J19Was K19
Worksheet: Sorting
I will run this simple procedure, ( which is in a normal code module. When I run the procedure the worksheet with Name "Sorting" is active).

Sub RangeSortExample()
Range("G13:K19").Sort Key1:=Range("G13:K19").Columns("B:B"), Order1:=xlAscending, Key2:=Range("G13:K19").Columns("D:D"), order2:=xlAscending, Key3:=Range("G13:K19").Columns("E:E"), order3:=xlDescending, MatchCase:=False
End Sub

Here is a break down of what that routine does:
The first key ,
Key1:=range("G13:K19").Columns("B:B"), Order1:=xlAscending , MatchCase:=False
, results in this
_____ ( Using Excel 2007 32 bit )
Row\Col
G
H
I
J
K

13Was G14aWas I14Was J14Was K14


14Was G13CWas I13Was J13Was K13


15Was G19fWas I19Was J19Was K19


16Was G15gWas I15ce


17Was G16gWas I16bWas K16


18Was G17gWas I17cf


19Was G18GWas I18cWas K18
Worksheet: Sorting
The above screen shows that we have all ordered ( based on the column 2 ,( "B" columns ), of that range , ( which is the column H of the spreadsheet) ). But note, we have duplicates in column 2 with values of g in the last 4 rows.
This next part ,
Key2:=range("G13:K19").Columns("D:D"), order2:=xlAscending, MatchCase:=False
, then sorts those last 4 rows using column 4, (columns "D") of that range values. ( This is the spreadsheet columns "J")
_____ ( Using Excel 2007 32 bit )
Row\Col
G
H
I
J
K

13Was G14aWas I14Was J14Was K14


14Was G13CWas I13Was J13Was K13


15Was G19fWas I19Was J19Was K19


16Was G16gWas I16bWas K16


17Was G15gWas I15ce


18Was G17gWas I17cf


19Was G18GWas I18cWas K18
Worksheet: Sorting
In the above screen shot we see that we now have 3 rows containing all gs in the second column, and all cs in the forth column.
The final term is, noting that we are using xlDescending (just for fun :) ) ,
Key3:=range("G13:K19").Columns("E:E"), order3:=xlDescending
So the last 3 rows are resorted such as to give in those last 3 rows a descending order in the values in the 5th column in those last 3 rows:
_____ ( Using Excel 2007 32 bit )
Row\Col
G
H
I
J
K

13Was G14aWas I14Was J14Was K14


14Was G13CWas I13Was J13Was K13


15Was G19fWas I19Was J19Was K19


16Was G16gWas I16bWas K16


17Was G18GWas I18cWas K18


18Was G17gWas I17cf


19Was G15gWas I15ce
Worksheet: Sorting

Notes:
For simplicity I chose to be case insensitive ( so like g = G __ - _ MatchCase:=False )
Some other argument options are available with the available VBA Range.Sort Method. I will not consider those.
## Furthermore, If you add the options on other lines then use a .Apply code line to apply the sort, then you can have more than 3 "Keys"

I am only considering a comparison to the simple single line using minimal options similar to the worked example

Why do an array alternative?.
I cant think of a particularly good reason: The VBA Range.Sort Method appears to be regarded as a good way to do these things, and often a solution for sorting arrays is based on pasting into a worksheet Range , then using the Range.Sort on that and then capturing the sorted range back into the original range
But I was just interested, out of general interest, to have an alternative: it often occurs that you find bugs in Excel things. The more you have control of "what is going on" , as you do with an array approach, the less is "hidden" from you, such that you have a better chance to change something id something goes wrong.

Various sorting methods, mathematical ways to sort..
The various methods of sorting seem immense and need a good understanding of advanced mathematics. I can't begin to discuss any of that. I will use exclusively the simply "Bubble sort method" , which I will explain from the simplest form, and then adapt to our case

The next posts will go through the simplest Bubble sort theory as applied to a simple 2 dimensional array, and then progress in the following posts to a Function to do similar to the simple case of a VBA Range.Sort Method with the possibility to add keys, for sorting further when values in the initial column used for sorting are identical
For ease of explanation I will refer to the first dimensions in an array as the rows, and the second dimensions as columns , pseudo such that an array could be thought of as a spreadsheet of values. For example a 2 dimensional array of 5 x 2 could be pictorially considered as 5 rows x 2 columns:

r1, c1r1, c2

r2, c1r2, c2

r3, c1r3, c2

r4, c1r4, c2

r5, c1r5, c2

[DocAElstein]

My simple sort idea, Simple Bubble Sort
The basic idea as far as I can tell is like , consider this example, for simplicity I will consider
In the procedures below, the first part , Rem 0 , will typically be just some way to get an Array of data for demonstration purposes. They are not part of the main theme of sorting. Also I use WsS just to be sure I am using the correct worksheet in my File. Those can be removed if you have the coding in a normal code module and want the coding to work on the active worksheet that you have "in front of you" , in other words the one that you are "looking at".
Alternatively, 'Comment out all references to the worksheet and test range, Rng, and use the code line alternative to capture the range you have selected.
__ Set RngToSort=Selection ' Selection.JPG: https://imgur.com/HnCdBt8
( We could sort the inputted array, arrTS() , and re paste that out. I have chosen to make a copy of the original array, arrOut() and sort that . I cant think of any reason to do that off the top of my head, other than maybe, in a final code you would still have a copy of the original unsorted data)
Also, Rem 2 is not part of the main demo. This simply pastes out the sorted array next to the original inputted range for easy comparison

Here we go….
Rem 1 Simple Bubble Sort ' =========="Left hand"====Outer Loop==
Simple worded description
Quick Explanation
_We take each row in turn. Start at row 1. Put the value from it "in our, (say left) hand". Actually "in our hand" just means the value at any time in the array element corresponding to the row we are looking at.
___'---------------"Right Hand"----Inner Loop-------
___We then go along all the other rows, (say point at them with our right hand). We look at those next rows in turn, and if we find that the row value "in our (left) hand" is bigger than a row we are pointing at with our right hand, we swap so that we have the smaller "in our (left)hand". Finally "in our (left)hand" will be the smallest. The first time we do this , it will mean that finally the value in the array element corresponding to the first row will have the smallest value in it.
(Remember "in our (left)hand" is the value at any time that is in the ( first initially), row. That value might be change at one or more occasions because it was bigger than the one we compared it with)
We then move on to the next row (with out left hand), and do a similar thing, so that will end up causing the value in the array element corresponding to the second row having the next smallest value in it. So finally the order looking down will be from smallest to largest.

Another "Hand"y way to explain
As it is a tricky concept to get the first time around I will say exactly the same again, just slightly differently:
Say I have a few rows with different values in them .. Grab the first row value in the left hand. Or rather hold it at the first row. Grab the second row value in the right hand, or rather hold it at the second row in the right hand. If the value at your left hand is > than that at the right then swap the values. Your left hand stays at the first row. Your left hand might be holding the original value or a different smaller value.
The right hand should now "let go" of the second row value, and move one row down to hold the third row value. If the value at your left hand is > than that at the right then swap the values. Your left hand stays at the first row.
The right hand should now "let go" of the third row value, and move one row down to hold the forth row value. If the value at your left hand is > than that at the right then swap the values. Your left hand stays at the first row.
As you continue doing this it will result in the smallest value ending up at your left hand, which is at the first row.
Once all rows have been looked at by your right hand, then:
Row one needs no more looking at , as it has the lowest value.
So move your left hand down to row 2, and move your right hand down to row 3.
Your left hand now stays at row 2, whilst your right hand move down all the rows. At each row the values at both hands are compared. If the value at your left hand is > than that at the right then you swap the values.
As you continue doing this it will result in the second smallest ** value ending up at your left hand, which is at the second row. (** Or if you like next number up in value from the smallest will end up at the second row ).

Swapping wth a computer
Inside any sort of coding or this sort of sorting you will need to do that swapping of two values. As far as I know, no coding can swap two things simultaneously

[DocAElstein]

Minor Modifications advancements to simple Bubble Sort Routine

Change Ascending/Descending
For the simple example so far we chose Ascending order, which means that the things "get bigger as you go down the rows"
To reverse this to descending so that things "get smaller as you go down the rows", you simply need to change
the > to a < in the array routine
and
the Order1:=xlAscending to Order1:=xlDescending in the VBA Range.Sort routine
http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=10979#post10979

I intended developing the solution into a function, so as a start to this, the routine will be modified to take an Optional argument of 0 or 1 , with the default of 0 being the case for an Ascending list. I am not being particularly efficient with the coding, and will duplicate sections thus

Rem 1 Simple Bubble Sort
Dim rOuter As Long ' ========"Left Hand"=====================Outer Loop=====================================
For rOuter = 1 To UBound(arrTS(), 1) - 1 ' For row 1 to the (last row -1) last row, given by the first dimension upper limit of the array
Dim rInner As Long ' -------Inner Loop-------------"Right Hand"--------------------------
For rInner = rOuter + 1 To UBound(arrOut(), 1)
'If arrOut(rOuter, Clm) > arrOut(rInner, Clm) Then ' This means that I am bigger than the next. So I will swap . I keep doing this which will have the effect of putting the smallest in the current rOuter. By the this and all next rOuter, I miss out the last, and any previous, which means I effectively do the same which puts the next smallest in this next rOuter.
If GlLl = 0 Then ' We want Ascending list
'If UCase(CStr(arrOut(rOuter, Clm))) > UCase(CStr(arrOut(rInner, Clm))) Then
If UCase(CStr(arrOut(rOuter, Clm))) > UCase(CStr(arrOut(rInner, Clm))) Then
Dim temp As Variant ' I want to Swap those 2 above - I cant easilly in any coding change two values simulataneosly. So one of them Element values will put in this temporary place. This Element Values will then be given the other. Finally the other Element will be given this temporary value
Dim Clms As Long '-------| with the condition met a loop is done for all columns in the array in which those two values used in the comparison are replaced at each column
For Clms = 1 To UBound(arrOut(), 2)
Let temp = arrOut(rOuter, Clms): Let arrOut(rOuter, Clms) = arrOut(rInner, Clms): Let arrOut(rInner, Clms) = temp
Next Clms '----------| for each column in the array at the two rows rOuter and rInner
Else
End If
Else ' GlLl is not 0 , so presumably we want Descending list
If UCase(CStr(arrOut(rOuter, Clm))) < UCase(CStr(arrOut(rInner, Clm))) Then
'Dim temp As Variant ' I want to Swap those 2 above - I cant easilly in any coding change two values simulataneosly. So one of them Element values will put in this temporary place. This Element Values will then be given the other. Finally the other Element will be given this temporary value
'Dim Clms As Long '-------| with the condition met a loop is done for all columns in the array in which those two values used in the comparison are replaced at each column
For Clms = 1 To UBound(arrOut(), 2)
Let temp = arrOut(rOuter, Clms): Let arrOut(rOuter, Clms) = arrOut(rInner, Clms): Let arrOut(rInner, Clms) = temp
Next Clms '----------| for each column in the array at the two rows rOuter and rInner
Else
End If

End If ' End of Ascending or Descending example
Next rInner ' ---------------------------------------------------------------------
Next rOuter ' ================================================== =========================================
Rem 2 Output for easy of demo
Here is a complete routine with calling procedure: http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=10981#post10981

Text or Numbers
It appears from the example tried, that the VBA Range.Sort routine recognizes numbers as numbers and then sorts as we might expect numbers to be sorted.
We can modify slightly our array routine to give similar results.
This is done by doing a comparison based on looking at numbers if at any time the two values under consideration can be seen by VBA as numbers.
The IsNumeric() function is used. If both values are seen to be numeric then they are compared as numbers. This would probably happen from a simple comparison of the values, but just to be on the safe side, we do an extra convert to Double via CDbl( ) in this sort of form
___ If IsNumeric(arrOut(rOuter, Clm)) And IsNumeric(arrOut(rInner, Clm)) Then ' Numeric case
_____ If CDbl(arrOut(rOuter, Clm)) > CDbl(arrOut(rInner, Clm)) Then

Once again, I am not coding particularly efficiently , and so I duplicate sections
Here is a complete routine with calling procedure: http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=10982#post10982
Here are some example results: http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=10983#post10983


The next post will make a few minor simplifications to develop further a general purpose Function.

[DocAElstein]

Conversion to Array sort function

From now on, I am making up a lot of this as I go along, so I doubt it will bee the most efficient.

General considerations.

_ Always reference the same array
My long term plans on the final version of this is that the routine will work in some recursion process, that is to say , copies of the function will be going further and further to sort further for rows where the last sort left some duplicated values in the column used to sort by. Those copies of the Function would be called from the function itself. That is the simple explanation of hat recursion is about , as applied to VBA Functions: the function starts, then it pauses, whilst it set off another run of the Function to go off and do something similar to what the first function was doing.
One thing that is always a bit awkward to do is pass the array being sorted through the different copies of the function. So what is normally done, and what we will do is have an array outside the function, ( either in an initial calling routine or at the top of the code module )
In the signature line , ( declaring first line) , of the Function it will be defined as being taken ByRef This means that within the Function we refer specifically to that array. This means that changes made in that Function will be reflected directly by those changes into the array we have outside: To a first approximation we can think of it as thought we physically take the array into the function and do stuff to that array, then put it back outside when we have finished. ( The alternative way that we will not use would be to take the array into the function , ByVal , as its name suggests, takes the values of the array at the time into a copy variable , ( sometimes referred to as a local copy variable ), and all is done inside the function on that copy variable , which ceases to exist when the function Ends. The values inside the original array outside are not effected in the ByVal case.

Simple pseudo code examples of difference between ByRef and ByVal:

Pseudo code ByRef ' ( Usually default option )
varMyArray = x
_ Call ReferToIt(varMyArray)
Sub ReferToIt(ByRef arr)
_ arr=y ' This is similar to saying varMyArray = y
End
varMyArray is now = y ' because effectively varMyArray was in arr

Pseudo code ByVal
varMyArray = x
_ Call TakeValue(varMyArray)
Sub TakeValue(ByVal arr)
_ arr=z
End
varMyArray is still = x ' effectively just a copy of varMyArray was in arr , and this copy was thrown away at the End of the routine. So nothing done to arr could have any effect on varMyArray in the ByVal case



Sort only consecutive rows
I am anticipating wanting to sort a number of consecutive rows in the array. On the first run this will be all rows, like for a 5 row array _ 1 2 3 4 5 if the
If the sort based on the values in an initial column found that the third and forth row had a similar value in that column, then a sort based on another column would need in comparison 3 4.
So I am suggesting a strategy could be to have the function do the sort routine based on consecutive rows given in some way, such as in a text string.




The next posts will look at implementations of the general ideas above

[DocAElstein]

Transformation to a Function or Pseudo Function Sub routine

The transformation to a Function we can do easily in two simple steps.
Step 1 The routine is changed so that it takes the array to be sorted into it
The calling routine and main routine here http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=10987#post10987 are almost the same as the previous. The only difference is that the array to be sorted, is now outside the main routine and is taken into the main routine at the main routine signature line
Sub SimpleArraySort4(ByRef arrTS() As Variant, Optional ByVal GlLl As Long)
( A few unused 'commented out lines have also been removed )

Because we are using ByRef , the previous testieing Calling routine can also use the original supplied array, arrTS() , after the main procedure Call , provided that the array taken in at the signature line is that sorted. If this is the case, then , in effect , the array taken in and the array in the routine are the same array, and changes done to the array in the function will be reflected in the array outside.
We discussed this previously. Just to recap:
Pseudo code ByRef ' ( Usually default option )
varMyArray = x
_ Call ReferToIt(varMyArray)
Sub ReferToIt(ByRef arr)
_ arr=y ' This is similar to saying varMyArray = y
End
varMyArray is now = y ' because effectively varMyArray was in arr

Often in learn material the basic difference between a simple Function procedure and a Sub routine procedure is said to be that a Function returns a value. In fact , as we see from above, the use of ByRef actually allows us effectively to return a value from a simple Sub procedure.

In the version Sub TestieSimpleArraySort4b() and Sub SimpleArraySort4b(__) here , http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=10988#post10988 we are using the effect of ByRef to give us a pseudo Function : To achieve this pseudo Function status, all the array references within the routine are changed to that variable taken into the routine at the signature line.
( This variable name can be anything, I will use arsRef() from now on for all references in the main routine). Doing this allows us to remove all the demo range code lines from the procedure: They can be placed outside and then we can use the array which we declare initially outside the routine, ( arrTS() ) , both before and after the procedure Call: Before it is used to feed the data to be sorted, and After the Call it had been filled with the sorted results , so can be used to paste out the sorted results alongside the original range to compare for demo purposes. To all intents and purposes, arrTS() can be considered as arsRef() and visa versa. This characteristic has been achieved by the use ByRef: Any use of arsRef() is simultaneously Referring to



Step 2 Function instead of Sub ???
I am doing this for completeness. But because I am doing things ByRef I don't need to do this. I may not use it finally
Often in learn material the basic difference between a simple Function procedure and a Sub routine procedure is said to be that a Function returns a value.
This can be explained as follows.
The signature line differs from a normal Sub routine in that it has the form similar to a Dim declaration code line. Consider such a simple Dim line:
_ Dim __ MyThing __ As Variant
A signature line for a Function could look like
Function MyThing(Arg) _ As Variant
In both cases you effectively have a variable of the specified type. The way you fill the variable is slightly different. In the simple case you fill it with something
__ MyThing = "42"
For the function you need to Call it to fill it, and unless the Argument is optional, then you must give it
_ MyThing_ "42"
or
_ Call MyThing("42")
Because the function usually ( you can decide, it does not have to !!!!! ) returns a value in a variable of the type you specify , the most typical use would look like
MyOtherThing = MyThing("42")
The returned value is then put in the variable MyOtherThing

At the end of the day, the main reason for using a function is given as that of returning a value of a specific type.
( By the way, once you have such coding written in a File , then in addition to using it in VBA,,then you could sometimes use it in a cell: Writing = MyThing("42") would often work to return you a value in the cell , resulting from what your function MyThing did with "42" )
If we declared our function as type Variant at the signature line, then that would allow us to return our sorted array, since a Variant variable can hold an array.

The reason why I may not finally use this is that by virtue of using ByRef, my Sub routine will also effectively return that array.
Within the Function itself, the Function MyThing, is like an unfilled variable , in this case a Variant variable. As it is unfilled it will not return anything. !!!!! So within the function, typically towards the end once we have finished doing all we wanted to do, we would have a code line like
Function MyThing(Byref ArrToSort() , ByVal aArg, Optional ByVal GlLl As Long) As Variant
' arrOut() = ArrToSort()
' Do stuff like sort the array
_ MyThing = arrOut()
End Function
The function is then "filled" with what it would return when called

Transformation of pseudo Function Sub SimpleArraySort4b(ByRef arsRef() As Variant, Optional ByVal GlLl As Long) to Function SimpleArraySort5(ByRef arsRef() As Variant, Optional ByVal GlLl As Long) As Variant

Let us note once more that this final step is not necessary: The last procedure fulfils all are current requirements of taking an array and sorting that array, and effectively giving it back to us. It does this by virtue of the array being taken ByRef
The true Function way is more conventionally used so I am doing it just for convenience

The convention way to use the Function would be to arrange that our sorted array is returned by the function. As our current routine stands we only need to add one code line to do this. This code line, placed towards the end of the Function would be
__SimpleArraySort5 = arsRef()
To explain:
The signature line .._
Function SimpleArraySort5(_____) As Variant
_.. Has a very similar effect to this line:
_ Dim _ SimpleArraySort5 _ As Variant
So SimpleArraySort5 could be thought of , to a first approximation , as a pseudo variable
The only difference is that when VBA ( or Excel following a = , like in = SimpleArraySort5 , in a cell ) "sees" SimpleArraySort5 , it will go off and do whatever is inside the function. At the End of the function, the value inside the pseudo variable will be empty still. If we do nothing else then the Function can be Called in exactly the same way that a normal sub routine can
_ Call SimpleArraySort5(___)
However, filling the pseudo variable , for example via code line SimpleArraySort5 = arsRef() , allows us the extra possibility of a code lines like
_____ = = SimpleArraySort5(____)
_ MySortedArray= SimpleArraySort5(MyUnsortedArray)
The type chosen in the declaring signature line must be compatible with what you are doing. In our case, we want to return an array. The only variable type that can hold/ pass an array is a Variant. Hence we chose Variant in this case

( because VBA works generally backwards when reading a code line, we could, in our case , use the same variable such
_ MyUnsortedSortedArray= SimpleArraySort5( MyUnsortedSortedArray )
_ arrTS() = SimpleArraySort5( arrTS() ) )

In the routines
Sub TestieSimpleArraySort5() and Function SimpleArraySort5(______) As Variant
http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=10989#post10989 , the main difference over the previous routines is the extra As Variant at the signature line, and finally a code line just before End Function of SimpleArraySort5 = arsRef()

In the testieing routine, we use codes line of this form in the conventional way in which a function is typically used.
_ arrTS() = SimpleArraySort5(arrTS(), _ 0 _ )
But we note that by virtue of using ByRef a simple call would surfice
_Call SimpleArraySort5(arrTS(), _ 0 _ )

Note: we have added an extra testing code section '2b)
In this extra section we fill a new array, arrDesc() , with the sorted array in Descending order. We use for demo purposes a typical function using code line
_ arrDesc() = SimpleArraySort5(arrTS(), 2136)
Correspondingly we have a demo output giving code line
_ RngToSort.Offset(0, RngToSort.Columns.Count * 3).Value = arrDesc()

We note further, that this is somewhat redundant. This is because the code part SimpleArraySort5(arrTS(), 2136) has the effect of re filling arrTS() with the newly sorted array by virtue of the use of ByRef in the signature line of the Function
We could therefore simply use a code line like _..
_Call SimpleArraySort5(arrTS(), 357)
_.. followed by an demo output giving line of
_ RngToSort.Offset(0, RngToSort.Columns.Count * 3).Value = arrTS()





We mow have a simple Array Bubble sort function
The next posts will develop 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
I will likely start from the "pseudo" Function
Sub SimpleArraySort4b(ByRef arsRef() ______)

[DocAElstein]

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

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/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=10994#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/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=10993#post10993
http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=10994#post10994
http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=10995#post10995



Demo example in next post…

[DocAElstein]

Take an example,
A list of Foods, their name in first column and a few other things like calories(Kcal) and Salt content in other columns

First I want to sort to group similar products (based on alphabetical order, but ascending or descending is not important) - This will be sorting on column 1 values

Within similar food types, I want to list them in an order of how healthy they might be, ( or at least in the order of least unhealthy ) .
Most important would be order starting with lowest Kcal.
After that for similar products with similar Kcal , we would consider the minimum salt content as likely to be the less unhealthy.

This might be our list
_____ ( Using Excel 2007 32 bit )
Row\Col
R
S
T
U
V
W

22Food ProductWas S22KcalWas U22SaltWas W22


23CrispsWas S23
500Was U23
0.7Was W23


24Beer Was S24
200Was U24
0.1Was W24


25WineWas S25
150Was U25
0.15Was W25


26BeerWas S26
200Was U26
0.07Was W26


27beerWas S27
220Was U27
0.2Was W27


28BeerWas S28
210Was U28
0.06Was W28


29WineWas S29
160Was U29
0.04Was W29


30wiNeWas S30
150Was U30
0.03Was W30


31CrispsWas S31
502Was U31
2Was W31


32Onion RingesWas S32
480Was U32
1Was W32


33Onion RingesWas S33
490Was U33
1.5Was W33


34CrispsWas S34
502Was U34
1.5Was W34


35CRISPSWas S35
500Was U35
1.1Was W35


36WineWas S36
170Was U36
0.1Was W36


37CrispsWas S37
500Was U37
3Was W37
Worksheet: Sorting


Here is a demo Calling test routine


Sub TestieSimpleArraySort6()
Rem 0 test data, worksheets info
Dim WsS As Worksheet: Set WsS = ThisWorkbook.Worksheets("Sorting")
Dim RngToSort As Range: Set RngToSort = WsS.Range("R23:W37")
' Set RngToSort = Selection ' ' Selection.JPG : https://imgur.com/HnCdBt8
Dim arrTS() As Variant: Let arrTS() = RngToSort.Value ' We would have to use .Value for a range capture of this sort because .Value returns a field of Variant types. But also at this stage we want to preserve string and number types
' Call SimpleArraySort6(1, arrTS(), " 1 2 3 4 5 ", " 1 Asc 2 Asc 3 Asc")
Dim cnt As Long, strIndcs As String: Let strIndcs = " "
For cnt = 1 To RngToSort.Rows.Count
Let strIndcs = strIndcs & cnt & " "
Next cnt
Debug.Print strIndcs ' For 5 rows , for example we will have " 1 2 3 4 5 " , for 15 rows " 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 "
Call SimpleArraySort6(1, arrTS(), strIndcs, " 1 Desc 3 Asc 5 Asc")
Rem 2 Output for easy of demo
' 2a
RngToSort.Offset(0, RngToSort.Columns.Count).Clear
Let RngToSort.Offset(0, RngToSort.Columns.Count).Value = arrTS()
Let RngToSort.Offset(0, RngToSort.Columns.Count).Interior.Color = vbYellow
' 2b VBA Range.Sort Method equivalent
Dim TestRngSrt As Range: Set TestRngSrt = RngToSort.Offset(0, RngToSort.Columns.Count * 2)
TestRngSrt.Clear
Let TestRngSrt.Value = RngToSort.Value
TestRngSrt.Sort Key1:=TestRngSrt.Columns("A:A"), order1:=xlDescending, Key2:=TestRngSrt.Columns("C:C"), order2:=xlAscending, Key3:=TestRngSrt.Columns("E:E"), order3:=xlAscending
TestRngSrt.Interior.Color = vbGreen
End Sub
'

That above routine uses the test range R23:W37 above and feeds that to the main recursion routine ( http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=10994#post10994 )











Full demo example is shown here
Final demo coding is here:
http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=10993#post10993
http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=10994#post10994
http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=10995#post10995

some more notes here
http://www.eileenslounge.com/viewtopic.php?f=30&t=31691&p=245517#p245517



Ref
https://stackoverflow.com/questions/41740000/sorting-on-four-columns

[DocAElstein]

Example: A few thousand columns with a few dozen rows

A requirement I have is to re sort occasionally a sub set of rows in a large list of very wide rows ( approx 3500 columns ). Typically I have sections of 50 rows which “belong together”, ( 49grouped + 1 below ) , and things may be added. It is easier to add anywhere in the section and then do a sort on the entire group, or part thereof , as required..
49grouped+1.JPG : https://imgur.com/a8rflEn
The Range.Sort method works well, so the array version is just a spare alternative…

Range.Sort method coding.
A full description is given here:
http://www.excelfox.com/forum/showthread.php/2312-Appendix-Thread-Diet-Protokol-Coding-Adaptions?p=11032&viewfull=1#post11032
http://www.excelfox.com/forum/showthread.php/2312-Appendix-Thread-Diet-Protokol-Coding-Adaptions?p=11033&viewfull=1#post11033
http://www.excelfox.com/forum/showthread.php/2312-Appendix-Thread-Diet-Protokol-Coding-Adaptions?p=11034&viewfull=1#post11034
http://www.excelfox.com/forum/showthread.php/2312-Appendix-Thread-Diet-Protokol-Coding-Adaptions?p=11035&viewfull=1#post11035

Here is just a brief Description of the Range.Sort method coding:
The range to sort, rngToSort , is determined. It is basically taken as the user selected range on the active worksheet in the active window. Because there are a lot of columns, and the number is known ( 1 – 3488 ) , these are hardcode, and the user only needs to make a selection of any columns over the required rows: The selection determines the row range and the column range is hard coded.
An array, ArrrngOrig() , is made of the current range values.
This can be used if it is decided not to accept the sorted range and instead to replace the original range order.
In the Array version alternative this will be the array to be given to the sort routine.
The main code line doing the Range.Sort is

rngToSort.Sort Key1:=wksToSort.Columns("H"), order1:=xlDescending, Key2:=wksToSort.Columns("J"), order2:=xlDescending, Key3:=wksToSort.Columns("X"), order3:=xlDescending

The relevant arguments from there that we need to feed to our array sort routine are therefore
Key1:=wksToSort.Columns("H"), order1:=xlDescending
Key2:=wksToSort.Columns("J"), order2:=xlDescending
Key3:=wksToSort.Columns("X"), order3:=xlDescending
The array to be given can be assigned directly to
ArrrngOrig()

Array sort routine alternative coding( revision of our current coding )
Here is our Main recursion Array sort routine http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=10994#post10994
Sub SimpleArraySort6(ByVal CpyNo As Long, ByRef arsRef() As Variant, ByVal strRws As String, ByVal strKeys As String)
Because this is a recursion routine, it need an initial routine to Call it the first time and feed it the appropriate arguments.
It will need to supply :
CpyNo : This is is very useful for debugging to help tell us which copy of the routine is running at any time. We have seen that this is incremented every time a routine pauses and sets off another copy. The increment is done as the new copy is started, and that incremented value passed to the newly starting routine, so CpyNo will be an indication of the copy running. So this we will need to set to one at the initial Call done by the initial Calling routine to 1
Also it is needed to pick out the correct sort key, in other words to set the copy of the routine to sort based on the correct column: Each further copy of the recursion routine uses the next column of values to be used in the progressive sorting.
arsRef() : We can declare a dynamic Variant array and assign our range capture array , ArrrngOrig() , directly to this. Lets call this arrTS() for consistence to our previous discussions
strRws : This is used to give the indices of the array, ( in a string form ) of all rows currently being sorted. On the initial Call this will need to have all the rows and be of this form “ 1 2 3 4 5 6 ……….. N “. We will need to make this in the Calling routine
strKeys : This takes a form to look similar to the arguments of the Range.Sort method. Because we are looking from column A , our columns follow the spreadsheet columns. All our order is Descending. This would be our basic form required, which will be passed unchanged between copies of the recursion routine. ( We pass this ByValue , but we could just as well uses ByRef for this variable )
In our initial Calling routine we will need a code line of this form
strKeys = “ 8 Desc 10 Desc 24 Desc “

Calling initial routine
The routine is here http://www.excelfox.com/forum/showthread.php/2312-Appendix-Thread-Diet-Protokol-Coding-Adaptions?p=11038&viewfull=1#post11038
The first 3 section, Rem 0 Rem 1 Rem 2 , and final section Rem 4 are not directly relevant to the sort routine, and are very similar to the same code sections in the Range.Sort routine described here: http://www.excelfox.com/forum/showthread.php/2312-Appendix-Thread-Diet-Protokol-Coding-Adaptions?p=11035&viewfull=1#post11035
Rem 3
'3a) arguments for Called routine
A simple loop produces our string of all row indices, strIndcs
An array is made containing the initial unsorted range to be passed to the recursion routine, arrTS()
The remaining arguments of recursion routine copy number, CpyNo , abd the keys required, strKeys , are passed hard coded directly in the next section, '3b)
'3b) ( '3c) alternative**) This is the main equivalent to the Range.Sort method
The main recursion routine is Called and after the range is given the array of sorted values
**‘3c) is an alternative which comes closer to resembling the single line Range.Sort method , for comparison:


rngToSort.Sort Key1:=wksToSort.Columns("H"), order1:=xlDescending, Key2:=wksToSort.Columns("J"), order2:=xlDescending, Key3:=wksToSort.Columns("X"), order3:=xlDescending

'3c)
arrTS() = rngToSort.Value: Call SimpleArraySort6(1, arrTS(), strIndcs, " 8 Desc 10 Desc 24 Desc "): rngToSort.Value = arrTS()

here again, pseudo form, for ease of comparison:

rngToSort.Sort
___________Key1:=wksToSort.Columns("H"), order1:=xlDescending,
_________________Key2:=wksToSort.Columns("J"), order2:=xlDescending,
_______________________Key3:=wksToSort.Columns("X"), order3:=xlDescending

‘ 3c)
arrTS() = rngToSort.Value
Call SimpleArraySort6(1, arrTS(), strIndcs,
__________________________" 8 Desc 10 Desc 24 Desc ")
rngToSort.Value = arrTS()


( For a spreadsheet, for the letter across the top,
_____________H is column number 8 , J is column number 10 , X is column number 24 )

Here is the file used do far:
"ProAktuellex8600x2Sort1.xlsm" https://app.box.com/s/d6isabudadt3swnryxiz7motspzeqa17

Here are some timing experimants:
http://www.excelfox.com/forum/showthread.php/2306-Just-testing-Testing-some-sort-routines-No-reply-needed?p=11041&viewfull=1#post11041
http://www.excelfox.com/forum/showthread.php/2306-Just-testing-Testing-some-sort-routines-No-reply-needed?p=11042&viewfull=1#post11042
http://www.excelfox.com/forum/showthread.php/2306-Just-testing-Testing-some-sort-routines-No-reply-needed?p=11043&viewfull=1#post11043
http://www.excelfox.com/forum/showthread.php/2306-Just-testing-Testing-some-sort-routines-No-reply-needed?p=11045&viewfull=1#post11045
http://www.excelfox.com/forum/showthread.php/2306-Just-testing-Testing-some-sort-routines-No-reply-needed?p=11046#post11046

[DocAElstein]

I am thinking of an alternative approach, the idea being to reduce on the steps to reorganising the array at every swap stage.. the idea came from 2 things …

_1 The recursion routine is fed currently the row indices of the rows that need to be sorted.
_2 We can use the VBA Application.Index Method which allows us to re sort an array “ in one go “ via a code line like , pseudo formula..

arrOut() = App.Indx( ArrIn() , {1;3;2} , {1,2,3} )
arrOut() = App.Indx( ArrIn() , row indices , column indicies )
arrOut() = App.Indx( ArrIn() , Rs() , Cms() )

The above code line would change an ArrIn() like this …_
A b c
D e f
G h I
_ … to a given output in arrOut() like this:
A b c
G h I
D e f

So the idea is that we sort the indices values, and then re apply the formula above

At this stage I propose modifying the existing code so as to have a better chance of a direct comparison in performance.. Both will be then subject to similar general inefficiencies arising from the very opened out explicit form of the codlings generally. I will do two version of this code, Sub SimpleArraySort7( and Sub SimpleArraySort8(
Sub SimpleArraySort7( will add the extra coding, and Sub SimpleArraySort8( will remove some of the now unnecessary / redundant parts thereafter, so as to attempt a good comparison to the previous Sub SimpleArraySort6(
Finally I may make a more trimmed version

Here the basic modifications for Sub SimpleArraySort7(__ , Sub TestieSimpleArraySort7()

Global Variables
To help simplify the comparison and so reduce the changes to the routines, I will have a some Global variables at the top of the module and outside any routine for the row and column indices
Dim Cms() As Variant, Rs() As Variant
This will allow me to refer to, that is to say change and use, in any copy of the recursion routine. (It would also be an alternative place here at the top of the module and outside any routine for our main array, arrTS(): we could then always refer to this, and then not need the ByRef arsRef() at the signature line of the recursion routine. But for now I will leave that as it is for closer comparison of the routines. )
These two “single width” arrays,
_ Rs() , “vertical, rows”
and
_ Cms() , “horizontal columns”
, will hold whole number indices for use in the VBA Index method formula idea.
For the type of the elements of these arrays, the Long type would be OK, and also in such situations VBA usually accepts Sting types that look like an number. The only reason that I use Variant is that I use a convenient way to get the initial indices, and that way happens to return a field of Variant types
To help in the development of this coding and to help with the explanation here, I have also moved the variable for the test range, RngToSort , to the top of the module and outside any routine to make it a global variable: This way I can use multiples of it’s dimensions to position intermediate paste outs of the arrTS(). For example , I have added a section immediately after the end of the main outer loop == for sorting, ' Captains Blog, Start Treck , which pastes out the current state of the sorted array , arrTS(), along with the current state of the indices, Rs()

' Captains Blog, Start Treck
Let RngToSort.Offset((RngToSort.Rows.Count * (CopyNo + 1)), 0).Value = arsRef()
RngToSort.Offset((RngToSort.Rows.Count * (CopyNo + 1)), -1).Resize(UBound(Rs(), 1), UBound(Rs(), 2)).Value = Rs()
Debug.Print " Running Copy " & CopyNo & " of routine." & vbCr & vbLf & " Sorted rows " & strRws & " based on values in column " & Clm & vbCr & vbLf & " Checking now for Dups in that last sorted list" & vbCr & vbLf
For Sub SimpleArraySort7(__ , I will also include a new array variable , as a global variable, arrIndx(). This I will fill by the formula line of
arrIndx() = Application.Index(arrOrig(), Rs(), Cms())
The formula above needs to be applied to the original range, so I also have another global variable which will contain the original range, arrOrig()
The final output, for example from the first passing of this section, can be seen here:
http://www.excelfox.com/forum/showthread.php/2306-Just-testing-Testing-some-sort-routines-No-reply-needed?p=11049&viewfull=1#post11049
In that screenshot the output from the previous routines produced in
__arsRef()
is shown and , for comparison, alongside it is shown that produced by
______________arrIndx() = Application.Index(arrIndx(), Rs(), Cms())

Modifying indices values in main sort loop sorting
In other words, how do we get the modified Rs() to use in
arrIndx() = Application.Index(arrIndx(), Rs(), Cms())
The way the current coding is organised makes this fairly simple. We have sections where all column elements in a row are swapped.
__ For Clms = 1 To UBound(arsRef(), 2)
___ Let Temp = arsRef(rOuter, Clms): Let arsRef(rOuter, Clms) = arsRef(rInner, Clms): Let arsRef(rInner, Clms) = Temp
__ Next
We use the row information in the variables rOuter and rInner. So quite simply, we do the same swap for row indices,
Dim TempRs As Long
_ TempRs = Rs(rOuter, 1): Let Rs(rOuter, 1) = Rs(rInner, 1): Let Rs(rInner, 1) = TempRs
With those simple modifications we obtain for the final full run the results shown here:
http://www.excelfox.com/forum/showthread.php/2306-Just-testing-Testing-some-sort-routines-No-reply-needed?p=11050&viewfull=1#post11050

Summary
In the next post the actual modifications to transform Sub SimpleArraySort6( to Sub SimpleArraySort7( are walked through in a bit more detail. Here is the brief summary to help in orienteering.
In the previous recursion routines, ( and still in this intermediate version, Sub SimpleArraySort7( ) the entire row values are swapped at various stags in the Bubble sort process. In our codings so far, we had typical sections like this

Dim Clms As Long '-------| with the condition met a loop is done for all columns in the array in which those two values used in the comparison are replaced at each column
For Clms = 1 To UBound(arsRef(), 2)
Let Temp = arsRef(rOuter, Clms): Let arsRef(rOuter, Clms) = arsRef(rInner, Clms): Let arsRef(rInner, Clms) = Temp
Next Clms '----------| for each column in the array at the two rows rOuter and rInner
In that above snippet, every column value in a row goes through the typical three line swap using a temporary variable: __ temp=My1 _ My1=My2 _ My2=temp https://excel.tips.net/T002525_Swapping_Two_Numbers.html
For the new coding idea, we only need to swap the row indices so as to change their order from like {1;2;3;4….} to the new sorted order like ModifiedRowIndicies ={2;1;4;3….}, since then we will apply the idea .._
__ arrNext() = App.Indx( originalRange , rows() , columns() )
__ arrNext() = App.Indx(originalRange, ModifiedRowIndicies , {1,2,3,4,…..})
_.. to get the modified row order from the original range. ( The columns, columns() , remain in the original order , {1,2,3,4,…..} )
So we have an extra code line which just swaps the row indicia. ( In this intermediate version I still include the previous swap sections )

Dim Temp As Variant ' I want to Swap those 2 above - I cant easilly in any coding change two values simulataneosly. So one of them Element values will put in this temporary place. This Element Values will then be given the other. Finally the other Element will be given this temporary value
Dim Clms As Long '-------| with the condition met a loop is done for all columns in the array in which those two values used in the comparison are replaced at each column
For Clms = 1 To UBound(arsRef(), 2)
Let Temp = arsRef(rOuter, Clms): Let arsRef(rOuter, Clms) = arsRef(rInner, Clms): Let arsRef(rInner, Clms) = Temp
Next Clms '----------| for each column in the array at the two rows rOuter and rInner
Dim TempRs As Long
Let TempRs = Rs(rOuter, 1): Let Rs(rOuter, 1) = Rs(rInner, 1): Let Rs(rInner, 1) = TempRs


The main other extra code section is then the code line to get the new row order using the reordered row indices
___ arrIndx() = Application.Index(arrOrig(), Rs(), Cms() )
This done at the end of the sort section

Next rInner ' ---------------------------------------------------------------------
Next rOuter ' ================================================== =========================================
Debug.Print "Doing an arrIndx()"
Let arrIndx() = Application.Index(arrOrig(), Rs(), Cms())
' Captains Blog, Start Treck

[DocAElstein]

Sub TestieSimpleArraySort7()
Sub SimpleArraySort7(ByVal CpyNo As Long, ByRef arsRef() As Variant, ByVal strRws As String, ByVal strKeys As String)

Global variables
At the top of the module in which are codes are we need our global variables

Dim Cms() As Variant, Rs() As Variant ' "HorizointalColumn" Indicies , "Virtical row" Indicies
Dim RngToSort As Range ' Test data range
Dim arrIndx() As Variant ' For modified array at end of each sort of a set of rows
Dim arrOrig() As Variant ' This arrIndx() = Application.Index(arrOrig(), Rs(), Cms()) applies the modified Rs() to the original unsorted data range. So we need an array to use constantly containing the original data range
Sub TestieSimpleArraySort7()
I use the same basic test data range as in other routines in the last few versions of Sub SimpleArraySort7(
Additionally I need the original data range in the separate ( global) array, arrOrig() , to use continuously in the first argument in the code line,
______Application.Index(arrOrig(), Rs(), Cms())
So this takes the value of the test data range
My array of column indicies , Cms() , is conveniently obtained in one go using a spreadsheet function , Column( ) which returns the column numbers of a given range in a horizontal array. I want like {1, 2, 3, 4, 5}, for my 5 column range, so I use something like Column(A:E) , Evaluate(Column(1to6)).JPG : https://imgur.com/jbaZdgJ
Similarly I can obtain the initial required vertical array indicia {1; 2; 3; ………} from a spreadsheet function, Row( ) Evaluate(Row(1to15)).JPG : https://imgur.com/UVTQCYO
' test index
I have a short test section whereby I paste out the row and column indices as well as the original array using these indices, .._
RngToSort.Offset(RngToSort.Rows.Count, 0).Value = Application.Index(arrTS(), Rs(), Cms())
_... which should return the original range for simple ordered indices, ( https://www.excelforum.com/excel-new-users-basics/1099995-application-index-with-look-up-rows-and-columns-arguments-as-vba-arrays.html#post4571172 )
For convenience I paste out the array thus produced directly under the original range for comparison. We find that they are identical , as expected.

Sub SimpleArraySort7(ByVal CpyNo As Long, ByRef arsRef() As Variant, ByVal strRws As String, ByVal strKeys As String)
For this version we have kept the same coding as for Sub SimpleArraySort7(_
The signature line remains the same, as all the extra variable we need are the Global variables discussed.
In every swap section we add the row indicie swap line ( 3 lines )
TempRs = Rs(rOuter, 1): Rs(rOuter, 1) = Rs(rInner, 1): Rs(rInner, 1) = TempRs
The only extra line needed for the routine to function with the Index method idea is
__arrIndx() = Application.Index(arrOrig(), Rs(), Cms())
This last line is added at just after every sort. We also include in this version a few lines to paste out the stages in the sorting for both the array from the previous method, arsRef() , and that from the Index method idea, arrIndx()

Next rInner ' ---------------------------------------------------------------------
Next rOuter ' ================================================== =========================================
Debug.Print "Doing an arrIndx()"
Let arrIndx() = Application.Index(arrOrig(), Rs(), Cms())
' Captains Blog, Start Treck
RngToSort.Offset((RngToSort.Rows.Count * (CopyNo + 1)), 0).Clear ' Area for array produced from previous method
Let RngToSort.Offset((RngToSort.Rows.Count * (CopyNo + 1)), 0).Value = arsRef()
RngToSort.Offset((RngToSort.Rows.Count * (CopyNo + 1)), RngToSort.Columns.Count).Clear ' Area for array produced by Index method idea
Let RngToSort.Offset((RngToSort.Rows.Count * (CopyNo + 1)), RngToSort.Columns.Count).Value = arrIndx()
RngToSort.Offset((RngToSort.Rows.Count * (CopyNo + 1)), -1).Resize(UBound(Rs(), 1), UBound(Rs(), 2)).Clear
Let RngToSort.Offset((RngToSort.Rows.Count * (CopyNo + 1)), -1).Resize(UBound(Rs(), 1), UBound(Rs(), 2)).Value = Rs() ' Current indicies order to apply to original range
Debug.Print " Running Copy " & CopyNo & " of routine." & vbCr & vbLf & " Sorted rows " & strRws & " based on values in column " & Clm & vbCr & vbLf & " Checking now for Dups in that last sorted list" & vbCr & vbLf
' Rem 3 Determine any duplicates in sort column values , and re run the routine to sort them by another column



Coding here
http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11053&viewfull=1#post11053
http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11052&viewfull=1#post11052

and in attached File



In the next post , the code is simplified slightly to just do the Index method idea

[DocAElstein]

Here are the key simplifications to take SimpleArraySort7 to SimpleArraySort8

Array referencing
Version Sub SimpleArraySort8(_ has as main distinguishing characteristic the removal of , or rather replacement of array , arsRef() , by that used in The Index method code line, arrIndx() . This array, arrIndx() , is moved from global variables: The array taken for By Referencing at the signature line will be arrIndx() in place of arsRef() . All references to arsRef() are replaced by arrIndx()
This could have been left as a global variable, but by declaring it in the signature line of Sub SimpleArraySort8(_ makes it the “housing carrying” wrapper for the supplied variable to the recursion routine array , and we can then leave the global variable with that same name arrIndx() for further use in the previous Sub SimpleArraySort7(_
The arrays for the index line in the recursion routine arrOrig() , Rs() , Cms() , remain as globals.
We will use the arrTS() from the original range capture to pass to be referenced to ( housed effectively within arrIndx() ). So that will be our final sorted array. ( arrIndx() will also be our final sorted array up to the point of the finally Ending of the first copy of the recursion routine, at which point that will then die: It is effective isolated from the “outside code module world”, so does not influence the global variable with the same name which we still have and which is available still for the previous version Sub SimpleArraySort7(

Column elements in a row swapping
One of the main distinguishing characteristics of the Index idea way, is that we sort the row indices in to a new order, and then apply the code line, .._
arrIndx() = App.Ind(arrOrig() , rowindicis, columnindicies
_.. to get the new order in one go.
However we must be careful. The immediate conclusion might possibly be that all the sections swapping all column elements in a row are now redundant and so can be removed. The is almost true, but not quite: The reordering of the row indicia is following directly the bubbling through sort of the column being used in the current sort. We must therefore continue to sort/ swap this column element for the rows in parallel to sorting/ swapping the row indicie.
Thus sections such as this in the previous Sub SimpleArraySort7(_ were swapping all the column elements and row indicies for two rows determined by the row number variables rOuter and rInner, …_

If IsNumeric(arsRef(rOuter, Clm)) And IsNumeric(arsRef(rInner, Clm)) Then
__ If CDbl(arsRef(rOuter, Clm)) < CDbl(arsRef(rInner, Clm)) Then
____ For Clms = 1 To UBound(arsRef(), 2)
_____ Let Temp = arsRef(rOuter, Clms): Let arsRef(rOuter, Clms) = arsRef(rInner, Clms): Let arsRef(rInner, Clms) = Temp
____ Next Clms '----------| for each column in the array at the two rows rOuter and rInner
___ Let TempRs = Rs(rOuter, 1): Let Rs(rOuter, 1) = Rs(rInner, 1): Let Rs(rInner, 1) = TempRs

_....Those sections need to be modified now so that they just swap those two rows in the column currently used to base the sort on, ( as well as still doing the swap of the row indicia )
We note that Clms was the variable for all columns in the loop for all columns in the swapping in the code snippet above , and Clm was the variable for the current column being used to determine the current sort order. So we no longer need that loop to swap all columns, - that can be removed. But if we do this removal, we must add a swap section for the Clm column …_

If IsNumeric(arrIndx(rOuter, Clm)) And IsNumeric(arrIndx(rInner, Clm)) Then
__ If CDbl(arrIndx(rOuter, Clm)) < CDbl(arrIndx(rInner, Clm)) Then
___ Let Temp = arrIndx(rOuter, Clm): Let arrIndx(rOuter, Clm) = arrIndx(rInner, Clm): Let arrIndx(rInner, Clm) = Temp
___ Let TempRs = Rs(rOuter, 1): Let Rs(rOuter, 1) = Rs(rInner, 1): Let Rs(rInner, 1) = TempRs


Simplifying ' Captains Blog, Start Treck
A number of additions were made for the previous version in order to compare the sort at various stages made by
the previous way – that in arsRef()
and
the Index idea way – that in arrIndx()

These are no longer needed and are removed to make the routine a better direct comparison with the latest routine version used in the previous l way , Sub SimpleArraySort6(_


Calling routine Sub TestieSimpleArraySort8() ( and Global variables and a required Function )
http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11056&viewfull=1#post11056

recursion routine Sub SimpleArraySort8(
http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11058&viewfull=1#post11058



I have started doing some timing measurements for the Index way idea to complement those already done ( http://www.excelfox.com/forum/showthread.php/2307-VBA-Range-Sort-with-arrays-Alternative-for-simple-use?p=11037&viewfull=1#post11037 Example: A few thousand columns with a few dozen rows )
The new measurements start from approximately here:
http://www.excelfox.com/forum/showthread.php/2306-Just-testing-Testing-some-sort-routines-No-reply-needed?p=11055&viewfull=1#post11055
http://www.excelfox.com/forum/showthread.php/2306-Just-testing-Testing-some-sort-routines-No-reply-needed?p=11061&viewfull=1#post11061

The initial measurements do not look particularly good.

In the next posts I will review and summarise this idea for a simple example as a pre stage in a slight re Write of coding Sub TestieSimpleArraySort8()
The re write is to make better use of the row indices since I can use them in place of the string of row indices which I currently pass through routines ( strRws = “ 1 2 6 9 8 ……. “ )

[DocAElstein]

Index idea Summary

This is intended to summarise the ideas from the last posts, using a simplified Example
The previous coding was an attempt to make a similar coding to the previous routines for a good comparison.
The purpose of this summary is to
re introduce the idea from scratch both to help understanding the general idea,
but also
to be a first step in the development of a generally more efficient coding by virtue of it being developed from the outset as for the Index idea.

Index Idea coding Complete
We can split this into two main ideas
Non Index idea bits
We remain with the general array recursion idea already used. The bubble sort technique is used.
The principle idea is to have an initial coding section, Rem 1. As previously this makes a sort of rows. Those are all the range data rows initially. They are always sorted based on the values in a specific column. ( Often the chosen column number or letter will be referred to as the ”Key:= _, _” )
The idea behind the use of recursion, is that after every sort, a check is done for rows having the same value. A copy of the sort routine then is done for those based on another column . This process goes on for as many different columns as necessary to get an ordered list , ( assuming the user has supplied enough “Keys” )
Index Idea bit
Without the index idea but I do this: If sorting “rows” in an array, using the Bubble sort way, then I go along ( down ) comparing values in a column, and swap them based on a < or > comparison. This means once the criteria for a sort has been reached, then I have to swap not only the values in the column that I am using to compare values, but also I have to do that for all columns in any two rows that get swapped.
For the Index idea way, firstly , I introduce an array, typically a 2 dimension 1 column, (“vertical”) array. It has all the indices of the rows in it. So at the start of any coding it looks like,

Rws() = 1;
____¬___2;
____¬___3;
____¬___.
____¬___.
___¬___.….etc..
( Often conventionally that is seen written as Rws() = {1; 2; 3…. …etc…} , whereby the ; typically indicates a new line for a “vertical” array )

I then still do the bubble sort/ swap along ( down ) a column. But I only change the values in that column and ignore the other columns. However I also swap the relevant row indices.
Just to clarify: Lets say after a bubble sort I ended up swapping the value in a column in the second and third rows. As I do that swap I also swap the same elements of my indicia array, Rws()
So looking at those numbers above, they would change to

Rws() = 1;
____¬___3;
____¬___2;
____¬___.
____¬___.
___¬___.….etc...
( or Rws() = {1; 3; 2…. , … etc…} )

Index idea way
In its simplest explanation: Excel VBA Application.Index Method applies a {row, column} co ordinate to an array to give the element from the array at the given {row, column} co ordinate position.
Less well documented is, that it allows us to reorder an array in this sort of way, pseudo code:

A b c _________________ A b c
G h I ___=___ App.Indx( D e f , {1; 3; 2} , {1, 2, 3} )
D e f _________________ G h i

A b c _________________ A b c __ { 1;
G h I ___=___ App.Indx( D e f , __ 3; __ , {1, 2, 3} )
D e f _________________ G h I ____ 2 }

The exact theory to how that above process works need a few weeks of reading to understand,
http://www.excelfox.com/forum/showthread.php/2145-Excel-VBA-Interception-and-Implicit-Intersection-and-VLookUp
https://www.excelforum.com/excel-new-users-basics/1099995-application-index-with-look-up-rows-and-columns-arguments-as-vba-arrays.html#post4571172
A summary based on the discussions at those above links, applied to our situation, is that Excel tries to reference the array in the first argument of the Index line, 9 times. This is that second argument 3x3 array
A b c
D e f
G h I
The second and third argument “force” Excel to try and do a 3 row x 3 column set of nine calculations
It needs for this referencing 9 sets of co ordinates to apply to that 3x3 array to return a similarly sized array.
The important conclusion form the theory at those links, is that in our situation, the way that Excel “works” means that as a , ( probably accidental ) , by-product of how Excel “works”, we effectively get duplications to fill the “missing” co ordinates
So Excel goes along all columns, then down a row, then along all columns, then down a row, then along all columns, using, in or example, these co ordinates, pseudo
___1 1 1 ___ 1 2 3
___3 3 3 _, _ 1 2 3
___2 2 2 ___ 1 2 3
So it does pseudo this, using the second argument, to give the final left hand Side results :.._
___{1,1}=A {1,2}=b {1,3}=c ‘ all columns for first row
___{3,1}=G {3,2}=h {3,3}=I ‘ all columns for third row
___{2,1}=D {2,2}=e {2,3}=f ‘ all columns for second row
_.. it does each reference at a proportionally “offset place” in its “memory” , and so the returned output is in dimensions reflecting the 9 calculations.
_.__________

So , the idea of the index way is that we just sort the values in a column and additionally change correspondingly the row indicie array , Rs().
At the end of the sort we have a code line something like this, which will give the row re order along all columns
__arrIndx() = Application.Index(arrOrig(), Rs(), Cms())
The original data array, arrOrig() , and the sequential list of the columns , Cms() , remains constant throughout any recursion coding.
The order of the indices in Rs() and subsequently the row order for the final array in arrIndx() , will typically change after each sort.


Simplified Examples
The purpose of this coding is simply to be able to easily follow and understand the entire bubble sort and bubble sort with Index idea way.
Therefore we will restrict the examples to simple numbers. The coding will deliberately be hard coded in many places to show actual numbers , as this will make it easier to see what we are doing.
We will use the same test data range.
Row\Col
B
CD
E

10
1
2
3
4


11
1
1
5
3a


12
2
9
9
9b


13
3
1
4
2c


14
4
8
8
8d


15
5
1
3
2e


16
6
7
7
7f
Worksheet: Sorting
The red numbers are not part of the test data range: I have added them manually and are just shown for convenience and to help in the explanations: They represent the “horizontal column” and “vertical row” indicies.
I will intend to do a sort based on three columns. I will do this in two routines, Sub Bubbles , and Sub BubblesIndexIdeaWay. Both use the bubble sort, and the second modifies slightly the routine to use the Index way idea
I will go for a simple Ascending order sort, based on sorting first by the first column , then the third and finally the second. A manual inspection tells me that the first sort will end up with three rows it cannot order because of all values of 1 in that first column. The second sort by column 3 will not be able to sort two rows both containing a 2 in that third column. A final sort by the second column should be able to give a final order.
I can also check this by applying a simple coding using the existing VBA Range Sort Method: In a simple single line use this allows me to have up to 3 Keys
SortRng.Sort _ key1:=SortRng.Columns(1), order1:=xlAscending, Key2:= SortRng.Columns(3), order2:=xlAscending, Key3:= SortRng.Columns(2), order3:=xlAscending, MatchCase:=False

Sub RangeSort() ' https://docs.microsoft.com/de-de/office/vba/api/excel.range.sort
' data range info
Dim WsS As Worksheet: Set WsS = ThisWorkbook.Worksheets("Sorting")
Dim RngToSort As Range: Set RngToSort = WsS.Range("B11:E16")
' copy and sort range using VBA Range.Sort Method
RngToSort.Copy
WsS.Paste Destination:=WsS.Range("B31")
Dim SortRng As Range: Set SortRng = WsS.Range("B31").Resize(RngToSort.Rows.Count, RngToSort.Columns.Count)
SortRng.Sort key1:=SortRng.Columns(1), order1:=xlAscending, Key2:=SortRng.Columns(3), order2:=xlAscending, Key3:=SortRng.Columns(2), order3:=xlAscending, MatchCase:=False ' Note: SortRng.Columns(3) -- the columns property of the range object, SortRng , gives the third column from the range SortRng , not the third column from the spreadsheet third column
End Sub
Running the above routine gives us the following:
Row\Col
AB
CD
E

30
1
2
3
4


31
5
1
3
2e


32
3
1
4
2c


33
1
1
5
3a


34
6
7
7
7f


35
4
8
8
8d


36
2
99
9b
Worksheet: Sorting
Once again I have added the red numbers manually for demonstration purposes

In the next posts we will develop the Array routine alternatives Sub Bubbles , and Sub BubblesIndexIdeaWay

[DocAElstein]

Recursion routines: Sub Bubbles , and Sub BubblesIndexIdeaWay

Sub Bubbles
Because recursion routines , ( at least for me ) are always difficult to understand, we will initially do a recursion routine without the new Index idea way, but which will use hindsight from the previous posts to make it in a way that can be easily modified to become Sub BubblesIndexIdeaWay But it actually the modifications are very minor, and actually only in the Calling routine Sub Call_Sub_Bubbles()
I suppose I am just to trying to consolidate the understanding of the recursion code, before introducing the new “Index way idea” form of it.
In this routine, Sub Bubbles , I will already use a global variable, which I have more recently introduced specifically for the Index way idea, Rs()
In previous coding I have passed a string of the row indices , strRws = “ 1 3 2 5 6 7 “ , between different copies of the recursion routines. Because I need to use Rs() in the next routine , Sub BubblesIndexIdeaWay , I will make use of it now to ease the transition to the Index way idea.
But important : I later need Rs() in , Sub BubblesIndexIdeaWay to house all indices in a reordered way to allow use of the App.Index formula. So I can’t do away with my strRws, which is used to pass between recursion routine copies the sub set of rows to be sorted for when rows in the column currently last sorted had duplicate values.
So I mention Rs() a bit, but don’t really use it much..
Only in the case of the first copy of the recursion routine, the previous string , strRws , and R(s) , fulfil the same basic purpose of housing indices for the current row order in the current running copy of the recursion routine

So for Sub Bubbles, or rather Sub Call_Sub_Bubbles() , we need ( only ) the global variable R(s).
But it does no harm to include all the variable needed for this and Sub BubblesIndexIdeaWay, at the top of the code module,

Option Explicit
Dim Cms() As Variant, Rs() As Variant ' "Horizointal Column" Indicies , "Virtical row" Indicies
Dim arrOrig() As Variant ' This arrIndx() = Application.Index(arrOrig(), Rs(), Cms()) applies the modified Rs() to the original unsorted data range. So we need an array to use constantly containing the original data range

Calling routine Sub Call_Sub_Bubbles()
Usually routines that take arguments at the signature line ( in the Sub( x As Long, …. start bit) ) , cannot be run directly , and must be set off by another “Calling” routine which passes the required arguments at the “Call” line. For a recursion routine this is also the case for the first copy of the routines. ( Further copies start as the existing copy pauses after itself Calling another independent copy of the recursion routine to start )
So we need a calling routine…._
Sub Call_Sub_Bubbles() Calls Sub Bubbles Copy1
Sub Bubbles Copy1 Calls Sub Bubbles Copy2
Sub Bubbles Copy2 Calls Sub Bubbles Copy3
_............
For convenience we can also, in this routine, for test purposes create the array from a worksheet range which we will pass as the array to be sorted by our test range.
This array, arrTS() , is passed to the recursion routine as one of the main arguments. ( The signature line of the recursion routine will declare the variable that needs to be passed this array, as being ByRef . This will means that all changes on that array inside the recursion routine , ( and necessarily any single copy of the recursion routine running at any time ) are referred back to this array. Effectively this can be thought of as the array arrTS() as being “carried in” the recursion routine signature line defined variable, and similarly one can consider all done on the array as being done on this arrTS()
The next characteristic of this Calling routine was in this Thread so far only used for when a recursion routine was called using the index way idea ( __Sort8 , __Sort7 codings )
I can’t easily replace in all codings the string of row indices , “ 1 3 2 5 6 7 “ , with the array of row indices,
Rs() = 1;
____¬___3;
____¬___2;
____¬___5
____¬___6
____¬___7 }
For the first copy of the recursion routine , ( the only copy Called by this Calling routine ) , those will be the initial ordered rows, in our example,
{1;
2;
3
4
5
6}
But subsequently, I will likely only need a subset of the rows to further sort. It would get very messy to be chopping and changing in particular, the dimensions, of such an array. Mostly this is because it is a 2 dimensional ( all be it 1 “width”/column) array, and they are not so easy to manipulate as are 1 dimensional array that lend themselves to easy manipulation with string functions. The Join and Split are in particular very useful, but these only work on 1 dimensional arrays

So I restrict the use of the Rs() in this coding to an alternative for generating the first string values. But that is not particularly any better than the previous way..

Sub Call_Sub_Bubbles()
For convenience we use spreadsheet functions to give us sequential indices initially in Rs() , ( and later in next routines Cms() ) Evaluate(Row(1to15)).JPG : https://imgur.com/UVTQCYO ( Evaluate(Column(1to6)).JPG : https://imgur.com/jbaZdgJ )
( The, "Vertical row" Indicies, ( and the "Horizointal Column" Indicies ) , can be Long or String types, and out App.Index coode line would work, but because we use for convenience spreadsheet functions vial the VBA Evaluate(“ “) function to obtain these. As the Evaluate(“ “) returns all things housed in Variant type, we declared Rws() ( and Cms() ) , as Variant )
The information we need for the column by which to be sorted is taken in a simple string, which we organise to have a similar form to that for the Range.Sort method. For our example it looks like this: " 1 Asc 3 Asc 2 Asc " . For our simplified example , the Asc has no effect, but is included to aid in comparison with previous / further developed routines which have / will allow the option to chose Ascending or Descending sort order
For any recursion routine it is almost always useful to have a variable set at the start of any copy of the routine which tells us which copy number is running. This I typically call CpyNo, CopyNumber , CopyNo or similar.
At the start of every copy of the recursion routine, the value passed at the signature line is put in a local copy variable which can be then used to give the copy number of the current running routine.
So from the Calling routine I pass 1. At the code line in the recursion routine which Calls into life a new copy of the routine, we pass like CopyNumber + 1 . This ensures we then have the correct value place at the start of any newly starting copy of the recursion routine, since the first copy of the recursion routine pauses and starts the second copy of the recursion routine, the second copy of the recursion routine pauses and starts the third copy of the recursion routine ….. etc…

Here is our final Calling routine, Sub Call_Sub_Bubbles()
( It gives the same demo range output as Sub RangeSort() , ( or should do once I have written the routine that it Calls :) ) )

'
Sub Call_Sub_Bubbles() ' Partially hard coded for ease of explanation
' data range info
Dim WsS As Worksheet: Set WsS = ThisWorkbook.Worksheets("Sorting")
Dim RngToSort As Range: Set RngToSort = WsS.Range("B11:E16")
' Set RngToSort = Selection ' ' Selection.JPG : https://imgur.com/HnCdBt8

Dim arrTS() As Variant ' array to be referred to in all recursion routines, initially the original data range
Let arrTS() = RngToSort.Value
' Initial row indicies
Let Rs() = Evaluate("=Row(1:6)") '
Dim strRows As String, Cnt As Long: Let strRows = " "
For Cnt = 1 To 6
Let strRows = strRows & Rs(Cnt, 1) & " "
Next Cnt
' we should have now strRows = " 1 2 3 4 5 6 "
Call Bubbles(1, arrTS(), strRows, " 1 Asc 3 Asc 2 Asc ")

' Demo output
Let WsS.Range("B31").Resize(RngToSort.Rows.Count, RngToSort.Columns.Count).Value = arrTS()
End Sub
'

The next post develops the Called routine,
Sub Bubbles(ByVal CpyNo As Long, ByRef arsRef() As Variant, ByVal strKeys As String)

[DocAElstein]

Sub Bubbles( ByVal CpyNo As Long, ByRef arsRef() As Variant, ByVal strKeys As String )
Here’s the first bit…
Sub Bubbles(ByVal CpyNo As Long, ByRef arsRef() As Variant, ByVal strKeys As String)
Dim CopyNo As Long: Let CopyNo = CpyNo

Rem -1 from the supplied arguments, get all data needed in current bubble sort
Dim Keys() As String: Let Keys() = Split(Replace(Trim(strKeys), " ", " ", 1, -1, vbBinaryCompare), " ", -1, vbBinaryCompare)
Dim Clm As Long: Let Clm = CLng(Keys((CopyNo * 2) - 2))

CopyNo:
On every recursion run , that is to say, the copy brought into life by a Call form the routine itself, this will be increased by the Recursion Call by 1.
This will be a local variable indicating the level down in recursion - I increase it by 1 at every Recursion Call, that is to say each Recursion Call in this routine gets given CopyNo+1 as the first passed argument , CpyNo
___Call Bubbles(CopyNo + 1, arsRef(), ……
During a long “tunnel down”, the number at this point will keep increasing to reflect how “far down” we are. This ensures we then have the correct value place at the start of any newly starting copy of the recursion routine, since the first copy of the recursion routine pauses and starts the second copy of the recursion routine, the second copy of the recursion routine pauses and starts the third copy of the recursion routine ….. etc…

Rem -1
Keys()
It is convenient to split " 1 Asc 3 Asc 2 Asc " , which will give me 6 elements, which with a bit of maths, I can get the information I need regarding the column to be used in determining the sort order
Clm
For the simplified routine I just want the column number used for the sort. ( 1 or 3 or 2 )
As we will see, each next copy of the recursion routine is intended to use the next column to be used in determining the sort. So a simple bit of maths determines the element, and hence the contained column number to be used :
Clm = CLng(Keys((CopyNo * 2) - 2))
So for example , at the initial Call, done by the Calling routine, CpyNo is passed 1 from the supplied CopyNo
I will then have Key(0) which in a 1 dimensional array produced by the VBA Split function is the first array element, which has “1” in it in our example.
When the routine itself sets off a new copy, the next copy, CopyNo will become 2. Our formula , CLng(Keys((CopyNo * 2) - 2)) will use Key(1) which in a 1 dimensional array produced by the VBA Split function is the third array element, which has “3” in it in our example.
( I want a number, so I use Clng , but that probably is not needed, as VBA has a habit of usually accepting a string which “looks” like a number as the number it looks like, ( i.e. it takes “8” as 8 ) , in any situation where it is excepting a number )

Rem 1 Bubble Sort
I must have explained this basic idea a thousand times to myself and others. But I find I need to re say it every time to remind myself.
Consider initially the first copy run …
I have rows of values ( numbers in this simple example) in a column, Clm in my main data array, arsRef(__ , Clm )
Rs Clm= 1
1 __1
2 __9
3 __1
4 __8
5 __1
6 __7
I have an =Outer Loop== in my coding, controlled by a variable, rOuter, that goes, in this example, the first time, from 1 To 5. Picking out the last but top and bottom number from strRws, in this case 1 and 5 from “ 1 2 3 4 5 6 “ can be got in lots of ways. In previous routines I made a 1 dimensional array from using th Split function , and looked at the first an last but one element of that array. For no special reason I use a bit of string manipulation this time. By the way, the Immediate window is very useful for fiddling around to get at the right thing in coding such as this – see ImmediteWindowToHelpGetAtStuff.JPG : https://imgur.com/yGqmYSu : http://www.eileenslounge.com/viewtopic.php?f=27&t=16407&p=247121#p247121
2205

A handy way of thinking about the bubble sort:
I think of the position of the outer loop at any time, as that as where my left hand. At any point in time, its position is determined by rOuter, and it is pseudo “holding” what is currently in that row number, rOuter = 1 2 3 4 or 5. ( Note I never “hold” the last row in my left hand )
At every of the 5 rows, my left hand waits there for a while, why my right hand in an -Inner Loop-- goes from one row above where my left hand currently is, rOuter+1 To the last row, 6
As I go “down” with my right hand holds temporarily each row value.
So at any point in the combined outer and inner looping, I have a value “in” each hand. This is effectively the middle point of the loop Here…

Basic Bubble sort loops

__For rOuter = 1 To 5 ‘ =====
___ For rInner = rOuter+1 To 6 ‘----

_____Here
______ Compare,
________ Possibly swap

____Next rInner ‘ ------
__Next rOuter ‘ =====

At Here, I compare the values in my hands. If the left hand, arsRef(rOuter, Clm) is > then the right hand, arsRef(rInner, Clm), then I swap the values.
If you consider what happens for one full inner loop, the first time, (rOuter = 1) , this process will mean that the smallest number anywhere ends up in my left hand.
As I move my left hand to row 2, and do the same process of moving my right hand down the remaining rows and comparing and possibly swapping values in my hand, then the next smallest ends up at row 2 .
As I move my left hand to row 3, and do the same process ……_
_….etc..

Doing the swap
I cannot in VBA, or in computing software generally, throw 2 values up in the air and catch them in the opposite hands. That is a nice Human ability to do things exactly at the same time. Most software just give the impression of doing things at the same time because it works so fast that we do not see every separate sequential thing. Our program must do a logical sequential progression: So to do the swap , ……
my left hand puts its value in a Temporary place:
____ then the left hand takes the value from the right hand:
_________finally the right hand takes the original left hand value from the Temporary place.

Swap all columns.
The reason for me doing the sorting is to re arrange all the rows. But what I have just explained has re ordered just one column, and not the entire row.
The main difference in this routine, Sub Bubbles , and Sub BubblesIndexIdeaWay is how I do this. For the current routine, Sub Bubbles , I do this in the usual way for an array type bubble sort.
At the inner point, Possibly swap Then, I don’t do the swap for just the column, Clm. ( I only use the variable Clm within the comparison line , Compare, If – I do this for the next routine also )
What I actually do is introduce another, third loop, which swaps all columns.

Recursion code designed to sort different row range
I cannot hard code the rOuter as 1 To 5 , and the rInner as rOuter+1 To 6 , not even for this simplified coding, as I want to use the same coding ( or rather further independent copies of it) , to sort sub row sections for the case of duplicate values in previously sorted columns.
Instead I use my array of row indices, Rs(). The intention will then be in Rem 3 to run a new copy of the recursion routine, Sub Bubbles , with the subset of Rs() and resort , ( and if necessary do this again to resort a further time by another column )
Each time I consider in total from the lower indicie to the upper indicie. ( rOuter from the lower, rInner to the upper ) The first time in the current example , this will be 1 To 6 , then it should be 1 To 3, and finally it should be 1 To 2 . ( It must not necessarily be, as in this example, that we always start at 1 )

Here is the final bubble sort code section

Dim rOuter As Long ' ========"Left Hand"=====================Outer Loop=====================================
For rOuter = Rs(LBound(Rs(), 1), 1) To Rs(UBound(Rs(), 1), 1) - 1 ' For first row indicie to last but one row indicie
Dim rInner As Long ' -------Inner Loop-------------"Right Hand"--------------------------
For rInner = rOuter + 1 To Rs(UBound(Rs(), 1), 1) ' from just above left hand through all the rest
'Here
If CDbl(arsRef(rOuter, Clm)) > CDbl(arsRef(rInner, Clm)) Then
Dim Temp As Variant ' I want to Swap those 2 above - I cant easilly in any coding change two values simulataneosly. So one of them Element values will put in this temporary place. This Element Values will then be given the other. Finally the other Element will be given this temporary value
Dim Clms As Long '-------| with the condition met a loop is done for all columns in the array in which those two values used in the comparison are replaced at each column
For Clms = 1 To UBound(arsRef(), 2)
Let Temp = arsRef(rOuter, Clms): Let arsRef(rOuter, Clms) = arsRef(rInner, Clms): Let arsRef(rInner, Clms) = Temp
Next Clms '----------| for each column in the array at the two rows rOuter and rInner
Else
End If
Next rInner ' -----------------------------------------------------------------------
Next rOuter ' ================================================== ========================================

_.___
As an intermediate step, lets end the routine for now at this stage, and run it via the Calling code
Intermediate coding for this is here: http://www.excelfox.com/forum/showthread.php/2306-Just-testing-Testing-some-sort-routines-No-reply-needed?p=11065&viewfull=1#post11065
( Note that …_
For rOuter = Rs(LBound(Rs(), 1), 1) To Rs(UBound(Rs(), 1), 1) - 1
_... is only valid for this first copy run, --- typically less rows are used and we get them from like the strRws = “1 2 3 “ … )

If we run that using our test range we get these results:

1 5 3 a

1 4 2 c

1 3 2 e

7 7 7 f

8 8 8 d

9 9 9 b
If we compare that with the expected final output, we see we are not quite there yet..

1 3 2 e

1 4 2 c

1 5 3 a

7 7 7 f

8 8 8 d

9 9 9 b

[DocAElstein]

Rem 3 Preparation for possible recursion Call
What we want to do
At this point, in many real applications we will likely have most of the rows sorted but will have possibly some rows with similar values in the column used in the initial sort.
We need some way to send arrange that the array as now sorted is resent again sorted by another running copy of the recursion routine over the duplicate rows.
We do not want to over simplify this coding. We did a bit of simplifying: For the sake of explanation of the general coding strategy it was OK to choose just numbers and all Ascending order. But to give the coding some worth in showing typical recursion coding workings, we should allow for the case of multiple duplicate row sections. We will discus that this is the sort of thing, that is to say going “back and forth” or “up and down” in a semi automatic way is what a recursion code is often best at.
There is no single way I know of, to write such a coding. It simply takes some careful thought to arrange that the Call of a new copy is such that on termination of that copy, the way the original copy resumes will allow for the possibility of things such as for “going back down” if, in this case, we need once again to resort multiple rows. Sometimes there are a couple of general characteristics which can help in the design of such a coding…

Main outer loop, and multiple copies of Copys/ “Levels”
Usually we will have a main outer loop, which in our case will be approximately across the entire range of current rows to be sorted. Often, but not always, towards the end, or last half, of the loop, will be the recursion Call, that is to say the code line which causes the current running routine to pause, whilst a new independent copy of the routine starts.
Having this Call part in a loop is probably what usually gives this ability to go “back and forth” between different independent copies of the recursion routine: You see , when a Called recursion routine copy ends, then the previous which had paused restarts, and depending on what your loop is doing, it will possibly cause the Call to be done again, so “you go back down” a copy level, or up a copy level depending on how you like to visualise it. It should be noted that each start and End of a recursion is an independent routine. The variable CopyNumber that we use tells us at “what level” we are, or how “far down” the recursion change of events. In our case that translates in the practice to which column in our list of columns to use , 1 3 2
CopyNo 1 : we are using column 1 to determine the sort order
CopyNo 2 : we are using column 3 to determine the sort order
CopyNo 3 : we are using column 2 to determine the sort order
There will only ever be one copy number 1 started, but that will likely pause a number of times. For further copy number , several may start and End: There can only ever be one running at any one time, but several independent copies could be ran from start to completion. The copy number is an indication of the “level” or in our case the column being used to determine the sort order. ( With hindsight… probably CopyLevel would be a better name for the variable… )

Ending
A typical, but not essential, characteristic of recursion routines is that not much usually goes on after the loop and / or last Call code line area. Further, a typical characteristic is that Ends, especially the final few Ends tend to occur one after the other. It tends to go unnoticed , for example when using debug F8 step code progression mode , since there Are not many code lines there. I find it therefore very demonstrative to have a Message box or Debug.Print code line immediately before the code end, with a message like “Ending a copy number “ & CopyNo & “”

Our specific case: What do we need to do
We already have the array we need for a possible required further sort in the case of duplicated values in the column used in the last bubble sort.
What we need to do is determine any rows with those duplicate values, and then pause the current routine whilst we run another copy of the routine after we have changed the indices in Rs() to just the sub set of rows to be sorted.
I guess there are many ways to do this. I expect I will come back some time and try a few.
The basic strategy to take advantage of recursion in this case will probably usually be the same

Basic recursion using strategy
This is not so difficult as the original sort will mean that rows for duplicated values in the current/ last sort column will be grouped together. So the basic strategy is to loop “down” and whilst we do this noting the indicies for these rows for duplicated values in the current/ last sort column. Once we have such a group use the Call Sub Bubbles(__ recursion starting code line to pause the current routine and start a new copy of the recursion routine to sort these rows which have duplicated values in the current/ last sort column.

Actual working application of the Basic recursion using strategy
I find it very convenient, as well as making debug easier , to “collect things” like row indices in a long string. This is because
it makes it easier to print or message box out what I have, so lends itself nicely to user interfaces to see what is going on,
and
there are very many string manipulating function and methods available

In fact, I already use the variable, strRws , for the range of rows, so it makes sense to use it to build my duplicated rows indicies in this sort of form like " 1 2 3 ". In other words similar I use it similar to how it was used in its first use , where, in this example, it was built like this in the Calling routine, " 1 2 3 4 5 6 ". For its first use it has all the indicies, and now , subsequently it will hold the indices corresponding to duplicate row values in the current/ last column used to define the sort order, in the latest version of arsRef() :
Just to clarify as it is easy to get lost in recursion routines: At this Rem 3 stage, I have most likely , just re ordered the array, arsRef(). It may have some rows that could not be put in any specific order if there were identical values in those rows in the column used in the last/ current sort.
One of the main workings of this section, Rem 3 , will be to obtain a string of the form " 1 2 3 " which represents duplicate row values in the current/ last column used to define the sort order.
( In a more realistic example , I might have several groups of such rows, so would have strRws = " 7 8 9 10 " , strRws = " 17 18 " , strRws = " 72 73 74 75 76 77 78 " , ..etc… Furthermore some of those rows might still need to be sorted using a different column / that is to say, using the next user given column, -- hence the reason for trying a recursion routine which “keeps going further and further” as needed

_ Let strRws = ""
I initialise my variable so that I can use it to build up my new duplicate variable rows list

__For rOuter = Left(Trim(tempStr), InStr(1, Trim(tempStr), " ", vbBinaryCompare) - 1) To …………………………
I begin a main **Outer Loop** for all but the last row, where the actual rows are determined by the upper and lower values in strRws
____ If strRws = "" Or InStr(1, Trim(strRws),…………….
____ I have a condition which should catch the situation of starting looking for a set of duplicates, so this will be at the start, strRws = "" , or if the last loop produced no addition to the string and so is left at a single indicia, meaning that no in between space is present, and consequently this will be 0 , InStr(1, Trim(strRws), " ", vbBinaryCompare). With these conditions met my string will become like " 1 " ( with more realistic data this could be any row number )

____ If Trim(UCase(CStr(arsRef(rOuter, Clm)))) = Trim(UCase(CStr(arsRef(rOuter + 1, Clm)))) …………………
____ This code line looks to see if we have a duplicate at the next row in last/ current sort column. ( This is why we loop in the main outer loop to 1 less than the last row, to prevent an error of array index out of range here )
_____ with the last condition met we add the indicie to the current string, strRws
_____ Let strRws = strRws & rOuter + 1 & " "
____ Else ' without the last condition met
____ without the last condition met, we might have the end of a group of duplicate rows, in which case it would be time to organise a recursion run so
____ If Not InStr(1, Trim(strRws), " ", vbBinaryCompare) = 0 Then
______we this we check for this situation needing a recursion run,
______with the condition met its time to organise recursion run
' Now its time to organise a recursion run
Because of how we built our string, strRws , we have nothing to do other than Call a new copy of the routine, Sub Bubbles( __ , with the appropriate arguments,
After that new copy of the recursion routine Ends, I will come back to just after the Call line and in a normal practical use I might still find another group of rows in this look at the first full row sorted array, so I set strRws=””
______ '+++*** this would be end of loop for most cases
Oh Fuck
In most cases I am finished after I am towards the end of the main Loop here.
But we have one slight problem: there is one small imperfection with our logic: In our logic, the end of a group of duplicates is determined by the two conditions:
_ firstly the next row is not a duplicate, and
_ secondly the current strRws has at least two indicies in it.
The problem comes if we have a group of duplicate rows that include the last row: In such an occurrence we will never reach a point where the next line being not a duplicate causes us to do the first then second check and subsequent Call of a new copy of the routine, Sub Bubbles( __.
To overcome this problem we include a last check which covers such an occurrence an allows a last Call of a new copy of the routine
I think for realistic real life data, this might be one of those situations whereby you ignore the last problem so as to either
use it effectively later to cause some mischief and then demand a high ransom to get correct it, or
or
you might consider arranging your data, such that the problem scenario would not occur. This might be a more efficient solution than having the extra check in every looping



In the next post is a summary of a run of the routines
( Final coding here:
http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11067&viewfull=1#post11067 )

[DocAElstein]

Illustration of a full run of coding from last post

If you run the coding discussed in the last post, then you will have available in the Immediate window ( http://www.eileenslounge.com/viewtopic.php?f=27&t=16407&p=247121#p247121 ) similar below.

I have added some screen shots in between to help illustrate better

So if you run routine Sub Call_Sub_Bubbles() on the following test data, then you will get the results below in the immediate window 8 without the extra screenshots which I have added here.
Row\Col
B
C
D
E

11
1
5
3a


12
9
9
9b


13
1
4
2c


14
8
8
8d


15
1
3
2e


16
7
7
7f
Worksheet: Sorting

After running Sub Call_Sub_Bubbles():

First procedure Call
Running Copy No. 1 of routine.
Sorted rows 1 2 3 4 5 6 based on values in column 1
Bubbles(1, arrTS(), strRows, " 1 Asc ")

1
5
3


1
4
2


1
3
2


7
7
7


8
8
8


9
9
9
Checking now for Dups in that last sorted list


Found dups in last list column 1, 1 2 3 , so now main Rec Call
Running Copy No. 2 of routine.
Sorted rows 1 2 3 based on values in column 3
Bubbles(1, arrTS(), strRows, " 1 Asc 3 Asc ")

1
4
2


1
3
2


1
5
3


7
7
7


8
8
8


9
9
9
Checking now for Dups in that last sorted list

Found dups in last list column 3, 1 2 , so now main Rec Call
Running Copy No. 3 of routine.
Sorted rows 1 2 based on values in column 2
Checking now for Dups in that last sorted list

Ending a copy, Copy level 3
Ending a copy, Copy level 2
Ending a copy, Copy level 1
The extra intermediate screenshots above I produced by using these alternative Call code lines
Call Bubbles(1, arrTS(), strRows, " 1 Asc ")
Call Bubbles(1, arrTS(), strRows, " 1 Asc 3 Asc ")


Finally in the spreadsheet you should see this
Row\Col
B
C
D

31
1
3
2


32
1
4
2


33
1
5
3


34
7
7
7


35
8
8
8


36
9
9
9
Worksheet: Sorting

[DocAElstein]

Converting Sub Bubbles to Sub BubblesIndexIdeaWay

This and the next posts will concentrate primarily on the changes required to convert the coding from the last posts, _...
http://www.excelfox.com/forum/showthread.php/2307-VBA-Range-Sort-with-arrays-Alternative-for-simple-use?p=11063&viewfull=1#post11063
http://www.excelfox.com/forum/showthread.php/2307-VBA-Range-Sort-with-arrays-Alternative-for-simple-use?p=11064&viewfull=1#post11064
http://www.excelfox.com/forum/showthread.php/2307-VBA-Range-Sort-with-arrays-Alternative-for-simple-use?p=11066&viewfull=1#post11066
http://www.excelfox.com/forum/showthread.php/2307-VBA-Range-Sort-with-arrays-Alternative-for-simple-use?p=11068&viewfull=1#post11068
_... into similar coding using the Index idea way.
http://www.excelfox.com/forum/showthread.php/2307-VBA-Range-Sort-with-arrays-Alternative-for-simple-use?p=11062&viewfull=1#post11062
http://www.excelfox.com/forum/showthread.php/2145-Excel-VBA-Interception-and-Implicit-Intersection-and-VLookUp/page2
https://www.excelforum.com/excel-new-users-basics/1099995-application-index-with-look-up-rows-and-columns-arguments-as-vba-arrays.html#post4571172

Global variables
I am not too keen generally on global variables , http://www.eileenslounge.com/viewtopic.php?f=30&t=29652#p229402 , but the alternative in our current coding would be to pass things in such a way as to arrange for By Referring to them throughout all passed routines. This makes things a bit messy due to a long signature line when we have a few variables, so I will use some global variables. But we note that it is not essential to the index way. It is done more for visual convenience and prettiness

These are the full set of global variable that should be placed at the top of a cod module

Option Explicit
Dim Cms() As Variant, Rs() As Variant ' "Horizointal Column" Indicies , "Vertica row" Indicies. ( The, "Vertical row" Indicies, ( and the "Horizointal Column" Indicies ) , can be Long or String types, and out App.Index coode line would work, but we typically use for convenience spreadsheet functions vial the VBA Evaluate(“ “) function to obtain these. As the Evaluate(“ “) returns all things housed in Variant type, we typically declared Rws() and Cms(), as Variant )
Dim arrOrig() As Variant ' This arrIndx() = Application.Index(arrOrig(), Rs(), Cms()) applies the modified Rs() to the original unsorted data range. So we need an array to use constantly containing the original data range
Dim arrIndx() As Variant ' For Sub SimpleArraySort7()


Sub Call_Sub_BubblesIndexIdeaWay()
We introduced the row indicies array, Rs(), in the last , although they were not really essential to have. We now require to use the complete variable associated with the Index idea way.
______ arrIndx() = Application.Index(arrOrig(), Rs(), Cms())
We need to add something to give us our column indicies , in this example for 4 columns , { 1 , 2, 3, 4 } , which we can conveniently get from a spreadsheet function, Column( ) , via Evaluate(“ “) in VBA
We also need to fill our original range data array, arrOrig(). This will simply be filled with the original data range, but will remain static.
We no longer need to pass any array: We will simply be able to reference the global variable , arrIndx() , for our final sorted output , after the routine, Sub BubblesIndexIdeaWay() has finally ended all copies of it ran after it being called by Sub Call_Sub_BubblesIndexIdeaWay()

arsRef() , arrIndx() WTF array
There is a lot of very similar arrays flying around at the moment.
Some will likely be redundant , or missing , in some codings. Often I will keep them to aid in coding version comparison, and their use may not be necessary, or , as mentioned full or partly redundant.

arrTS() : Up until now this was usually our array to be sorted and was taken from the spreadsheet capture, arrTS() = RngToSort.Value . As this was normally taken in as the array to be By Referred to , it was pseudo like the array carried in the array of the recursion function, arsRef() , and all changes were referred to this. It became finally the sorted array and would usually be then pasted out for demonstration purposes at some range offset from the original data. This pasting out ways usually the last line in the Calling routine, so became the last thing done typically.
arrIndx() : This is intended to be a global variable array, the array updated continuously with the new index way idea, arrIndx() = Application.Index(arrOrig(), Rs(), Cms()) . So this becomes , or is a replacement for , both all uses of arsRef() , and for the final usage of arrTS().
arsRef() : Mostly used as the pseudo “housing / carrying” array used throughout the recursion routines, passed between copies and continually being modified so that the “carried” array, usually arrTS() would finally have the final sorted data range array. For the index way idea, we have a couple of ways to use arrIndx():
_ We can pass this to be held in arsRef(),
_ We can simply change all references in the routine from arsRef() to arrIndx(). Because arrIndx() is a global variable, we will then always be referring to this.
Using the second option is the most sensible and efficient as then we can completely do without arsRef() , and indeed could completely remove it from anywhere, including the signature line of the recursion routine.
For the sake of consistency and comparison, I will use the first option. This will also be helpful in development of the next routine, Sub BubblesIndexIdeaWay(), as I will have an intermediate stage, with both arrays present, filled by the alternative ways, and can compare the results.
As arrIndx() will be passed to the recursion routine by Sub Call_Sub_BubblesIndexIdeaWay() , it needs to be filled initially, arrIndx() = arrTS()
All of these arrays are dynamic arrays of Variant type elements and so can be directly assigned to each other


Here is the almost the final Calling routine:


Sub Call_Sub_BubblesIndexIdeaWay() ' Partially hard coded for ease of explanation
' data range info
Dim WsS As Worksheet: Set WsS = ThisWorkbook.Worksheets("Sorting")
Dim RngToSort As Range: Set RngToSort = WsS.Range("B11:E16")
' Set RngToSort = Selection ' ' Selection.JPG : https://imgur.com/HnCdBt8
Dim arrTS() As Variant ' This is somewhat redundant for this version and could be replaced by arrOrig()
Let arrTS() = RngToSort.Value
' Index idea variables
Let arrOrig() = arrTS()
Let arrIndx() = arrTS()
Let Cms() = Evaluate("=Column(A:F)") ' Convenient way to get
Let Rs() = Evaluate("=Row(1:6)") ' Initial row indicies
' Initial row indicies from full original range´of rows
Dim strRows As String, Cnt As Long: Let strRows = " "
For Cnt = 1 To 6
Let strRows = strRows & Rs(Cnt, 1) & " "
Next Cnt
' we should have now strRows = " 1 2 3 4 5 6 "
Call BubblesIndexIdeaWay(1, arrIndx(), strRows, " 1 Asc 3 Asc 2 Asc ")
' Call BubblesIndexIdeaWay(1, arrIndx(), strRows, " 1 Asc ")
' Call BubblesIndexIdeaWay(1, arrIndx(), strRows, " 1 Asc 3 Asc ")
' Demo output
Let WsS.Range("B31").Resize(RngToSort.Rows.Count, RngToSort.Columns.Count).Value = arrIndx()
End Sub


_.____________________-

In the over next post the routine Sub BubblesIndexIdeaWay( __) will be developed. This will basically involve minor modifications to Sub Bubbles(__)

Before the final conversion , I will refresh and review the situation and add some extra lines to the above calling routine:
Row and Column indicies, Rs() and Cms()
For demonstration purposes, I will add a couple of extra final output lines to the calling routine, Sub Call_Sub_BubblesIndexIdeaWay()
These lines will simply paste out the initial and final row and column indices.

[DocAElstein]

Refresh: Current stand of things, Initial Sub BubblesIndexIdeaWay as Sub Bubbles
Before the final conversion , I will refresh and review the situation and add some extra lines to the above calling routine:
Row and Column indicies, Rs() and Cms()
For demonstration purposes, I will add a couple of extra final output lines to the calling routine, Sub Call_Sub_BubblesIndexIdeaWay()
These lines will simply paste out the initial and final row and column indices.

Initially I will run an intermediate routine , (which is a version of the last recursion routine) , from the below Calling routine. That version of the last recursion routine is here: http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11071&viewfull=1#post11071
That referenced routine is simply Sub Bubbles(), with the name changed to Sub BubblesIndexIdeaWay(), and the two recursion Calling code lines changed from
Call Bubbles(CopyNo + 1, arsRef(), strRws, strKeys)
to
Call BubblesIndexIdeaWay(CopyNo + 1, arsRef(), strRws, strKeys)

If I use this below calling routine, (Sub Call_Sub_BubblesIndexIdeaWay() ) , using our test data range ( http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11073&viewfull=1#post11073 ) , to Call the intermediate recursion routine ( http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11071&viewfull=1#post11071 ) , then I get the results shown.

Calling routine,
Sub Call_Sub_BubblesIndexIdeaWay()
Here: http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11074&viewfull=1#post11074

Original data range with added row and column indices
Row\Col
A
B
C
D
E

10
1
2
3
4


11
1
1
5
3a


12
2
9
9
9b


13
3
1
4
2c


14
4
8
8
8d


15
5
1
3
2e


16
6
7
7
7f
Worksheet: Sorting



Intermediate Output : Note row indicies are incorrect: They are not yet reordered. This is one of the main issues in the next post.
Row\Col
A
B
C
D
E

30
1
2
3
4


31
1
1
3
2e


32
2
1
4
2c


33
3
1
5
3a


34
4
7
7
7f


35
5
8
8
8d


36
6
9
9
9b
Worksheet: Sorting

[DocAElstein]

Final conversion of Sub Bubbles to Sub BubblesIndexIdeaWay

As we have the current stand of the routines, as discussed in the last post, we have the final Calling routine, ( Sub Call_Sub_BubblesIndexIdeaWay(), http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11074&viewfull=1#post11074 )
But currently , Sub BubblesIndexIdeaWay , is basically, Sub Bubbles
This Final conversion of Sub Bubbles to Sub BubblesIndexIdeaWay will be done in twp parts: Firstly we will get the same results in arrIndx() as we already have in arsRef(). Then we will remove the redundant bits

Part 1 Additions to make arrIndx() give us final Output
From the last post, we see that we do not yet have the reordered Rs() at the end of the routines running. Therefore this will give the wrong final results
____________arrIndx() = Application.Index(arrOrig(), Rs(), Cms())
Getting the correct Rs() is the main requirement to get Sub Bubbles working

We need to remind ourselves what goes on in basic the sort, after which it is fairly obvious how we get the reordered indices:
In the bubble sort, we reach a point whereby rows need to be swapped. Lets consider as random example that rows 2 and 3 need to be swapped, so
row 2 become row 3 , and row 3 becomes row 2.
As discussed we cannot do that easily in computing. In computing we need to have a temporary variable, pseudo
Temp = row 2
row 2 = row 3
row 3 = Temp

Our variable Rs() , holds the indices we need to apply to the original array to get the new reordered array. The current coding swaps rows. Rs() holds the indices of our original array. If we want the indicies in that Rs() , when applied to the original array, to return the reordered array, then they need to be swapped at exactly as the rows currently in arsRef() are.
Putting that again in just slightly different wording. The original rows have an original row indicie. As the rows are moved ( swapped) up and down in arsRef() , the corresponding row indicie needs to be moved excactly the same in Rs()
I can’t put that any clearer. You need to take a bit of time to let that run through your head, and then it should make sense.

Once we have understood that last bit, then the coding modification may be obvious: At the current swap section we need to swap the indicies in Rs()
Currently we have this:

Dim Clms As Long '-------| with the condition met a loop is done for all columns in the array in which those two values used in the comparison are replaced at each column
For Clms = 1 To UBound(arsRef(), 2)
Let Temp = arsRef(rOuter, Clms): Let arsRef(rOuter, Clms) = arsRef(rInner, Clms): Let arsRef(rInner, Clms) = Temp
Next Clms '----------| for each column in the array at the two rows rOuter and rInner
We simply add there a swap for the idiocies

Dim Clms As Long '-------| with the condition met a loop is done for all columns in the array in which those two values used in the comparison are replaced at each column
For Clms = 1 To UBound(arsRef(), 2)
Let Temp = arsRef(rOuter, Clms): Let arsRef(rOuter, Clms) = arsRef(rInner, Clms): Let arsRef(rInner, Clms) = Temp
Next Clms '----------| for each column in the array at the two rows rOuter and rInner
Dim TempRs As Long
Let TempRs = Rs(rOuter, 1): Let Rs(rOuter, 1) = Rs(rInner, 1): Let Rs(rInner, 1) = TempRs

If you make just that modification and run the complete coding, ( see here for example http://www.excelfox.com/forum/showthread.php/2306-Just-testing-Testing-some-sort-routines-No-reply-needed?p=11076&viewfull=1#post11076 ) , then you will find that the final indicies are correct…. Compare the input:

1 2 3 4

1 1 5 3 a

2 9 9 9 b

3 1 4 2 c

4 8 8 8 d

5 1 3 2 e

6 7 7 7 f

Here the output

1 2 3 4

5 1 3 2 e

3 1 4 2 c

1 1 5 3 a

6 7 7 7 f

2 9 9 9 b

4 8 8 8 d


We have the required Rs() , and can add the code line to apply to the formula , arrIndx() = Application.Index(arrOrig(), Rs(), Cms())
We could add this code line now anywhere, for example at the end of the calling routine

Let RngDemoOutput = arrIndx()
Let RngDemoOutput.Offset(-1, 0).Resize(1, 4).Value = Cms(): RngToSort.Offset(-1, 0).Resize(1, 4).Font.Color = vbRed
Let RngDemoOutput.Offset(0, -1).Resize(6, 1).Value = Rs(): RngToSort.Offset(0, -1).Resize(6, 1).Font.Color = vbRed
'
Let RngDemoOutput.Offset(RngDemoOutput.Rows.Count, 0) = Application.Index(arrOrig(), Rs(), Cms())
End Sub
The above coding modification would give us this output:
Row\Col
A
B
C
D
E

30 1 2 3 4


31 5 1 3 2 e


32 3 1 4 2 c


33 1 1 5 3 a


34 6 7 7 7 f


35 2 9 9 9 b


36 4 8 8 8 d


37 1 3 2 e


38 1 4 2 c


39 1 5 3 a


40 7 7 7 f


41 9 9 9 b


42 8 8 8 d


The above use of the formula, Application.Index(arrOrig(), Rs(), Cms()) , is helpful to demo its use.
However, before we can move on in the next post to removal of redundant things, that is to say, code actions, we must do some further modifications.

_.___________________

The next two modifications are not immediate obvious at this stage, but it should become clearer why we need to do this, once the removing of redundant parts and associated modifications are discussed in the next post.

Rem 2
The modification so far will not be enough once we remove the redundant parts, since we must pass the modified full arrays at the Call code lines within the recursion routine. We will therefore need to have available the modified array, as given by arrIndx() , after the sort , Rem 1, and before the recursion call section, Rem 3

Next rOuter ' =============End Rem 1================================================= ================
Rem 2
Let arrIndx() = Application.Index(arrOrig(), Rs(), Cms())
Rem 3 Preparation for possible recursion Call

_._____________
As final modification in preparation of the removal of redundant parts, we need to use and send the arrIndx() rather than arsRef()

_._____________
As mentioned, these last two modifications are not immediate obvious at this stage, but it should become clearer why we need to do this, once the removing of redundant parts and associated modifications are discussed in the next post.
_.___________
Here are the final modifications before removal of redundant parts….
http://www.excelfox.com/forum/showthread.php/2306-Just-testing-Testing-some-sort-routines-No-reply-needed?p=11077&viewfull=1#post11077

The next post considers modifications to remove redundant code actions

[DocAElstein]

Final conversion of Sub Bubbles to Sub BubblesIndexIdeaWay

Step 2 removal of redundant coding
In the last post we modified indices values in main sort loop sorting
to get the modified Rs() to use in this
arrIndx() = Application.Index(arrIndx(), Rs(), Cms())

What we did was, at this section _...
__ For Clms = 1 To UBound(arsRef(), 2)
___ Let Temp = arsRef(rOuter, Clms): Let arsRef(rOuter, Clms) = arsRef(rInner, Clms): Let arsRef(rInner, Clms) = Temp
__ Next
_... we used the row information in the variables rOuter and rInner, to do same swap for row indices,
Dim TempRs As Long
_ TempRs = Rs(rOuter, 1): Let Rs(rOuter, 1) = Rs(rInner, 1): Let Rs(rInner, 1) = TempRs

Column elements in a row swapping
One of the main distinguishing characteristics of the Index idea way, is that we sort the row indices in to a new order, and then apply the code line, .._
arrIndx() = App.Ind(arrOrig() , rowindicis, columnindicies
_.. to get the new order in one go.
However we must be careful. The immediate conclusion might possibly be that all the sections swapping all column elements in a row are now redundant and so can be removed. The is almost true, but not quite: The reordering of the row indicia is following directly the bubbling through sort of the column being used in the current sort. We must therefore continue to sort/ swap this column element e currently have this
____ For Clms = 1 To UBound(arsRef(), 2)
_____ Let Temp = arsRef(rOuter, Clms): Let arsRef(rOuter, Clms) = arsRef(rInner, Clms): Let arsRef(rInner, Clms) = Temp
____ Next Clms
___ Let TempRs = Rs(rOuter, 1): Let Rs(rOuter, 1) = Rs(rInner, 1): Let Rs(rInner, 1) = TempRs

That needs now to be modified now so that they just swap those two row values in the column currently used to base the sort on, ( as well as still doing the swap of the row indicia )
We note that Clms was the variable for all columns in the loop for all columns in the swapping in the code snippet above , and Clm was the variable for the current column being used to determine the current sort order. So we no longer need that loop to swap all columns, - that can be removed. But if we do this removal, we must add a swap section for the Clm column …_


___ Let Temp = arrIndx(rOuter, Clm): Let arrIndx(rOuter, Clm) = arrIndx(rInner, Clm): Let arrIndx(rInner, Clm) = Temp
___ Let TempRs = Rs(rOuter, 1): Let Rs(rOuter, 1) = Rs(rInner, 1): Let Rs(rInner, 1) = TempRs


The complete bubble loop section now looks like this

Rem 1 Bubble sort
Dim rOuter As Long ' ========"Left Hand"=====================Outer Loop=====================================
' For rOuter = Rs(LBound(Rs(), 1), 1) To Rs(UBound(Rs(), 1), 1) - 1 ' THIS WOULD ONLY WORK FOR Copy No 1 For first row indicie to last but one row indicie - I could do this for copy 1
For rOuter = Left(Trim(strRws), InStr(1, Trim(strRws), " ", vbBinaryCompare) - 1) To Right(Trim(strRws), Len(Trim(strRws)) - InStrRev(Trim(strRws), " ", -1, vbBinaryCompare)) - 1 ' ImmediteWindowToHelpGetAtStuff.JPG : https://imgur.com/yGqmYSu : http://www.eileenslounge.com/viewtopic.php?f=27&t=16407&p=247121#p247121
'For rOuter = 1 To 5 ' For first run
Dim rInner As Long ' -------Inner Loop-------------"Right Hand"--------------------------
For rInner = rOuter + 1 To Rs(UBound(Rs(), 1), 1) ' from just above left hand through all the rest
If CDbl(arsRef(rOuter, Clm)) > CDbl(arsRef(rInner, Clm)) Then
Dim Temp As Variant ' I want to Swap those 2 above - I cant easilly in any coding change two values simulataneosly. So one of them Element values will put in this temporary place. This Element Values will then be given the other. Finally the other Element will be given this temporary value
Let Temp = arrIndx(rOuter, Clm): Let arrIndx(rOuter, Clm) = arrIndx(rInner, Clm): Let arrIndx(rInner, Clm) = Temp
Dim TempRs As Long
Let TempRs = Rs(rOuter, 1): Let Rs(rOuter, 1) = Rs(rInner, 1): Let Rs(rInner, 1) = TempRs
Else
End If
Next rInner ' -----------------------------------------------------------------------
Next rOuter ' ==================End=Rem 1================================================= ==============
Rem 2
Let arrIndx() = Application.Index(arrOrig(), Rs(), Cms())

Here is the full final coding for Sub Call_Sub_BubblesIndexIdeaWay() and Sub BubblesIndexIdeaWay(__
http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11074&viewfull=1#post11074
http://www.excelfox.com/forum/showthread.php/2307-VBA-Range-Sort-with-arrays-Alternative-for-simple-use?p=11078&viewfull=1#post11078


_.__________________________-

The next part of this Thread will be to extend the shortened demo coding from the last few posts to a full coding example.
Effectively this will be a slightly more efficient version of Sub SimpleArraySort8(__
http://www.excelfox.com/forum/showthread.php/2307-VBA-Range-Sort-with-arrays-Alternative-for-simple-use?p=11054&viewfull=1#post11054
http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11056&viewfull=1#post11056
http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11058&viewfull=1#post11058

_...... I expect I will do that later as a bit of revision when it snows next winter , and I come back inside to sit more on my bum and do computer stuff… Until then I’m off to do more useful things outside…. See ya x






https://excelfox.com/forum/showthread.php/2056-Appendix-Thread-2-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11079&viewfull=1#post11079






Ref
https://excelribbon.tips.net/T009600_Sorting_Letters_and_Numbers.html
http://www.eileenslounge.com/viewtopic.php?p=247043#p247043

[DocAElstein]

In the next posts are miscillaneous notes for future referrence

I may or may not edit and re use them as time goes on ...


https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/watch?v=SIDLFRkUEIo&lc=UgzTF5vvB67Zbfs9qvx4AaABAg (https://www.youtube.com/watch?v=SIDLFRkUEIo&lc=UgzTF5vvB67Zbfs9qvx4AaABAg)
https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgxLtKj969oiIu7zNb94AaABAg (https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgxLtKj969oiIu7zNb94AaABAg)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgzMCQUIQgrbec400jl4AaABAg (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgzMCQUIQgrbec400jl4AaABAg)
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg (https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg)
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugx12mI-a39T41NaZ8F4AaABAg.9iDQqIP56NV9iFD0AkeeJG (https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugx12mI-a39T41NaZ8F4AaABAg.9iDQqIP56NV9iFD0AkeeJG)
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugxa2VYHMWJWXA6QI294AaABAg.9irLgSdeU3r9itU7zdnW Hw (https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugxa2VYHMWJWXA6QI294AaABAg.9irLgSdeU3r9itU7zdnW Hw)
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgzFkoI0n_BxwnwVMcZ4AaABAg (https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgzFkoI0n_BxwnwVMcZ4AaABAg)
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg.9htChVuaX9W9htG01cKBzX (https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg.9htChVuaX9W9htG01cKBzX)
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg.9ht16tzryC49htJ6TpIO XR (https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg.9ht16tzryC49htJ6TpIO XR)
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgwMKwGZpDjv7vi7pCx4AaABAg (https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgwMKwGZpDjv7vi7pCx4AaABAg)
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg.9ht16tzryC49htOKs4jh 3M (https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg.9ht16tzryC49htOKs4jh 3M)
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgxVW-am20rQ5GFuJ9F4AaABAg (https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgxVW-am20rQ5GFuJ9F4AaABAg)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)


https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg (https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg)
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNsaS3Lp1 (https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNsaS3Lp1)
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgR1EPUkhw (https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgR1EPUkhw)
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNe_XC-jK (https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNe_XC-jK)
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNPOdiDuv (https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNPOdiDuv)
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgN7AC7wAc (https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgN7AC7wAc)
https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm9wlhQrYJP 3M (https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm9wlhQrYJP 3M)
https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg (https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg)
https://www.youtube.com/watch?v=DVFFApHzYVk&lc=Ugyi578yhj9zShmhuPl4AaABAg (https://www.youtube.com/watch?v=DVFFApHzYVk&lc=Ugyi578yhj9zShmhuPl4AaABAg)
https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgxvxlnuTRWiV6MUZB14AaABAg (https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgxvxlnuTRWiV6MUZB14AaABAg)
https://www.youtube.com/watch?v=_8i1fVEi5WY&lc=Ugz0ptwE5J-2CpX4Lzh4AaABAg (https://www.youtube.com/watch?v=_8i1fVEi5WY&lc=Ugz0ptwE5J-2CpX4Lzh4AaABAg)
https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgxoHAw8RwR7VmyVBUt4AaABAg.9C-br0lEl8V9xI0_6pCaR9 (https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgxoHAw8RwR7VmyVBUt4AaABAg.9C-br0lEl8V9xI0_6pCaR9)
https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=Ugz5DDCMqmHLeEjUU8t4AaABAg.9bl7m03Onql9xI-ar3Z0ME (https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=Ugz5DDCMqmHLeEjUU8t4AaABAg.9bl7m03Onql9xI-ar3Z0ME)
https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgxYnpd9leriPmc8rPd4AaABAg.9gdrYDocLIm9xI-2ZpVF-q (https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgxYnpd9leriPmc8rPd4AaABAg.9gdrYDocLIm9xI-2ZpVF-q)
https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgyjoPLjNeIAOMVH_u94AaABAg.9id_Q3FO8Lp9xHyeYSuv 1I (https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgyjoPLjNeIAOMVH_u94AaABAg.9id_Q3FO8Lp9xHyeYSuv 1I)
https://www.reddit.com/r/windowsxp/comments/pexq9q/comment/k81ybvj/?utm_source=reddit&utm_medium=web2x&context=3 (https://www.reddit.com/r/windowsxp/comments/pexq9q/comment/k81ybvj/?utm_source=reddit&utm_medium=web2x&context=3)
https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg (https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg)
https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm9wlhQrYJP 3M (https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm9wlhQrYJP 3M)
ttps://www.youtube.com/watch?v=LP9fz2DCMBE (ttps://www.youtube.com/watch?v=LP9fz2DCMBE)
https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg (https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg)
https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg.9wdo_rWgxSH9wdpcYqrv p8 (https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg.9wdo_rWgxSH9wdpcYqrv p8)
ttps://www.youtube.com/watch?v=bFxnXH4-L1A (ttps://www.youtube.com/watch?v=bFxnXH4-L1A)
https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxuODisjo6cvom7O-B4AaABAg.9w_AeS3JiK09wdi2XviwLG (https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxuODisjo6cvom7O-B4AaABAg.9w_AeS3JiK09wdi2XviwLG)
https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxBU39bTptFznDC1PJ4AaABAg (https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxBU39bTptFznDC1PJ4AaABAg)
ttps://www.youtube.com/watch?v=GqzeFYWjTxI (ttps://www.youtube.com/watch?v=GqzeFYWjTxI)
https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgwJnJDJ5JT8hFvibt14AaABAg (https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgwJnJDJ5JT8hFvibt14AaABAg)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[DocAElstein]

??? URL link to this post is https://excelfox.com/forum/showthread.php/2307-VBA-Range-Sort-with-arrays-Alternative-for-simple-use/page3#post15512
https://excelfox.com/forum/showthread.php/2307-VBA-Range-Sort-with-arrays-Alternative-for-simple-use/page3#post15512
https://excelfox.com/forum/showthread.php/2307-VBA-Range-Sort-with-arrays-Alternative-for-simple-use?p=15512#post15512
https://excelfox.com/forum/showthread.php/2307-VBA-Range-Sort-with-arrays-Alternative-for-simple-use?p=15512#post15512







Some extra notes for this Thread
https://eileenslounge.com/viewtopic.php?f=30&t=36415
this post
https://eileenslounge.com/viewtopic.php?p=282569&sid=c152f3e01b8240fac89d77316ed12315#p282569

Data in column Q, outputs Columns S and T
S is like the original macro in the main question (https://eileenslounge.com/viewtopic.php?p=282518#p282518)
Column T has the output got from my two macros below,
Sub TakeThat1()
Sub TakeThat2()

_____ Workbook: Bubble Sort Demo 1.xlsm ( Using Excel 2007 32 bit )
Row\Col
Q
R
S
T
U

1
1
1
1


2
2
12
12


3
3
13
13


4
21
2


5
2
23


6
23
3


7
31


8
32


9
3


10
Worksheet: Sorting


' https://eileenslounge.com/viewtopic.php?f=30&t=36415 https://eileenslounge.com/viewtopic.php?p=282569&sid=c152f3e01b8240fac89d77316ed12315#p282569
'
Sub TakeThat1()
Rem 1 data
Dim Ay() As Variant, Bea() As Variant: ReDim Bea(1 To 1000)
Dim Eye As Long, AyeAye As Long, Kay As Long
Let Ay() = Range("Q1").CurrentRegion.Value2
Rem 2 Do It
Dim Dik As Object: Set Dik = CreateObject("Scripting.Dictionary")
For Eye = LBound(Ay(), 1) To UBound(Ay(), 1)
For AyeAye = LBound(Ay(), 1) To UBound(Ay(), 1)
If Ay(Eye, 1) = Ay(AyeAye, 1) Then
Let Kay = Kay + 1
Let Bea(Kay) = Ay(Eye, 1)
If Not Dik.exists(BubSrt(Bea(Kay))) Then Dik.Add Key:=Bea(Kay), Item:="AnyThong"
Else
Let Kay = Kay + 1
Let Bea(Kay) = Ay(Eye, 1) & Ay(AyeAye, 1)
If Not Dik.exists(BubSrt(Bea(Kay))) Then Dik.Add Key:=Bea(Kay), Item:="AnyThong"
End If
Next AyeAye
Next Eye

Dim UnicBea() As Variant: Let UnicBea() = Dik.Keys()

Rem 3 Output
Range("S1:T20").ClearContents
' Let Range("S1").Resize(UBound(Bea()) + 1, 1).Value2 = Application.Transpose(Bea())
Let Range("S1").Resize(10, 1).Value2 = Application.Transpose(Bea())
Let Range("T1").Resize(UBound(UnicBea()) + 1, 1).Value2 = Application.Transpose(UnicBea())
Let Range("T1").Resize(UBound(UnicBea()) + 1, 1).Value2 = Application.Index(UnicBea(), Evaluate("=row(1:" & UBound(UnicBea()) + 1 & ")/row(1:" & UBound(UnicBea()) + 1 & ")"), Evaluate("=row(1:" & UBound(UnicBea()) + 1 & ")"))
End Sub

Sub TakeThat2()
Rem 1 data
Dim Ay() As Variant, Bea() As Variant: ReDim Bea(1 To 1000)
Dim Eye As Long, AyeAye As Long, Kay As Long
Let Ay() = Range("Q1").CurrentRegion.Value2
Rem 2 Do It
Dim strUnic As String: Let strUnic = " "
For Eye = LBound(Ay(), 1) To UBound(Ay(), 1)
For AyeAye = LBound(Ay(), 1) To UBound(Ay(), 1)
If Ay(Eye, 1) = Ay(AyeAye, 1) Then
Let Kay = Kay + 1
Let Bea(Kay) = Ay(Eye, 1)
If InStr(1, strUnic, " " & BubSrt(Bea(Kay)) & " ", vbBinaryCompare) = 0 Then Let strUnic = strUnic & BubSrt(Bea(Kay)) & " "
Else
Let Kay = Kay + 1
Let Bea(Kay) = Ay(Eye, 1) & Ay(AyeAye, 1)
If InStr(1, strUnic, " " & BubSrt(Bea(Kay)) & " ", vbBinaryCompare) = 0 Then Let strUnic = strUnic & BubSrt(Bea(Kay)) & " "
End If
Next AyeAye
Next Eye
Let strUnic = Mid(strUnic, 2, Len(strUnic) - 2) ' Take off the first and last space

Dim UnicBea() As String: Let UnicBea = Split(strUnic, " ", -1, vbBinaryCompare)
Rem 3 Output
Range("S1:T20").ClearContents
' Let Range("S1").Resize(UBound(Bea()) + 1, 1).Value2 = Application.Transpose(Bea())
Let Range("S1").Resize(10, 1).Value2 = Application.Transpose(Bea())
Let Range("T1").Resize(UBound(UnicBea()) + 1, 1).Value2 = Application.Transpose(UnicBea())
Let Range("T1").Resize(UBound(UnicBea()) + 1, 1).Value2 = Application.Index(UnicBea(), Evaluate("=row(1:" & UBound(UnicBea()) + 1 & ")/row(1:" & UBound(UnicBea()) + 1 & ")"), Evaluate("=row(1:" & UBound(UnicBea()) + 1 & ")"))
End Sub

Function BubSrt(ByVal Thong As String) As String
Dim Buf() As String: Let Buf() = Split(StrConv(Thong, vbUnicode), Chr$(0)): ReDim Preserve Buf(UBound(Buf()) - 1) ' https://stackoverflow.com/questions/13195583/split-string-into-array-of-characters
Dim Ey As Long, Jay As Long
Dim Temp As Long
For Ey = LBound(Buf()) To UBound(Buf()) - 1
For Jay = Ey + 1 To UBound(Buf())
If Buf(Ey) > Buf(Jay) Then
Let Temp = Buf(Jay)
Let Buf(Jay) = Buf(Ey)
Let Buf(Ey) = Temp
End If
Next Jay
Next Ey
Let BubSrt = Join(Buf(), "")
End Function




Sub Testie() ' https://stackoverflow.com/questions/13195583/split-string-into-array-of-characters
Dim Thong As String: Let Thong = "132"
Dim Buf() As String: Let Buf() = Split(StrConv(Thong, vbUnicode), Chr$(0))
ReDim Preserve Buf(UBound(Buf()) - 1)
End Sub
Sub Testie2()
Dim ThingThong As String: Let ThingThong = "132"
Let ThingThong = BubSrt(ThingThong)
End Sub

[DocAElstein]

link to this post https://excelfox.com/forum/showthread.php/2307-VBA-Range-Sort-with-arrays-Alternative-for-simple-use/page3#post15513
https://excelfox.com/forum/showthread.php/2307-VBA-Range-Sort-with-arrays-Alternative-for-simple-use/page3#post15513






Sub Take3()
Rem 1 data
Dim Ay() As Variant
Dim Eye As Long, AyeAye As Long, Kay As Long
Let Ay() = Range("Q1").CurrentRegion.Value2
Rem 2 Do It
Dim Dik As Object: Set Dik = CreateObject("Scripting.Dictionary")
For Eye = LBound(Ay(), 1) To UBound(Ay(), 1)
For AyeAye = LBound(Ay(), 1) To UBound(Ay(), 1)
If Ay(Eye, 1) = Ay(AyeAye, 1) Then
Let Kay = Kay + 1
'Let Bea(Kay) = Ay(Eye, 1)
If Not Dik.exists(BubSrt(Ay(Eye, 1))) Then Dik.Add Key:=Ay(Eye, 1), Item:="AnyThong"
Else
Let Kay = Kay + 1
'Let Bea(Kay) = Ay(Eye, 1) & Ay(AyeAye, 1)
If Not Dik.exists(BubSrt(Ay(Eye, 1) & Ay(AyeAye, 1))) Then Dik.Add Key:=Ay(Eye, 1) & Ay(AyeAye, 1), Item:="AnyThong"
End If
Next AyeAye
Next Eye

Dim UnicBea() As Variant: Let UnicBea() = Dik.Keys()

Rem 3 Output
Range("S1:T20").ClearContents
Let Range("T1").Resize(UBound(UnicBea()) + 1, 1).Value2 = Application.Transpose(UnicBea())
Let Range("T1").Resize(UBound(UnicBea()) + 1, 1).Value2 = Application.Index(UnicBea(), Evaluate("=row(1:" & UBound(UnicBea()) + 1 & ")/row(1:" & UBound(UnicBea()) + 1 & ")"), Evaluate("=row(1:" & UBound(UnicBea()) + 1 & ")"))
End Sub

Sub Take4()
Rem 1 data
Dim Ay() As Variant
Dim Eye As Long, AyeAye As Long, Kay As Long
Let Ay() = Range("Q1").CurrentRegion.Value2
Rem 2 Do It
Dim strUnic As String: Let strUnic = " "
For Eye = LBound(Ay(), 1) To UBound(Ay(), 1)
For AyeAye = LBound(Ay(), 1) To UBound(Ay(), 1)
If Ay(Eye, 1) = Ay(AyeAye, 1) Then
Let Kay = Kay + 1
If InStr(1, strUnic, " " & BubSrt(Ay(Eye, 1)) & " ", vbBinaryCompare) = 0 Then Let strUnic = strUnic & BubSrt(Ay(Eye, 1)) & " "
Else
Let Kay = Kay + 1
If InStr(1, strUnic, " " & BubSrt(Ay(Eye, 1) & Ay(AyeAye, 1)) & " ", vbBinaryCompare) = 0 Then Let strUnic = strUnic & BubSrt(Ay(Eye, 1) & Ay(AyeAye, 1)) & " "
End If
Next AyeAye
Next Eye
Let strUnic = Mid(strUnic, 2, Len(strUnic) - 2) ' Take off the first and last space

Dim UnicBea() As String: Let UnicBea = Split(strUnic, " ", -1, vbBinaryCompare)
Rem 3 Output
Range("S1:T20").ClearContents
Let Range("T1").Resize(UBound(UnicBea()) + 1, 1).Value2 = Application.Transpose(UnicBea())
Let Range("T1").Resize(UBound(UnicBea()) + 1, 1).Value2 = Application.Index(UnicBea(), Evaluate("=row(1:" & UBound(UnicBea()) + 1 & ")/row(1:" & UBound(UnicBea()) + 1 & ")"), Evaluate("=row(1:" & UBound(UnicBea()) + 1 & ")"))
End Sub


Function BubSrt(ByVal Thong As String) As String
Dim Buf() As String: Let Buf() = Split(StrConv(Thong, vbUnicode), Chr$(0)): ReDim Preserve Buf(UBound(Buf()) - 1) ' https://stackoverflow.com/questions/13195583/split-string-into-array-of-characters
Dim Ey As Long, Jay As Long
Dim Temp As Long
For Ey = LBound(Buf()) To UBound(Buf()) - 1
For Jay = Ey + 1 To UBound(Buf())
If Buf(Ey) > Buf(Jay) Then
Let Temp = Buf(Jay)
Let Buf(Jay) = Buf(Ey)
Let Buf(Ey) = Temp
End If
Next Jay
Next Ey
Let BubSrt = Join(Buf(), "")
End Function

[DocAElstein]

link to this post https://excelfox.com/forum/showthread.php/2307-VBA-Range-Sort-with-arrays-Alternative-for-simple-use?p=15514#post15514
https://excelfox.com/forum/showthread.php/2307-VBA-Range-Sort-with-arrays-Alternative-for-simple-use?p=15514#post15514
https://excelfox.com/forum/showthread.php/2307-VBA-Range-Sort-with-arrays-Alternative-for-simple-use/page3#post15514
https://excelfox.com/forum/showthread.php/2307-VBA-Range-Sort-with-arrays-Alternative-for-simple-use/page3#post15514








Function

required for last two posts




Function BubSrt(ByVal Thong As String) As String
Dim Buf() As String: Let Buf() = Split(StrConv(Thong, vbUnicode), Chr$(0)): ReDim Preserve Buf(UBound(Buf()) - 1) ' https://stackoverflow.com/questions/13195583/split-string-into-array-of-characters
Dim Ey As Long, Jay As Long
Dim Temp As Long
For Ey = LBound(Buf()) To UBound(Buf()) - 1
For Jay = Ey + 1 To UBound(Buf())
If Buf(Ey) > Buf(Jay) Then
Let Temp = Buf(Jay)
Let Buf(Jay) = Buf(Ey)
Let Buf(Ey) = Temp
End If
Next Jay
Next Ey
Let BubSrt = Join(Buf(), "")
End Function

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://eileenslounge.com/viewtopic.php?p=244184#p244184 (https://eileenslounge.com/viewtopic.php?p=244184#p244184)
https://eileenslounge.com/viewtopic.php?p=246586#p246586 (https://eileenslounge.com/viewtopic.php?p=246586#p246586)
https://eileenslounge.com/viewtopic.php?p=246112#p246112 (https://eileenslounge.com/viewtopic.php?p=246112#p246112)
https://eileenslounge.com/viewtopic.php?p=246112#p246112 (https://eileenslounge.com/viewtopic.php?p=246112#p246112)
https://eileenslounge.com/viewtopic.php?p=245761#p245761 (https://eileenslounge.com/viewtopic.php?p=245761#p245761)
https://eileenslounge.com/viewtopic.php?p=245722#p245722 (https://eileenslounge.com/viewtopic.php?p=245722#p245722)
https://eileenslounge.com/viewtopic.php?p=245616#p245616 (https://eileenslounge.com/viewtopic.php?p=245616#p245616)
https://eileenslounge.com/viewtopic.php?p=247043#p247043 (https://eileenslounge.com/viewtopic.php?p=247043#p247043)
https://www.excelfox.com/forum/showthread.php/2307-VBA-Range-Sort-with-arrays-Alternative-for-simple-use (https://www.excelfox.com/forum/showthread.php/2307-VBA-Range-Sort-with-arrays-Alternative-for-simple-use)
https://eileenslounge.com/viewtopic.php?p=245238#p245238 (https://eileenslounge.com/viewtopic.php?p=245238#p245238)
https://eileenslounge.com/viewtopic.php?p=245131#p245131 (https://eileenslounge.com/viewtopic.php?p=245131#p245131)
https://eileenslounge.com/viewtopic.php?f=18&t=31638 (https://eileenslounge.com/viewtopic.php?f=18&t=31638)
https://eileenslounge.com/viewtopic.php?p=244579#p244579 (https://eileenslounge.com/viewtopic.php?p=244579#p244579)
https://eileenslounge.com/viewtopic.php?p=244648#p244648 (https://eileenslounge.com/viewtopic.php?p=244648#p244648)
https://eileenslounge.com/viewtopic.php?p=244647#p244647 (https://eileenslounge.com/viewtopic.php?p=244647#p244647)
https://eileenslounge.com/viewtopic.php?p=244577#p244577 (https://eileenslounge.com/viewtopic.php?p=244577#p244577)
https://eileenslounge.com/viewtopic.php?p=245201#p245201 (https://eileenslounge.com/viewtopic.php?p=245201#p245201)
https://eileenslounge.com/viewtopic.php?p=243975#p243975 (https://eileenslounge.com/viewtopic.php?p=243975#p243975)
https://eileenslounge.com/viewtopic.php?p=243884#p243884 (https://eileenslounge.com/viewtopic.php?p=243884#p243884)
https://eileenslounge.com/viewtopic.php?p=242439#p242439 (https://eileenslounge.com/viewtopic.php?p=242439#p242439)
https://eileenslounge.com/viewtopic.php?p=243595#p243595 (https://eileenslounge.com/viewtopic.php?p=243595#p243595)
https://eileenslounge.com/viewtopic.php?p=243589#p243589 (https://eileenslounge.com/viewtopic.php?p=243589#p243589)
https://eileenslounge.com/viewtopic.php?p=243589#p243589 (https://eileenslounge.com/viewtopic.php?p=243589#p243589)
https://eileenslounge.com/viewtopic.php?p=243002#p243002 (https://eileenslounge.com/viewtopic.php?p=243002#p243002)
https://www.eileenslounge.com/viewtopic.php?p=242761#p242761 (https://www.eileenslounge.com/viewtopic.php?p=242761#p242761)
https://eileenslounge.com/viewtopic.php?p=242459#p242459 (https://eileenslounge.com/viewtopic.php?p=242459#p242459)
https://eileenslounge.com/viewtopic.php?p=242054#p242054 (https://eileenslounge.com/viewtopic.php?p=242054#p242054)
https://eileenslounge.com/viewtopic.php?p=241404#p241404 (https://eileenslounge.com/viewtopic.php?p=241404#p241404)
https://eileenslounge.com/viewtopic.php?p=229145#p229145 (https://eileenslounge.com/viewtopic.php?p=229145#p229145)
https://eileenslounge.com/viewtopic.php?p=228710#p228710 (https://eileenslounge.com/viewtopic.php?p=228710#p228710)
https://eileenslounge.com/viewtopic.php?p=226938#p226938 (https://eileenslounge.com/viewtopic.php?p=226938#p226938)
https://eileenslounge.com/viewtopic.php?f=18&t=28885 (https://eileenslounge.com/viewtopic.php?f=18&t=28885)
https://eileenslounge.com/viewtopic.php?p=222689#p222689 (https://eileenslounge.com/viewtopic.php?p=222689#p222689)
https://eileenslounge.com/viewtopic.php?p=221622#p221622 (https://eileenslounge.com/viewtopic.php?p=221622#p221622)
https://eileenslounge.com/viewtopic.php?f=27&t=22512 (https://eileenslounge.com/viewtopic.php?f=27&t=22512)
https://eileenslounge.com/viewtopic.php?f=26&t=26183 (https://eileenslounge.com/viewtopic.php?f=26&t=26183)
https://eileenslounge.com/viewtopic.php?f=26&t=26030 (https://eileenslounge.com/viewtopic.php?f=26&t=26030)
https://eileenslounge.com/viewtopic.php?p=202322#p202322 (https://eileenslounge.com/viewtopic.php?p=202322#p202322)
https://www.excelforum.com/word-formatting-and-general/1174522-finding-a-particular-word-phrase-in-word.html#post4604396 (https://www.excelforum.com/word-formatting-and-general/1174522-finding-a-particular-word-phrase-in-word.html#post4604396)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)