List top five results based on age grouping

[loza]

Have a sheet called TOTALS set out as
A5 B6 C5 to V5 W5 X5
name club ind events total age group
Fred aaaa 22 10
Anne bbbb 30 12
Tim aaaa 28 10

This sheet list everyone and every age group.
On a new sheet need to list Top Five Point Scorers for each Age Group eg

10 yrs
1 Name Club Points
2
3
4
5

Prefer a function other than filter or pivot table as results are updated
and automatically and will be used by others.

Many thanks
Loza

 

[Pete_UK]

Have a look at the LARGE function on the points range using parameters
1, 2, 3, 4 and 5 for the top 5. You will also have to use MATCH and
INDEX to get the name and the club, and you will probably have to deal
with ties (as in the two 10s in your example).

Hope this helps.

Pete

 

[Max]

My offer to you ..
a nice sample customized to suit your scenario (ready-to-use):
http://www.freefilehosting.net/download/3f42h
AutoList Top x by Age in separate shts.xls

The steps:
1. Set-up the parent sheet
In a sheet: x,
You have your source data in cols A to X (as posted), data from row6 down,
where the key cols are col W (points), and col X (age)

List the ages in AA5 across, eg: 10, 11, 12, …
Then place in AA6:
=IF($X6="","",IF($X6=AA$5,$W6-ROW()/10^10,""))
Copy AA6 across/fill down to cover the max expected extent of source data.
This quickly creates the criteria table with tie-breakers which will be read
in all the child sheets (the ones by age).

2. Set-up one child sheet
In a new sheet: 10yrs
Enter the age in B1: 10
In A3: =IF(B3="","",ROWS($1:1))
In B3:
=IF(ROWS($1:1)>COUNT(OFFSET(x!$Z$6:$Z$200,,MATCH($B$1,x!$AA$5:$IV$5,0))),"",INDEX(x!A$6:A$200,MATCH(LARGE(OFFSET(x!$Z$6:$Z$200,,MATCH($B$1,x!$AA$5:$IV$5,0)),ROWS($1:1)),OFFSET(x!$Z$6:$Z$200,,MATCH($B$1,x!$AA$5:$IV$5,0)),0)))
Copy B3 to D3.

In D3, amend the INDEX to point to col W in x, make it:
.... INDEX(x!W$6:W$200 ...

Select A33, copy down to say, D10. That should return the required
auto-results for age: 10 in cols A to D. Read-off the top 5 as desired. Ties,
if any, in the points will be displayed in the same relative order that they
appear in the source sheet: x.

Then to get the same for the rest of the age groups, just make copies of the
sheet: 10yrs, key in the age in B1 (eg: 11), relabel the sheetname to taste.