Targeting top result for each entry

[reporting_95]

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Hi,[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]I have a workbook that lists all terms attended for each student as well as their GPA for that term in descending order. What I am trying to achieve is target the top entry for each student, which will give me the last term attended as well as their last known GPA. Please see a short example of a much larger list below. [/FONT]


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Student ID[/TD]
[TD]Last Name[/TD]
[TD]First Name[/TD]
[TD]Cumulative GPA [/TD]
[TD]Term[/TD]
[/TR]
[TR]
[TD][FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]122145[/FONT]<strike></strike>
[/TD]
[TD]Smith[/TD]
[TD]John[/TD]
[TD]3.1[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD][FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]122145[/FONT]<strike></strike>
[/TD]
[TD]Smith[/TD]
[TD]John[/TD]
[TD]2.9[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD][FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]122145[/FONT][/TD]
[TD]Smith<strike></strike>
[/TD]
[TD]John[/TD]
[TD]3.45[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]122651[/TD]
[TD]Reid[/TD]
[TD]Jane[/TD]
[TD]3.7[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]122651[/TD]
[TD]Reid[/TD]
[TD]Jane[/TD]
[TD]3.0[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]122156[/TD]
[TD]Hayes[/TD]
[TD]Ben[/TD]
[TD]4.0[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD][FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]122156[/FONT]
[/TD]
[TD]Hayes[/TD]
[TD]Ben[/TD]
[TD]3.52[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]

As you can see from the example, the last term attended is always the first entry for each student. I would need Term 5 for John, Term 4 for Jane, and Term 6 for Ben. This would give me their last term attended, and cumulative GPA. I tried filtering out by specific term but that would throw off the numbers because each student's last term attended is different.
Is there a way I can target the top entry for each student? Please kindly advise.
Thanks!!

 

[Eric W]

You could add a helper column which flags the uppermost entry per student. Then filter on that:

Book1
	A	B	C	D	E	F
1	Student ID	Last Name	First Name	Cumulative GPA	Term	Filter
2	122145	Smith	John	3.1	5	TRUE
3	122145	Smith	John	2.9	4	FALSE
4	122145	Smith	John	3.45	3	FALSE
5	122651	Reid	Jane	3.7	4	TRUE
6	122651	Reid	Jane	3	3	FALSE
7	122156	Hayes	Ben	4	6	TRUE
8	122156	Hayes	Ben	3.52	5	FALSE
Sheet3

Cell Formulas
Range	Formula
F2	=COUNTIFS($B$2:$B2,B2,$C$2:$C2,C2)=1

 

[reporting_95]

Wow great stuff, works perfectly. You ROCK Eric!

Thank you so much!!

 

[Eric W]

Glad to help!