PDA

View Full Version : [SOLVED] How to find the top5 and bottom5 values without using large or small



waimea
12-17-2018, 02:52 PM
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
12-17-2018, 03:53 PM
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
12-17-2018, 09:03 PM
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

23430

23431

waimea
12-18-2018, 02:57 AM
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
12-18-2018, 07:13 AM
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
12-18-2018, 07:23 AM
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
12-18-2018, 07:47 AM
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
12-18-2018, 07:54 AM
Hi again, you are truly a VBA wizard and the functions you created are really clever!

Thanks for the code!