For dynamic sorting / ranking, is there a speed difference b/t the RANK and COUNTIFS methods? (And are there other faster methods?)

[d0rian]

I have a large sheet that relies on a lot of live ranking. Is there a speed / efficiency difference between the two formulas below? (Obviously trivial for the tiny data set I used to create the image, but my sheet pulls in live stock data and is live-sorting 1,000+ cells that update nearly every second, so I need to optimize efficiency.)

 

[d0rian]

I realized I should clarify one thing; this all started with my search for a RANKIF function (which doesn't exist, so I had to cobble something together). My real data set looks like the image below. Columns A has names, B has values -- I want to return the rank of col B values among ONLY other col-B values that share the same col-A values i.e. I want to rank Fred's values separately from Mike's and Dave's. It didn't seem like I could get there with the RANK function unless I used a bunch of helper columns (D:I in the image), all to produce the ranking I want in column-J.

I read somewhere about using the COUNTIFS function as you see I did in col-C. It does everything I want in a single column, BUT my sheet performance is dragging terribly. So I wanted to know whether there is a material speed difference between the RANK and COUNTIFS methods you see (illustrated in the formulas in row 2), and if there is, maybe I'd go back to the RANK method, despite all the ugly helper columns.

If I'm ignoring some even better / faster method to achieve what I want, I'm all ears.

 

[DanteAmor]

You can use the following macro, this way you will not have formulas and your sheet will be faster.
After making changes to your sheet, execute the macro and it will set the rank.

Sub rank()
    Dim datos As Range, i As Long, n As Long
    
    Set datos = Range("A3", Range("A" & Rows.Count).End(xlUp))
    For i = 3 To datos.Rows.Count
        n = WorksheetFunction.CountIf(datos.Columns(1), datos(i, 1))
        With datos(i - 2, 1).Resize(n, 1).Offset(, 2)
            .Formula = "=RANK(B" & i & ",$B$" & i & ":$B$" & i + n - 1 & ",1)"
            .Value = .Value
        End With
        i = i + n - 1
    Next
End Sub

 

[d0rian]

Thanks for the reply, but something I perhaps didn't make clear enough is that what I'm doing is live-ranking stock prices that I'm pulling into Excel via my brokerage's API...so I need formulas because the stock prices are updating in real-time, so I need their rank order to update in real-time as well...whereas VBA will only be useful if I were doing a one-off rank/ordering, right?

 

[DanteAmor]

Thanks for the reply, but something I perhaps didn't make clear enough is that what I'm doing is live-ranking stock prices that I'm pulling into Excel via my brokerage's API...so I need formulas because the stock prices are updating in real-time, so I need their rank order to update in real-time as well...whereas VBA will only be useful if I were doing a one-off rank/ordering, right?

We can put the sheet in an automatic event, so every time you update the sheet, the macro will also run.

Put the following formula in cell, for example, B"
=TODAY()


Put the following code in the events of your sheet

Private Sub Worksheet_Calculate()
    Dim datos As Range, i As Long, n As Long
    
    On Error GoTo AppEna
    Application.EnableEvents = False
    Set datos = Range("A3", Range("A" & Rows.Count).End(xlUp))
    For i = 3 To datos.Rows.Count
        n = WorksheetFunction.CountIf(datos.Columns(1), datos(i, 1))
        With datos(i - 2, 1).Resize(n, 1).Offset(, 2)
            .Formula = "=RANK(B" & i & ",$B$" & i & ":$B$" & i + n - 1 & ",1)"
            .Value = .Value
        End With
        i = i + n - 1
    Next
AppEna:
    Application.EnableEvents = False


End Sub

SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.

 

[d0rian]

We can put the sheet in an automatic event, so every time you update the sheet, the macro will also run.

Put the following formula in cell, for example, B"
=TODAY()


Put the following code in the events of your sheet

Private Sub Worksheet_Calculate()
    Dim datos As Range, i As Long, n As Long
    
    On Error GoTo AppEna
    Application.EnableEvents = False
    Set datos = Range("A3", Range("A" & Rows.Count).End(xlUp))
    For i = 3 To datos.Rows.Count
        n = WorksheetFunction.CountIf(datos.Columns(1), datos(i, 1))
        With datos(i - 2, 1).Resize(n, 1).Offset(, 2)
            .Formula = "=RANK(B" & i & ",$B$" & i & ":$B$" & i + n - 1 & ",1)"
            .Value = .Value
        End With
        i = i + n - 1
    Next
AppEna:
    Application.EnableEvents = False


End Sub

SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.

Just to clarify, what qualifies as tripping the "so every time you update the sheet" trigger? The tick-by-tick stock prices are streaming in real-time via my brokerage's API and they update every second (might even be every half-second)...is every single one of those updates going to trigger this code to run?

 

[DanteAmor]

Just to clarify, what qualifies as tripping the "so every time you update the sheet" trigger? The tick-by-tick stock prices are streaming in real-time via my brokerage's API and they update every second (might even be every half-second)...is every single one of those updates going to trigger this code to run?

just try and you will see it. Much of the code in Excel is tested and the result is reviewed to see if it is desired.