How do I organize students best results?

[ALEXIA ZUPANCIC]

Hi to everyone.

For work, I am given a wide amount of datas to organize: these datas contain, for each student of an accounting course, in columns, the name of the test they took (it's 2 different tipologies), the name of the student, the surname of the student (these datas are repeated as many times as the times they took each test), and the score. For example, if a student took the test A 8 times, his name appears 8 times, and the score is different for each of the times. (Sorry for bad english, it's not my native language). 

For each student, I have to keep the best score in each test, and I have to eliminate all the other scores they obtained in the other attempts. Since I cannot do it manually because it requires a huge amount of time, how do I do this with a macro or a formula on excel?

Thank you so much

Alexia

[#2]

Hi Alexia,

I would recommend using Pivot Table feature in Excel. 

1) Select the data

2) Select Pivot Table from Insert menu

3) Drag Student details (ID, First Name, Last Name,..) to Rows

4) Drag Course details (ID, Name,..) to Columns

5) Drag score/marks to Values

6) Change 'Sum of Score' to 'Max of Score' from the Value Field Settings

Resulting table will give you the best score of every student for each course. I have attached a sample for you to check out.

Good luck!

Cheers,

Kasi