In this case, I think I'd implement an AutoFilter or, depending on your
circumstances, an Advance Filter.
With A1:B22 containing:
Age Name
10 A
12 B
14 C
16 D
18 E
20 F
22 G
24 H
26 I
28 J
30 K
32 L
34 M
36 N
38 O
40 P
42 Q
44 R
46 S
48 T
50 U
This code, in a General Module, filters the source range to only display the
Top 5 values in Col_A (which may include more than 5 members if there are
duplicates). The filtered cells are then copied and pasted at cell D1
'------------Start of Code---------------
Option Explicit
Sub TryThis()
Dim iLgTarget As Integer
Dim dblTargetVal As Double
Dim rSource As Range
Dim rDest As Range
iLgTarget = 5
dblTargetVal = WorksheetFunction.Large([A2:A22], iLgTarget)
Set rSource = Range("A1:B22")
Set rDest = Range("D1")
With rSource
'Engage the autofilter
.AutoFilter Field:=1, Criteria1:=">=" & dblTargetVal
'Select the visible cells, copy them to the destination cell
.SpecialCells(Type:=xlCellTypeVisible).Copy Destination:=rDest
'Remove the autofilter
.AutoFilter
End With
End Sub
'------------End of Code---------------
The end result in cells D1:E6 is
Age Name
42 Q
44 R
46 S
48 T
50 U
Is that something you can work with?
***********
Regards,
Ron
XL2002, WinXP
rockhammer said:
Thanks for the quick reply, Ron. Well, sorry, perhaps I should have
clarified... my table actually contains more data than just those two, and I
need to pull out data from all columns for the top n and bottom n entries.
To illustrate, suppose column A stores the age of individuals and some
individuals happen to have the same age. Column B stores the ranking of the
age values. Then I also have column C storing name, D storing address, etc.,
etc.
I need to pull, say, the top n=10 individuals by age including their name,
address, etc., and also, say, the bottom m=10 individuals in the same way.
It's just that with =large() I can get the values but cannot use those values
to lookup the names, addresses, etc. because age is not unique. This is
essentially the same problem as using =rank().
The only way I can think of to achieve this is to do something like:
counter = 0
for i = 1 to n ' n as defined above
x =
application.countif(range(cells(row1st,LookupCol),cells(rowLast,LookupCol)),i)
if isnumeric(x) then
counter = counter + x
rowStart = row1st
for j = 1 to x
r = application.match(i,range(cells(rowStart,LookupCol), _
cells(rowLast,LookupCol)),0)
'
' then use r to lookup/copy everything I need
'
rowStart = r + 1
next j
end if
next i
' I recognize the above could potentially give me counter > n;
' I'll just need to tighten up the exit criteria
' for bottom m, i'll just have adjust the for/next variables
I'm just wondering if there is a simpler, more elegant way than the above to
achieve the same result.
Thanks.
:
Skip the RANK function and try this, instead:
With
A1:100
A2: 75
A3:100
A4: 75
A5: 50
And
C1: 1
C2: 2
C3: 3
C4: 4
C5: 5
Then
D1: =LARGE($A$1:$A$5,C1)
Copy D1 down through D5
In this example, the Col_D formula return
100
100
75
75
50
Does that help?
***********
Regards,
Ron
XL2002, WinXP
:
I have a table in which one column (say A) contains unsorted numeric values
and another column (say B) contains the values returned by =rank() based on
those numeric values.
In cases where those numeric values in col A are not unique, the ranks in
column B will contain duplicates as well.
I want to find a way to extract the top ranking n numeric values and the
bottom ranking m numeric values WITHOUT HAVING TO SORT THE TABLE.
Is there an (easy) way to do that?
Thanks.
