Searching for a formula to display highest score and last date student tested

E

Excelself

New Member
Joined
Mar 16, 2018
Messages
22
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

[TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]LAST NAME[/TD]
[TD]FIRST NAME[/TD]
[TD]TEST SCORE[/TD]
[TD]TEST DATE[/TD]
[TD]ID[/TD]
[TD]LAST NAME[/TD]
[TD]FIRST NAME[/TD]
[TD]HIGHEST SCORE[/TD]
[TD]LAST TEST DATE[/TD]
[/TR]
[TR]
[TD]1771234[/TD]
[TD]SMITH[/TD]
[TD]ALLEN[/TD]
[TD]450[/TD]
[TD]8/4/19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1771235[/TD]
[TD]ABIL[/TD]
[TD]NOE[/TD]
[TD]562[/TD]
[TD]9/4/19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1771236[/TD]
[TD]VILLA[/TD]
[TD]MARY[/TD]
[TD]489[/TD]
[TD]10/1/19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1771234[/TD]
[TD]SMITH[/TD]
[TD]ALLEN[/TD]
[TD]545[/TD]
[TD]10/8/19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1771234[/TD]
[TD]SMITH[/TD]
[TD]ALLEN[/TD]
[TD]600[/TD]
[TD]9/2/19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1771239[/TD]
[TD]ESQUIVEL[/TD]
[TD]MARIO[/TD]
[TD]520[/TD]
[TD]9/25/19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1771236[/TD]
[TD]VILLA[/TD]
[TD]MARY[/TD]
[TD]596[/TD]
[TD]8/30/19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
you can do that with standard PivotTable

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]ID[/td][td=bgcolor:#DDEBF7]LAST NAME[/td][td=bgcolor:#DDEBF7]FIRST NAME[/td][td=bgcolor:#DDEBF7]Max of TEST SCORE[/td][td=bgcolor:#DDEBF7]Max of TEST DATE[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
1771234​
[/td][td]SMITH[/td][td]ALLEN[/td][td]
600​
[/td][td]
08/10/2019​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
1771235​
[/td][td]ABIL[/td][td]NOE[/td][td]
562​
[/td][td]
04/09/2019​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
1771236​
[/td][td]VILLA[/td][td]MARY[/td][td]
596​
[/td][td]
01/10/2019​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
1771239​
[/td][td]ESQUIVEL[/td][td]MARIO[/td][td]
520​
[/td][td]
25/09/2019​
[/td][/tr]
[/table]
 
Last edited:
Upvote 0
Excelself said:
Is there a formula that can make that work?
Click to expand...
Sure.
F2 copied down as far as you might need (after adjusting the $100s to be sure to cover the range of your data)
G2 copied across to H2 and down
I2 copied across to J2 and down. You will then have to format column J to display dates.

<b>Test Results</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:68px;" /><col style="width:94px;" /><col style="width:98px;" /><col style="width:102px;" /><col style="width:89px;" /><col style="width:68px;" /><col style="width:94px;" /><col style="width:98px;" /><col style="width:127px;" /><col style="width:127px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:10pt; text-align:right; ">ID</td><td style="font-size:10pt; ">LAST NAME</td><td style="font-size:10pt; ">FIRST NAME</td><td style="font-size:10pt; text-align:right; ">TEST SCORE</td><td style="font-size:10pt; text-align:right; ">TEST DATE</td><td style="font-size:10pt; text-align:right; ">ID</td><td style="font-size:10pt; ">LAST NAME</td><td style="font-size:10pt; ">FIRST NAME</td><td style="font-size:10pt; text-align:right; ">HIGHEST SCORE</td><td style="font-size:10pt; text-align:right; ">LAST TEST DATE</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; text-align:right; ">1771234</td><td style="font-size:10pt; ">SMITH</td><td style="font-size:10pt; ">ALLEN</td><td style="font-size:10pt; text-align:right; ">450</td><td style="font-size:10pt; text-align:right; ">4/08/2019</td><td style="font-size:10pt; text-align:right; ">1771234</td><td style="font-size:10pt; ">SMITH</td><td style="font-size:10pt; ">ALLEN</td><td style="font-size:10pt; text-align:right; ">600</td><td style="font-size:10pt; text-align:right; ">8/10/2019</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:10pt; text-align:right; ">1771235</td><td style="font-size:10pt; ">ABIL</td><td style="font-size:10pt; ">NOE</td><td style="font-size:10pt; text-align:right; ">562</td><td style="font-size:10pt; text-align:right; ">4/09/2019</td><td style="font-size:10pt; text-align:right; ">1771235</td><td style="font-size:10pt; ">ABIL</td><td style="font-size:10pt; ">NOE</td><td style="font-size:10pt; text-align:right; ">562</td><td style="font-size:10pt; text-align:right; ">4/09/2019</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:10pt; text-align:right; ">1771236</td><td style="font-size:10pt; ">VILLA</td><td style="font-size:10pt; ">MARY</td><td style="font-size:10pt; text-align:right; ">489</td><td style="font-size:10pt; text-align:right; ">1/10/2019</td><td style="font-size:10pt; text-align:right; ">1771236</td><td style="font-size:10pt; ">VILLA</td><td style="font-size:10pt; ">MARY</td><td style="font-size:10pt; text-align:right; ">596</td><td style="font-size:10pt; text-align:right; ">1/10/2019</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:10pt; text-align:right; ">1771234</td><td style="font-size:10pt; ">SMITH</td><td style="font-size:10pt; ">ALLEN</td><td style="font-size:10pt; text-align:right; ">545</td><td style="font-size:10pt; text-align:right; ">8/10/2019</td><td style="font-size:10pt; text-align:right; ">1771239</td><td style="font-size:10pt; ">ESQUIVEL</td><td style="font-size:10pt; ">MARIO</td><td style="font-size:10pt; text-align:right; ">520</td><td style="font-size:10pt; text-align:right; ">25/09/2019</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-size:10pt; text-align:right; ">1771234</td><td style="font-size:10pt; ">SMITH</td><td style="font-size:10pt; ">ALLEN</td><td style="font-size:10pt; text-align:right; ">600</td><td style="font-size:10pt; text-align:right; ">2/09/2019</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-size:10pt; text-align:right; ">1771239</td><td style="font-size:10pt; ">ESQUIVEL</td><td style="font-size:10pt; ">MARIO</td><td style="font-size:10pt; text-align:right; ">520</td><td style="font-size:10pt; text-align:right; ">25/09/2019</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="font-size:10pt; text-align:right; ">1771236</td><td style="font-size:10pt; ">VILLA</td><td style="font-size:10pt; ">MARY</td><td style="font-size:10pt; text-align:right; ">596</td><td style="font-size:10pt; text-align:right; ">30/08/2019</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >F2</td><td >=IFERROR(INDEX<span style=' color:008000; '>($A$2:$A$100,MATCH<span style=' color:#0000ff; '>(0,INDEX<span style=' color:#ff0000; '>(COUNTIF<span style=' color:#804000; '>($F$1:F1,$A$2:$A$100)</span>+<span style=' color:#804000; '>(A$2:A$100="")</span>,0)</span>,0)</span>)</span>,"")</td></tr><tr><td >G2</td><td >=IF($F2="","",INDEX<span style=' color:008000; '>(B$2:B$100,MATCH<span style=' color:#0000ff; '>($F2,$A$2:$A$100,0)</span>)</span>)</td></tr><tr><td >I2</td><td >=IF($F2="","",AGGREGATE<span style=' color:008000; '>(14,6,D$2:D$100/<span style=' color:#0000ff; '>($A$2:$A$100=$F2)</span>,1)</span>)</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4
 
Upvote 0
It worked out perfectly. I did not get the formula for the last date the student tested and if you could give me the formula for finding the lowest score as well.

Thank you
 
Upvote 0
Cheers, glad you got it sorted out. Just for confirmation, the issue of latest date was given with
Peter_SSs said:
I2 copied across to J2 and down. You will then have to format column J to display dates.
Click to expand...
.. and lowest score is the same formula as highest score with the first argument in the AGGREGATE function changed from 14 to 15.


BTW, I don't think a Pivot Table would give you the results with all the information for a student on a single row like you requested.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Return High Value for multiple instances
Replies
5
Views
649
Fluff
Fluff
J
  • Question Question
Take Excel Worksheet, Divide into Worksheets/Workbooks by Employee/Card #, Email via Gmail to Employee for Input
Replies
9
Views
864
jennifer7i884
J
G
  • Question Question
Formula to count with multiple criteria and exclude from a separate column, then sort by count in one cell with spill
Replies
0
Views
571
Greenbehindthecells
G
S
  • Solved
Seperate Data In One Cell Across Multiple Cells
Replies
6
Views
2K
Alex Blakenburg
A
richh
  • Question Question
Obtain Highest Score Based on Two sets of Dated Tests
Replies
3
Views
529
richh
richh

Forum statistics

Threads
1,223,885
Messages
6,175,186
Members
452,615
Latest member
bogeys2birdies

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top