RE: retrieving top/bottom n rank()-ed data
- From: rockhammer <rockhammer@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Wed, 13 Jun 2007 18:36:01 -0700
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.
"Ron Coderre" wrote:
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
"rockhammer" wrote:
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.
- Follow-Ups:
- RE: retrieving top/bottom n rank()-ed data
- From: Ron Coderre
- RE: retrieving top/bottom n rank()-ed data
- Prev by Date: RE: ActiveCell.Value copies unwanted currency format of source cel
- Next by Date: RE: ActiveCell.Value copies unwanted currency format of source cel
- Previous by thread: RE: ActiveCell.Value copies unwanted currency format of source cel
- Next by thread: RE: retrieving top/bottom n rank()-ed data
- Index(es):
Relevant Pages
|
Loading
