Hi,
I have a macro where i need to identify the 5 largest values in a specific column of data. Note: Lets say there are 30 columns of data in this file.
I need to copy and paste the rows which correspond to these 5 largest values into another worksheet. I am not sure the best way to do this. I was thinking of using "FIlters" but i am not sure how yet.
Any thoughts on how to approach this?
this will give you the row numbers you will need to use to copy the rows
this will give you an idea how to approach things
Please Login or Register to view this content.
Last edited by nigelog; 01-22-2020 at 01:30 PM.
that code works!!!!!!!! but i am not sure why it works. can you explain it just a little.
ok, i think i understand now...i had to look up the worksheet function "Large" to see how it worked. I had never heard of this worksheet function before.
one more question....i see how the "filter" and "Large" things work....and i am playing with them still just to see if there are any issues.
But i did have a similar question about if this type of thing could be done with values that are stored in a dictionary.
You could also use an autofilter for values >= the 5th largest value.
Rory
Any idea on how to do this is you could not use the worksheetfunction.large. For example, if you had an array of values how you would determine the top 5 values? And their corresponding row numbers?
So if we use the "Large" method...we need to know the range. But what is the range when we already have filters on for say country....see enclosed file.
We need to know top 5 days and their corresponding rows...........for say country USA.......how to use this then.
Hi ! Try this ‼
Originally Posted by welchs101
As always an easy way is the Excel basics, here LARGE & MATCH worksheet functions,
even directly on a 'formula filtered' values array - so without the need of a worksheet filter - like according to your attachment
this VBA demonstration where V is the Values array, T the Top five result array & R the top five Rows # :
PHP Code:Sub Demo1()
Dim V, T, R
With [A1].CurrentRegion.Columns
V = Evaluate("IF(" & .Item(1).Address & "=""United States""," & .Item(2).Address & ",-1)")
End With
T = Application.Large(V, [ROW(1:5)])
R = Application.Match(T, V, 0)
Stop
End Sub
Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !
Last edited by Marc L; 01-24-2020 at 09:28 PM. Reason: simplification …
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks