Filter top 3 by group

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

SeaQuest - 07 Jun 2007 00:51 GMT

I have a table of student ID's (numeric) courses and marks such as
shown in this example:

ID Course Mark
123456 Math 11 80
123456 English 11 82
123456 Socials 11 75
123456 Science 11 92
123456 Geog 11 84
123456 Physics 11 70
234567 Math 12 65
234567 English 12 71
234567 Chem 12 60
234567 Geog 12 88
234567 Phys 12 70

I want to produce a list that shows the top 3 courses for each student
such as

ID Course Mark
123456 Science 11 92
123456 Geog 11 84
123456 English 11 82
234567 Geog 12 88
234567 English 12 71
234567 Phys 12 70

In other words I basically want to sort first by student ID then by
mark and filter out all but the top 3 marks for each student. I am not
having much success with this and am wondering if someone can assist
me?

Jim

Reply to this Message

Pete_UK - 07 Jun 2007 01:39 GMT

Sort your data set by ID (ascending order) and by Mark (descending
order), then put this formula in D2 (assuming row 1 is for headings):

=IF(A2=A1,D1+1,1)

Copy this formula down column D - you might like to put the heading
"Rank" in D1.

Then apply autofilter on column D - choose "Custom" then "Less than"
and 4, and you will have the top three scoring subjects for each
student ID. If you wish, you could highlight the visible rows of data,
click <copy> then move the cursor to a blank area (or even another
sheet) and paste the data in - only the filtered data will be copied
across.

Hope this helps.

Pete

> I have a table of student ID's (numeric) courses and marks such as
> shown in this example:
[quoted text clipped - 29 lines]
>
> Jim

Reply to this Message