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.
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.
