retrieving top/bottom n rank()-ed data
 

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.

retrieving top/bottom n rank()-ed data
 

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.

retrieving top/bottom n rank()-ed data
 

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.

retrieving top/bottom n rank()-ed data
 

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" wrote:

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.

retrieving top/bottom n rank()-ed data
 

Hi Ron, thanks a lot for your efforts. Yes, this certainly will work. I've
had only limited exposure to autofilters so far and certainly not via vb
code. I'm glad to learn something new. Thanks a lot.

"Ron Coderre" wrote:

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" wrote:

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.

retrieving top/bottom n rank()-ed data
 

I'm glad you can work with that.....Thanks for letting me know.

(Post back with any more questions.)

***********
Regards,
Ron

XL2002, WinXP


"rockhammer" wrote:

Hi Ron, thanks a lot for your efforts. Yes, this certainly will work. I've
had only limited exposure to autofilters so far and certainly not via vb
code. I'm glad to learn something new. Thanks a lot.

"Ron Coderre" wrote:

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" wrote:

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.