Trying To Break ties

[Chesley]

I have a column with a number of integers in column A, which has a number of ties in it. I need to be able to break the ties by using values in B, and if there is still ties, then got column c, and again, if there is ties go to column D,etc ( up to 7 columns with values to be used to break the Ties). I'm trying to do this in vba, however being new to vba , I haven't been able to get this to work, I find it gets to convoluted when I get to checking the second column of values to try and break the ties, I have all values stored in arrays. As you can see below the first "77" tie is broken by the next column "36" but the next two 77 ties are not broken until the last possible check.

77 36 24 12 41 27 13 5
77 37 23 11 40 27 13 4
77 37 23 11 40 27 13 5

I've been trying to resolve this for a couple of weeks, sad to say I haven't, but the good news is I'm learning a lot about Excel VBA. If someone can point me in the right direction it would be appreciated.

Thanks

 

[Peter_SSs]

I think I understand what is required now.

Actually, I'm not sure

Blue I'm pretty sure about, but should the red be included too and if so should each red item be less 1/18 hcp?


<del>TotalScoreCol</del> NetScoreCol (Same as Total - hcp)
BckTotalCols (In) - less 1/2 hcp
BckLastSixCols - less 1/3 hcp
BckLastThreeCols - less 1/6 hcp
BckLastOneCol
FrontTotalCols (Out) - less 1/2 hcp
FrontLastSixCols - less 1/3 hcp
FrontLastThreeCols - less 1/6 hcp
FrontLastOneCol
Individual hole cols in reverse

 

[Chesley]

OK, thanks Peter.
Name HDCP Skins 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 Out In Gross Net
Henry DDDDD 11 Y 3 6 3 6 4 5 3 3 5 4 5 5 6 5 7 4 3 4 38 43 81 70
Dave EEEE 11 Y 4 7 6 5 4 4 5 5 6 6 6 3 3 6 5 5 4 5 46 43 89 78
Gary AAAA 12 Y 6 5 6 4 4 4 5 5 4 4 6 5 4 4 4 5 4 6 43 42 85 73
Bill BBBB 12 Y 4 4 6 4 5 7 6 7 6 5 4 5 5 6 6 4 4 6 49 45 94 82
George CCCC 13 Y 3 5 5 4 3 5 5 6 5 6 3 5 5 5 5 3 4 6 41 42 83 70

And if I did the calculations right: the net values would be,
Net IN Last6 last 3 Out FrontLast6 FrontLast3
70 37.5 25.33 9.17 32.5 22.33 9.17
78 37.5 24.33 12.17 40.5 25.33 14.17
73 36 23.00 13.00 37 22.00 12.00
82 39 27.00 12.00 43 31.00 17.00
70 35.5 23.67 10.83 34.5 23.67 13.83

 

[Chesley]

Peter, I've been looking online trying to find a definitive answer with no success, everything I found had a statement similar to what I provided. Soooooo, I would say Yes to include the RED items, only because in each article I found it stated Net is to be resolved in the same manner that Gross was decided. So I would say use the 1/18 on those items.

 

[Peter_SSs]

Let's try this then. You will see that I have made TieBreakColsArr into a 2-dimensional array.
The first item in each pair holds similar info to the previous macro. The second item in each pair is the fraction of the handicap that must be deducted for that particular part of the tie-breaking regime.

Sub Net_Pos()
  Dim a, b, Pos, RowList, hcpFrac
  Dim TieBreakColsArr(1 To 27, 1 To 2)
  Dim i As Long, j As Long, LR As Long, rws As Long, Inc As Long
  Dim s As String
  Dim SL As Object
  
  TieBreakColsArr(1, 1) = 24                      'NetScoreCol
  TieBreakColsArr(1, 2) = 0                         'Handicap fraction to subtract
  
  TieBreakColsArr(2, 1) = 22                      'BckTotalCols (In)
  TieBreakColsArr(2, 2) = 1 / 2                     'Handicap fraction to subtract
  
  TieBreakColsArr(3, 1) = Evaluate("row(15:20)")  'BckLastSixCols
  TieBreakColsArr(3, 2) = 1 / 3                     'Handicap fraction to subtract
  
  TieBreakColsArr(4, 1) = Evaluate("row(18:20)")  'BckLastThreeCols
  TieBreakColsArr(4, 2) = 1 / 6                     'Handicap fraction to subtract
  
  TieBreakColsArr(5, 1) = 20                      'BckLastOneCol
  TieBreakColsArr(5, 2) = 1 / 18                    'Handicap fraction to subtract
  
  TieBreakColsArr(6, 1) = 21                      'FrontTotalCols (Out)
  TieBreakColsArr(6, 2) = 1 / 2                     'Handicap fraction to subtract
  
  TieBreakColsArr(7, 1) = Evaluate("row(6:11)")   'FrontLastSixCols
  TieBreakColsArr(7, 2) = 1 / 3                     'Handicap fraction to subtract
  
  TieBreakColsArr(8, 1) = Evaluate("row(9:11)")   'FrontLastThreeCols
  TieBreakColsArr(8, 2) = 1 / 6                     'Handicap fraction to subtract
  
  TieBreakColsArr(9, 1) = 11                      'FrontLastOneCol
  TieBreakColsArr(9, 2) = 1 / 18                    'Handicap fraction to subtract
  
  For i = 18 To 1 Step -1
    TieBreakColsArr(28 - i, 1) = i + 2            'Individual hole cols in reverse
    TieBreakColsArr(28 - i, 2) = 1 / 18             'Handicap fraction to subtract
  Next i
  
  LR = Range("F" & Rows.Count).End(xlUp).Row
  a = Range("D2:AA" & LR).Value
  rws = UBound(a)
  ReDim b(1 To rws, 1 To 27)
  ReDim Pos(1 To rws, 1 To 1)
  Set SL = CreateObject("System.Collections.Sortedlist")
  
  For i = 1 To rws
    s = vbNullString
    For j = 1 To 27
      b(i, j) = Format(Application.Sum(Application.Index(a, i, TieBreakColsArr(j, 1))) - TieBreakColsArr(j, 2) * a(i, 1), "000.000")
      s = s & "|" & b(i, j)
    Next j
    If SL.ContainsKey(s) Then
      SL(s) = SL(s) & " " & i
    Else
      SL.Add s, i
    End If
  Next i
  For i = 1 To SL.Count
    RowList = Split(SL.getByIndex(i - 1))
    For j = 0 To UBound(RowList)
      Pos(RowList(j), 1) = i + Inc & IIf(UBound(RowList) > 0, " (Play-off)", "")
    Next j
    If UBound(RowList) > 0 Then Inc = Inc + 1
  Next i
  Range("AB2").Resize(rws).Value = Pos
  Range("AB1").Value = "Net Pos"
End Sub

For the sample data you provided in post #32, results are as follows.

Excel Workbook
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
1	HDCP	Skins	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	Out	In	Gross	Net	Net Pos
2	11	Y	3	6	3	6	4	5	3	3	5	4	5	5	6	5	7	4	3	4	38	43	81	70	2
3	11	Y	4	7	6	5	4	4	5	5	6	6	6	3	3	6	5	5	4	5	46	43	89	78	4
4	12	Y	6	5	6	4	4	4	5	5	4	4	6	5	4	4	4	5	4	6	43	42	85	73	3
5	12	Y	4	4	6	4	5	7	6	7	6	5	4	5	5	6	6	4	4	6	49	45	94	82	5
6	13	Y	3	5	5	4	3	5	5	6	5	6	3	5	5	5	5	3	4	6	41	42	83	70	1
Net Pos 1

I think your calculations of the various net calculations you did are correct, though I only checked the first row, using the string below, produced by this new code for the first player.
Since we now have fractions involved, each (net) score is formatted to 3 decimal places "000.000"

|070.000|037.500|025.333|009.167|003.389|032.500|022.333|009.167|004.389|003.389|002.389|003.389|006.389|004.389
|005.389|004.389|004.389|003.389|004.389|002.389|002.389|004.389|003.389|005.389|002.389|005.389|002.389

This represents the net score for each of the 27 tie-break rules we have, with the blue ones being the ones that you had shown calculations for.

 

[Peter_SSs]

Too much to include in the last post, so here are the results for the previous sample data, though I think I have altered a few of the numbers.
This does include some ties but in practice that would now be extremely rare as the only way to produce that now is to have players with identical handicaps have identical scores on all 18 holes.

Excel Workbook
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
1	HDCP	Skins	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	Out	In	Total	Net	Net Pos
2	3	Y	6	3	4	5	3	4	5	4	4	4	4	4	4	5	4	4	4	4	38	37	75	72	4
3	4	Y	4	6	4	5	5	5	4	4	4	6	5	4	3	4	3	3	3	5	41	36	77	73	6 (Play-off)
4	5	Y	5	6	4	5	5	5	4	4	4	6	5	4	3	4	3	3	3	5	42	36	78	73	5
5	4	Y	4	6	4	5	5	5	4	4	4	6	5	4	3	4	3	3	3	5	41	36	77	73	6 (Play-off)
6	5	Y	5	5	4	5	4	5	4	4	5	4	4	4	3	5	4	4	3	5	41	36	77	72	3
7	6	Y	2	3	3	3	5	5	3	6	5	5	4	5	3	4	4	4	3	10	35	42	77	71	2
8	7	N	4	5	5	5	3	5	4	4	5	6	4	6	3	3	4	4	3	5	40	38	78	71	1
9	8	Y	6	5	2	6	5	6	5	4	5	5	3	3	5	5	4	4	6	4	44	39	83	75	8
10	8	N	4	7	4	4	5	5	5	4	5	5	5	5	3	4	5	5	4	5	43	41	84	76	9
11	6	N	4	6	5	4	5	5	5	4	5	5	5	5	3	4	5	5	4	5	43	41	84	78	12
12	7	N	6	5	4	4	3	6	5	6	3	6	7	4	3	5	5	3	3	6	42	42	84	77	10
13	9	N	6	5	3	5	5	5	5	4	5	4	3	4	7	4	4	5	7	5	43	43	86	77	11
14	6	N	6	7	5	6	4	6	3	5	4	5	6	5	4	5	5	4	2	5	46	41	87	81	13 (Play-off)
15	6	N	6	7	5	6	4	6	3	5	4	5	6	5	4	5	5	4	2	5	46	41	87	81	13 (Play-off)
Net Pos 2

 

[Chesley]

Peter, I just entered your code , all is GREAT, thank you very much for resolving this, and for the explanations on how it works.

Thanks
Ches

 

[Peter_SSs]

Peter, I just entered your code , all is GREAT, thank you very much for resolving this, and for the explanations on how it works.

Thanks
Ches

You are very welcome. Happy golfing, and I hope you have a "1" somewhere in your row of scores!