Consulting

Results 1 to 3 of 3

Thread: Ranking with conditions

  1. #1
    VBAX Regular
    Joined
    Oct 2018
    Posts
    43
    Location

    Ranking with conditions

    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
    Last edited by Edmond; 07-08-2019 at 02:29 PM.
    Edmond

  2. #2
    VBAX Mentor
    Joined
    Dec 2008
    Posts
    358
    Location
    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
    Last edited by Artik; 07-08-2019 at 09:45 PM.

  3. #3
    VBAX Regular
    Joined
    Oct 2018
    Posts
    43
    Location
    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!
    Edmond

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •