Peter, your ABSOLUTELY MAGICIAL, I looked at the quantity of your code and thought, "No way did he resolve this with only 46 lines of code" WOW, I can't tell you how happy I am to see this.
You are welcome.
1. I understand the TieBreakColsArr and what your doing except the evaluate part, I mean I know that your evaluating the last six, last three etc, but when I add the array to the watch window I not sure what its telling me at the evaluate parts.
The evaluate part makes an array of the relevant column numbers (even though it uses rows() to do it) for the particular sum. If we look at
TieBreakColsArr(8) = Evaluate("row(7:9)")
It makes an array of the numbers (7, 8, 9)
This is then used in the line
b(i, j) = Format(Application.Sum(Application.Index(a, i, TieBreakColsArr(j))), "000")
which becomes like
b(i, j) = Format(Application.Sum(Application.Index(a, i, {7,8,9})), "000")
So for the first row of data (i=1) the Sum adds (in pseudo code) Index(all the data, row 1, columns 7, 8 & 9) to get the front 9 last 3 total.
2. I see a and b are arrays, one with the row values and the other with the TieBreakColsArr array values and your using s to build a delimited string, after that I'm kind of lost. Could you explain please.
You are right about the delimited string. (It didn't really need
all the delimiters but that made it easier for me to see what was happening)
So, for the first player from the post #20 data, their sting is:
|075|037|025|012|004|038|025|013|004|004|004|004|004|005|004|004|004|004|004|004|005|004|003|005|004|003|006
This represents, in order of importance, the 27 tie-break
* amounts:
Total|Back 9 total|Back 9 last 6|Back 9 last 3|... etc
By formatting them all as 3-digits, all the strings are of identical length & can therefore be sorted in alphabetical order to achieve our goal.
Each of these strings is added
** into a "Sorted List" (as a Key) along with its row number (as an Item). A sorted list automatically orders its members from smallest to largest as they are entered.
Once we have processed all the rows into the Sorted List we just extract the Items back out of the Sorted List from the first (smallest) Key to the largest. Each time we use the Item (row number(s)) to put the position of that player into the results array (array Pos)
The 'Inc' variable is used to keep track of how many position numbers we have skipped due to ties.
* We actually have some duplicated tie-breakers that could have been eliminated but I felt it easier to leave them in for code understanding. For example
TieBreakColsArr(5) = 18 is using hole #18 as a tie-break, as is TieBreakColsArr(10), being the first one generated by
Code:
For i = 18 To 1 Step -1 'Individual hole cols in reverse
TieBreakColsArr(28 - i) = i
Next i
So TieBreakColsArr(10) will never get used to break a tie, as that tie would have already been broken by TieBreakColsArr(5)
** Actually, if one of these strings is already in the Sorted List, then all we do is add the new row number to the existing row number(s) already stored as the Item for that Key:
Code:
If SL.ContainsKey(s) Then
SL(s) = SL(s) & " " & i
Hope that made some sense. Usually easier to do than to describe.
Great work, Peter ...
... and full marks for patience!
Regards,
Thanks Rick. The solution is of course largely down to you introducing me to Sorted Lists recently.
The patience was because I had an interest in the problem, figuring I could get some practice in with these Sorted Lists.
