Create a Rank based on the Frame Played and Won-CodePudding

I have been trying to create a Rank based on the Frame played and Won. All i got is the Rank formula from the Google.

=RANK(D2,$D$2:$D$18)

But i want a fair Ranks if player has played 1 frame and won 1 then its ranks will be next from the player who played 3 frames and won 3.

I have attached a picture with Ranks Result and The Result I want.

Your help will be appreciated.

[ enter image description here

CodePudding user response：

Please, try the next code. In order to work well, the range should be sorted descending, by column "D:D" (like in your picture sample) and the percentages should be calculated without decimals (using Round). As it looks in your picture (but not by Decrease decimal format...):

Sub specRank()
   Dim sh As Worksheet, lastR As Long, rngD As Range, rngB As Range, arr, arrF, arrFin, El, k As Long
   
   Set sh = ActiveSheet 'use there the necessary sheet
   lastR = sh.Range("B" & sh.rows.count).End(xlUp).row
   Set rngD = sh.Range("D2:D" & lastR): Set rngB = sh.Range("B2:B" & lastR)
   
   arr = Evaluate(rngD.Address & "&" & rngB.Address) 'put in an array the concatenation between columns D:D and B:B
   arrF = fUniques(Application.Transpose(arr)) 'obtain an array of unique such concatenated strings
   ReDim arrFin(1 To UBound(arr)): k = 1       'reDim the final array to keep the rank as (I understood) you need
    For Each El In arr
        arrFin(k) = Application.match(El, arrF, 0): k = k   1
    Next
    sh.Range("E2").Resize(UBound(arrFin), 1).Value = Application.Transpose(arrFin)
End Sub

Function fUniques(arr As Variant) As Variant 'it works on 1D arrays
  Dim uniques
  With Application
      uniques = .Index(arr, 1, Filter(.IfError(.match(.Transpose(.Evaluate("ROW(1:" & _
                  UBound(.match(arr, arr, 0)) & ")")), .match(arr, arr, 0), 0), "|"), "|", False))
  End With
   fUniques = uniques
End Function