Lists in Excel

[Jaded]

Probably a longshot, but is there any way in Excel of doing this?

Basically, I have a list of 25 pupils and their test marks. Is there a way of writing a formula so that a list appears of (say) the top 10 pupils?

[GotaFancy?]

A few ways of doing this .......

1) Sort by the mark and just take the top 10 to do whatever you want?

2) Use the autofilter to show top 10 only (may have to use rank command on the list and autofilter by that column)

3) Put in a rank in the leftmost column - then in a different worksheet do a vlookup on the rank for the top 10.... (need to think carefully about how you deal with duplicates - often I get around this by adding on an incrementally very small number and then ranking the new value (which by defininition is unique - so no duplicates possible)...... believe me, not anywhere as complicated as I'm making it sound!!!!)

[GotaFancy?]

Thought Option 3 would make a bit more sense with an example......

Rank			Name	Score
22	2.001	0.001	Kid 1	2
16	8.002	0.002	Kid 2	8
18	6.003	0.003	Kid 3	6
11	11.004	0.004	Kid 4	11
15	8.005	0.005	Kid 5	8
9	12.006	0.006	Kid 6	12
13	9.007	0.007	Kid 7	9
10	11.008	0.008	Kid 8	11
17	6.009	0.009	Kid 9	6
20	5.01	0.01	Kid 10	5
24	0.011	0.011	Kid 11	0
23	0.012	0.012	Kid 12	0
19	5.013	0.013	Kid 13	5
4	16.014	0.014	Kid 14	16
1	19.015	0.015	Kid 15	19
3	17.016	0.016	Kid 16	17
21	4.017	0.017	Kid 17	4
2	17.018	0.018	Kid 18	17
12	9.019	0.019	Kid 19	9
5	15.02	0.02	Kid 20	15
7	14.021	0.021	Kid 21	14
14	8.022	0.022	Kid 22	8
8	12.023	0.023	Kid 23	12
6	14.024	0.024	Kid 24	14
Top 10
1	Kid 15	19
2	Kid 18	17
3	Kid 16	17
4	Kid 14	16
5	Kid 20	15
6	Kid 24	14
7	Kid 21	14
8	Kid 23	12
9	Kid 6	12
10	Kid 8	11

[Jaded]

Ooh... that looks good GaF, thanks! (I could have done option 1 by myself, I'm just really really lazy and wanted to save time if possible )