Ranking with conditions

[Edmond]

Hello All,

My end goal would be to find the 3 (or more) biggest values within a range while applying conditions.

I am using the worksheetFunction.Large currently but of course without filters.

I thought about creating an UDF with a loop but as my set of data is quite massive I am afraid it takes a long time to process.

Did some of you face that issue or would know a way to solve it?

Thanks a lot!!

This is what I have done:
(it works but just wanted to know if other solutions exist)

With wsRD        
        LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
        LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
        Set rg = Range(.Cells(1, 1), .Cells(LastRow, LastCol + 3))
        
        For i = 1 To LastRow
            If .Cells(1 + i, 8) = "70" Then
                If .Cells(1 + i, 43) = "NewLate" Then
                    If .Cells(i + 1, 24).Value > lRank Then
                        lRank = .Cells(i + 1, 24).Value
                    End If
                End If
            End If
        Next
End With 
        MsgBox lRank

[Artik]

I have not wondered how to find some of the greatest values. I am referring only to the presented code.
Executing the code will accelerate significantly if you search the array, not the worksheet cells.
If you can predict which condition is less frequently satisfied (If varArr (i, 1) = 70 Then or If varArr (i, 36) = "NewLate" Then), then it should be the first one.


BTW. Instead of Cells (1 + i, ...) it was enough to start the loop from 2

    Dim wsRD        As Worksheet
    Dim LastRow     As Long
    Dim rg          As Range
    Dim i           As Long
    Dim lRank       As Double 'or other type
    Dim varArr      As Variant
    
    Set wsRD = ...
    
    With wsRD
        LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
        Set rg = .Range(.Cells(2, 8), .Cells(LastRow, 43))
    End With


    varArr = rg.Value


    For i = 1 To UBound(varArr)


        If varArr(i, 1) = 70 Then    '8-7=1 The 8-th column in the worksheet is the 1-st column in Array
            If varArr(i, 36) = "NewLate" Then    '43-7=36
                If varArr(i, 17) > lRank Then    '24-7=17
                    lRank = varArr(i, 17)
                End If
            End If
        End If


    Next i


    MsgBox lRank

..::Edit
OK, I was thinking about finding a few maximum values considering the conditions. Something very similar to the previous code.

    Dim wsRD        As Worksheet
    Dim LastRow     As Long
    Dim rg          As Range
    Dim i           As Long
    Dim varArr      As Variant
    Dim varArrToRank As Variant
    
    Set wsRD = ...
    
    With wsRD
        LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
        Set rg = .Range(.Cells(2, 8), .Cells(LastRow, 43))
    End With


    varArr = rg.Value
    
    ReDim varArrToRank(1 To UBound(varArr))


    For i = 1 To UBound(varArr)


        If varArr(i, 1) = 70 Then
            If varArr(i, 36) = "NewLate" Then
                If varArr(i, 17) > lRank Then
                    varArrToRank(i) = varArr(i, 17)
                End If
            End If
        End If


    Next i


    MsgBox "1-st Max = " & Application.Large(varArrToRank, 1) & vbLf & _
           "2-nd Max = " & Application.Large(varArrToRank, 2) & vbLf & _
           "3-th Max = " & Application.Large(varArrToRank, 3)

::..


Artik

[Edmond]

Hi Artik,

Many thanks for having taken the time to answer.

I didn't know the search by array was faster, really interesting. Would you know why?

I have also found that page which compares Match/Find/Array. (link)

Anyway thanks again!