max formula

[boogie]

A B C D E
1 M012 Martin Computer 90
2 Math 75
3 History 65
4 Science 100
5
6 E013 Ernnie Computer 100
7 Math 93
8 History 70
9 Science 85

Question: ColumnA is the IDNumber. ColumnB
are the students' Name, C are Subject and D are Grades.
On column E1, I want to get who is the highest
per subject (with name,subject and grade) so that we can give
them awards. I have 50 different subjects in all grade
level in 4000 rows and more than 300 students. Thanks.

 

[Frank Kabel]

Hi
first a question:
Looking at your example suggests that the iD Nmber and the name is NOT
filled for all rows (this would make it a little bit complicated). Any
chance to repeat these two fileds for all rows?

 

[Aladin Akyurek]

I believe it would be an option of choice to use pivot tables here...

Assuming that you insert a row of labels before your sample data like
Id,Name,Subject, and Grade...

1) Select the data area including labels.
2) Activate Data|PivotTable and PivotChart Report...
At the Layout step...
3) Drag the Subject button to the PAGE field.
4) Drag the Id and Name buttons to the ROW field.
5) Drag the Grade button to the DATA field.
6) Change Count of Grade to Max of Grade: double click on Count of Grade and
choose for Max.
7) Click Finish.

Now hide Totals and blank values: Click on these values and activate Hide.

This last step is important:

Click on Name in the resulting pivot table/area.
Activate the Advanced button.
Check Automatic for AutoShow options.

Set the value for Top to 1.

Note. A formula-based approach, as described in: http://tinyurl.com/3hzxf,
is also possible if you can extract data per subject in separate areas.