PDA

View Full Version : Ranking with conditions



Edmond
07-08-2019, 02:18 PM
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
07-08-2019, 09:22 PM
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
07-09-2019, 05:51 AM
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 (https://fastexcel.wordpress.com/2011/10/26/match-vs-find-vs-variant-array-vba-performance-shootout/))

Anyway thanks again!