FINDING TOP 20 (URGENT PLEASE)

[FARAZ QURESHI]

Please help me in the following example:

I have a list of 100 figures in B1:B100. I want to insert a formula in
A1:A100 which results in showing the position of each number i.e. from 1 to
100 in the order of volume of corresponding figure in Column A.

In the end I insert:
1 to 20 (in C1:C20) and
=vlookup(C1,$A$1:$B$100,2,0)

In the end result in D120 is the list of top 20 figures from the list
which updates on even a slight change in B1:B100.

Thanx

 

[FARAZ QURESHI]

Rank() function does help but how to insert multiple arrays in such a function?
For instance 1:100 have a gap in between?

 

[FARAZ QURESHI]

I think I am still unable to clarify of multiple ranges. PLEASE consider the
following example:

A B C
1 PLAYER GROUP AMOUNTS
2 A I 1000
3 B I 90000
4 I Total 91000
5 C II 82000
6 D II 81000
7 E II 25000
8 F II 71000
9 II Total 259000
10 G III 47000
11 III Total 47000
12 H IV 83000
13 I IV 30000
14 J IV 99000
15 K IV 57000
16 L IV 2000
17 M IV 83000
18 N IV 76000
19 O IV 62000
20 IV Total 492000
21 P V 52000
22 Q V 29000
23 V Total 81000
24 R VI 8000
25 S VI 62000
26 T VI 94000
27 VI Total 164000
28 Grand Total 1134000

Here, please note that I want the ranking only in column D where there is a
SUBTOTAL in the respective cell in column C.

Please help urgently!

 

[Pete_UK]

Put this in cell D2 and copy down to row 27:

=IF(ISNUMBER(SEARCH("total",B2)),RANK(C2,C$2:C$27),"")

It will show the ranks in the subtotal rows, but it will be across the
whole range of numbers. If you want the true rankings, you can put
this formula in E2 and copy down to E27:

=IF(D2="","",RANK(D2,D$2$27))

This will give you ranks 1 to 6, and you can hide column D if you only
want to see this result.

Hope this helps.

Pete

 

[FARAZ QURESHI]

Thanx Pete!

Sure was a great solution! However any idea how to combine the two formula
in a single one so as to avoid an additional column?

 

[Pete_UK]

I can't think of one, because first of all you have to pick out only
the numbers that represent the subtotals and then apply the RANK to
them (RANK ignores blanks). You could have a different formula in D2:

=IF(ISNUMBER(SEARCH("total",B2)),C2,"")

and then copy this down, with the same formula in column E. This will
give you the numbers in column D and the ranks in column E, as before.

Hope this helps.

Pete