macro for top10 & worst10 ranking

[Mayte]

Is there a way do a macro to ...extract the top10 and worst10 people from a
ranking?

I have a worksheet with 200+ employees, they all have 5 measures and based
on their scores they are assigned points and when points are added, a ranking
is made.

I have my formulas for the points and ranking done & I can identify the
top10 and worst10 in column G using the SMALL and LARGE formulas but ...I
need to somehow extract/copy the whole rows for the top10 and worst 10 and
paste them into a new sheet?? can that be done?

it looks like this but will 200+ rows

A B C D E F G
1 Name Score1 Score2 Score3 Score4 Score5 RANK
2 mika 85% 90% 30% 2% 55% 5
3 tetsu 94% 92% 50% 1% 88% 1
4 shouji 95% 33% 26% 7% 44% 9

Cheers,
Mayte

 

[Max]

How about modelling it up dynamically using formulas,
with tiebreakers built in to return all required results?

One easy play to deliver it on both counts here

Assume source table as posted in sheet: x, cols A to G, data from row2 down
In H2: =IF(G2="","",G2+ROW()/10^10)
In I2: =IF(G2="","",G2-ROW()/10^10)
Select H2:I2, copy down to cover the max expected extent of the "Rank"
formulas in col G. Leave H1:I1 blank

Then in a new sheet: Top10,
Put in A2
=IF(ROWS($1:1)>COUNT(x!$H:$H),"",INDEX(x!A:A,MATCH(SMALL(x!$H:$H,ROWS($1:1)),x!$H:$H,0)))
Copy A2 to G2, fill down to say G20? to cover the possibility of ties or
multiple ties in the Top 10.

Similarly, in another sheet: Bottom10,
Put in A2
=IF(ROWS($1:1)>COUNT(x!$I:$I),"",INDEX(x!A:A,MATCH(LARGE(x!$I:$I,ROWS($1:1)),x!$I:$I,0)))
Copy A2 to G2, fill down to say G20? to cover the possibility of ties or
multiple ties in the Bottom 10.

Paste the same col headers from x into Top10 & Bottom10, format the cols,
and there you have it, a functional model delivering the required results
dynamic to source data/formulas in x, up and about in a matter of minutes.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik

 

[Mayte]

thanks a lot !!!

 

[Max]

Welcome
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik