How to find the top5 and bottom5 values without using large or small

[waimea]

Hi,

I am trying to find the top5 and bottom5 values using LARGE(array, nth position) and SMALL(array, nth position), then I am using INDEX & MATCH to get the corresponding values.

However, I am also trying to loop through a range of formulas and the nth position in Large and Small requires one loop each to get the top1,2,3,4,5 values and by not using LARGE and SMALL the code becomes easier to loop.

Is there any good alternative to find the top5 or top nth that isn't LARGE or SMALL?

[Stohan]

If I got your task right, you need sort of the information: top1 - index, top2 - index ...

if you want to solve it with macros, you should look at the sorting (but probably will require to add index column and sort within 2 columns) Not sure if it will easier

Another way it is just sort the columns after adding index column if there is repetitive values could be. (if the table have the possibilities to do it)

This one is useful for you I think:
https://www.youtube.com/watch?v=1_v-uqoyXqI

[Paul_Hossler]

As a concept ...

You could use LARGE(rng,5) and SMALL(rng,5) to find the 5th and then just see which entries are larger / smaller

Capture.JPG

Capture2.JPG

[waimea]

Hi, thank you for your replies.

The current code I have is:

Dim J As Integer
For J = 1 To 5 Step 1
ws.Cells(1 + J, 14).Formula = "=LARGE($K$3:$K$41," & J & ")"
ws.Cells(i + J, 14).NumberFormat = "0.00"
Next J

I have read that I can use:

first = Application.WorksheetFunction.Large(rng,1)
second = Application.WorksheetFunction.Large(rng,2)        
third = Application.WorksheetFunction.Large(rng,3)
fourth = Application.WorksheetFunction.Large(rng,4)
fifth = Application.WorksheetFunction.Large(rng,5)

Something like:

Dim i As Integer
For i = 1 to 5

ws.cells(1 + j, 14).Formula = "Application.WorksheetFunction.Large(rng, i)"

Next i

I haven't tried the last code and I don't know if I will work?

[Paul_Hossler]

I haven't tried the last code and I don't know if I will work?

No



I thought you wanted to avoid loops

Option Explicit

Sub test()

    Dim i As Long
    Dim r As Range
    Set r = ActiveSheet.Range("A1:A25")
    
    For i = 1 To 5
        Range("C" & i).Value = Application.Evaluate("LARGE(" & r.Address & "," & i & ")")
    Next i
End Sub

or

Sub test2()

    Dim A(1 To 5) As Long
    Dim r As Range
    Set r = ActiveSheet.Range("A1:A25")
    
    A(1) = Application.Evaluate("LARGE(" & r.Address & ",1)")
    A(2) = Application.Evaluate("LARGE(" & r.Address & ",2)")
    A(3) = Application.Evaluate("LARGE(" & r.Address & ",3)")
    A(4) = Application.Evaluate("LARGE(" & r.Address & ",4)")
    A(5) = Application.Evaluate("LARGE(" & r.Address & ",5)")

    Debug.Print A(1)
    Debug.Print A(2)
    Debug.Print A(3)
    Debug.Print A(4)
    Debug.Print A(5)

End Sub

[waimea]

Hi Paul,

thank you for your reply and you are correct, I want to avoid the extra loop for incrementing nth in the LARGE(array, nth) from #1 to #5.

I am going to try both of your suggestions right away!

[Paul_Hossler]

I doubt there'd be any perceptible performance penalty just looping 5 (or 500) times

If you wanted to simplify the code, you could put the Top/Bottom 5 logic in functions

Option Explicit
Sub test()
    Dim T As Variant, B As Variant
    Dim i As Long
    
    T = Top(Range("A1:A50"))
    B = Bottom(Range("A1:A50"))
    For i = 1 To 5
        Debug.Print T(i)
    Next i
    For i = 1 To 5
        Debug.Print B(i)
    Next i

End Sub



Function Top(R As Range, Optional N As Long = 5) As Variant
    Dim i As Long
    Dim A() As Long
    
    ReDim A(1 To N)
    
    For i = 1 To N
        A(i) = Application.Evaluate("LARGE(" & R.Address & "," & i & ")")
    Next i
    Top = A
End Function


Function Bottom(R As Range, Optional N As Long = 5) As Variant
    Dim i As Long
    Dim A() As Long
    
    ReDim A(1 To N)
    
    For i = 1 To N
        A(i) = Application.Evaluate("SMALL(" & R.Address & "," & i & ")")
    Next i
    Bottom = A
End Function

[waimea]

Hi again, you are truly a VBA wizard and the functions you created are really clever!

Thanks for the code!