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!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.