Forum Discussion
Solved
Create a Rank based on the Frame Played and Won
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 an...
The following assumes that you always sort the results descending on % and Frames Won, as in your sample workbook.
In E2, enter 1 (the top entry will always have rank 1 because of the sort order).
In E3, enter the formula =E2+OR(D3<>D2,B3<>B2)
This adds 1 to the rank if either % or Frames Won changes.
Fill or copy down to the last row with data.
thank you its working perfect. Sir one more favor please if i want to do it via VBA can you please do this
For example:
Sub SortAndRank()
Dim LastRow As Long
' Speed up execution
Application.ScreenUpdating = False
' Find the last row
LastRow = Range("A" & Rows.Count).End(xlUp).Row
' Sort descending on % and Framse Played
Range("A1:E" & LastRow).Sort _
Key1:=Range("D1"), Order1:=xlDescending, _
Key2:=Range("B1"), Order2:=xlDescending, _
Header:=xlYes
With Range("E2:E" & LastRow)
' Add formulas
.Formula = "=SUM(E1,OR(D2<>D1,B2<>B1))"
' Optional: replace formulas with their result
.Value = .Value
End With
' Show the result
Application.ScreenUpdating = True
End Sub- Sir thank you very much.
Sir now i am looking for this Purple highlighted result how to achieve this, sorry to bother you again
In E3:
=E2+IF(AND(B3=B2,D3=D2),0,COUNTIFS(B$2:B2,B2,D$2:D2,D2))Fill down.
This can also be used in the macro:
.Formula = "=E2+IF(AND(B3=B2,D3=D2),0,COUNTIFS(B$2:B2,B2,D$2:D2,D2))"