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

Excelself · Oct 26, 2019

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

Peter_SSs · Oct 30, 2019

A formula approach below. However, note that since a student is doing the test multiple times it would be possible for them to get their highest (or lowest) score on 2 or more different dates with different EFL values.
If that occurs, my formulas would return the most recent date when the highest/lowest score was achieved & the corresponding EFL on that date (see lowest scores for Mary below).

Test Results <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:43px;" /><col style="width:68px;" /><col style="width:94px;" /><col style="width:98px;" /><col style="width:50px;" /><col style="width:88px;" /><col style="width:78px;" /><col style="width:49px;" /><col style="width:87px;" /><col style="width:77px;" /><col style="width:89px;" /></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><td >K</td><td >L</td><td >M</td><td >N</td><td >O</td><td >P</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; ">EFL</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; ">HIGH</td><td style="font-size:10pt; text-align:right; ">HIGH DATE</td><td style="font-size:10pt; text-align:right; ">HIGH EFL</td><td style="font-size:10pt; text-align:right; ">LOW</td><td style="font-size:10pt; text-align:right; ">LOW DATE</td><td style="font-size:10pt; text-align:right; ">LOW EFL</td><td style="font-size:10pt; text-align:right; ">LAST 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; ">1</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; text-align:right; ">5</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; ">1</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; ">2</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; ">2</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; ">2</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; ">3</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; text-align:right; ">7</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; ">3</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; ">4</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; text-align:right; ">6</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; text-align:right; ">6</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; text-align:right; ">5</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><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; text-align:right; ">6</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><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; text-align:right; ">7</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><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; " >9</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; ">30/08/2019</td><td style="font-size:10pt; text-align:right; ">8</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><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> <table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td >Spreadsheet Formulas</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 >G2</td><td >=IFERROR(INDEX($A$2:$A$100,MATCH(0,INDEX(COUNTIF($G$1:G1,$A$2:$A$100)+(A$2:A$100=""),0),0)),"")</td></tr><tr><td >H2</td><td >=IF($G2="","",INDEX(B$2:B$100,MATCH($G2,$A$2:$A$100,0)))</td></tr><tr><td >I2</td><td >=IF($G2="","",INDEX(C$2:C$100,MATCH($G2,$A$2:$A$100,0)))</td></tr><tr><td >J2</td><td >=IF($G2="","",AGGREGATE(14,6,D$2:D$100/($A$2:$A$100=$G2),1))</td></tr><tr><td >K2</td><td >=IF(G2="","",AGGREGATE(14,6,E$2:E$100/((A$2:A$100=G2)*(D$2:D$100=J2)),1))</td></tr><tr><td >L2</td><td >=IF(G2="","",INDEX(F:F,AGGREGATE(15,6,ROW(F$2:F$100)/((A$2:A$100=G2)*(D$2:D$100=J2)*(E$2:E$100=K2)),1)))</td></tr><tr><td >M2</td><td >=IF($G2="","",AGGREGATE(15,6,D$2:D$100/($A$2:$A$100=$G2),1))</td></tr><tr><td >N2</td><td >=IF(G2="","",AGGREGATE(14,6,E$2:E$100/((A$2:A$100=G2)*(D$2:D$100=M2)),1))</td></tr><tr><td >O2</td><td >=IF(G2="","",INDEX(F:F,AGGREGATE(15,6,ROW(F$2:F$100)/((A$2:A$100=G2)*(D$2:D$100=M2)*(E$2:E$100=N2)),1)))</td></tr><tr><td >P2</td><td >=IF($G2="","",AGGREGATE(14,6,E$2:E$100/($A$2:$A$100=$G2),1))</td></tr></table></td></tr></table>

Excelself · Oct 30, 2019

Sorry for the confusion, the post was meant for Peter. Yes, the ID number is the same for Christopher as rectified below.

Thank you

Student Id	Student Last Name	Student First Name	Test Type Cd	Test Dt	Scale Score	Efl
1793528	Acevedo	Blessing	TABE 11/12 Reading	9/18/2019	493	2
1775820	Acker	Christopher	TABE 11/12 Reading	8/8/2019	482	2
1812454	Acosta	Brittany	TABE 11/12 Reading	10/9/2019	486	2
1797562	Acosta	Bryan	TABE 11/12 Reading	7/19/2019	477	2
1801415	Addison Jr	Delaney	TABE 11/12 Reading	9/11/2019	493	2
1790581	Aguirre	Christopher	TABE 11/12 Reading	7/9/2019	487	2
17806581	Aguirre	Christopher	TABE 11/12 Reading	9/20/2019	482	2
1780581	Aguirre	Christopher	TABE 11/12 Reading	8/12/2019	550	4
1618791	Albarran	Jackielynn	TABE 11/12 Reading	7/9/2019	572	4

sandy666 · Oct 30, 2019

still different

and each time you answer use @[screenname]

Excelself · Oct 30, 2019

Thank you Peter, I did not realize you had already answered my post. Will try it and will let you know.

Good Day!

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

Excelself

New Member

Peter_SSs

MrExcel MVP, Moderator

Excel Facts

Excelself

New Member

sandy666

Banned - Rules violations

Excelself

New Member

Similar threads

Forum statistics

Share this page

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

Excelself

New Member

Peter_SSs

MrExcel MVP, Moderator

Excel Facts

Excelself

New Member

sandy666

Banned - Rules violations

Excelself

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock