I have a database containing hundreds of students. That database has student ID numbers (column A), student last name (column B), students first name (column C), test scores (column D) , and test date (column E) as shown below. I want column F to display the student's ID number, column G, the student's last name, column H , the student's first name, column I, the student's highest score from all the tests he/she has taken and column J, the date when that student last tested. The problem is that the database contains information about a same student who has taken the test several times and therefore his/her ID and name appears multiple times with a different test score and a different test date. I want a formula to identify every student from the database by their ID, last name, first name, highest score, and last date he/she tested'.
Thank you!!!
Below is an example of a database
<tbody>
</tbody>
Thank you!!!
Below is an example of a database
ID | LAST NAME | FIRST NAME | TEST SCORE | TEST DATE | ID | LAST NAME | FIRST NAME | HIGHEST SCORE | LAST TEST DATE |
1771234 | SMITH | ALLEN | 450 | 8/4/19 | |||||
1771235 | ABIL | NOE | 562 | 9/4/19 | |||||
1771236 | VILLA | MARY | 489 | 10/1/19 | |||||
1771234 | SMITH | ALLEN | 545 | 10/8/19 | |||||
1771234 | SMITH | ALLEN | 600 | 9/2/19 | |||||
1771239 | ESQUIVEL | MARIO | 520 | 9/25/19 | |||||
1771236 | VILLA | MARY | 596 | 8/30/19 | |||||
<tbody>
</tbody>