Option Explicit
Sub golf()
Dim i&, j&, t&, rng, sum&, dic As Object, index, score, myScore&, res(), id As String
Set dic = CreateObject("Scripting.Dictionary")
index = Range("C4:T4").Value
score = Range("B7:V10").Value
For i = 1 To UBound(score)
For j = 1 To UBound(score)
id = score(i, 1) & "|" & score(j, 1)
If Not dic.exists(id) Then
dic.Add id, Round((score(j, 21) - score(i, 21)) * 0.7, 0)
End If
Next
Next
ReDim res(1 To UBound(score), 1 To 18)
For j = 2 To 19
For i = 1 To UBound(score)
myScore = score(i, j): sum = 0
For t = 1 To UBound(score)
If i <> t Then
If score(t, j) < myScore Then
sum = sum + 1
ElseIf score(t, j) > myScore Then
sum = sum - 1
Else
id = score(i, 1) & "|" & score(t, 1)
If index(1, j - 1) <= Abs(dic(id)) Then sum = sum + IIf(dic(id) < 0, 1, -1)
End If
End If
Next
res(i, j - 1) = sum
Next
Next
Range("C12").Resize(UBound(res), 18).Value = res
End Sub