Forum Discussion
Create a Rank based on the Frame Played and Won
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.
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