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

[Excelself]

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

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>

 

[sandy666]

you can do that with standard PivotTable

ID	LAST NAME	FIRST NAME	Max of TEST SCORE	Max of TEST DATE
1771234​	SMITH	ALLEN	600​	08/10/2019​
1771235​	ABIL	NOE	562​	04/09/2019​
1771236​	VILLA	MARY	596​	01/10/2019​
1771239​	ESQUIVEL	MARIO	520​	25/09/2019​

 

[Excelself]

Sounds good, but i am not familiar with pivot tables. Is there a formula that can make that work?

 

[sandy666]

I don't know
I prefer life easier not complicated

have a nice day

 

[Peter_SSs]

Is there a formula that can make that work?

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

 

[Excelself]

ok
Thank you
I will learn Pivot Table. Right, the easiest way is best.

 

[Excelself]

Thank you. Will try that.

Appreciate your help and time.

 

[Excelself]

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

 

[Excelself]

Never mind. I figured it out and Thanks to you.

 

[Peter_SSs]

Cheers, glad you got it sorted out. Just for confirmation, the issue of latest date was given with

I2 copied across to J2 and down. You will then have to format column J to display dates.

.. 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.