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

[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]

 

[sandy666]

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.

why not?

[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]Min 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]

450​

[/td][td]

08/10/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

1771235​

[/td][td]ABIL[/td][td]NOE[/td][td]

562​

[/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]

489​

[/td][td]

01/10/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

1771239​

[/td][td]ESQUIVEL[/td][td]MARIO[/td][td]

520​

[/td][td]

520​

[/td][td]

25/09/2019​

[/td][/tr]
[/table]

 

[Peter_SSs]

why not?

I'm certainly not an expert on Pivot Tables but I wasn't able to create that layout. Can you provide the steps required?

 

[sandy666]

sure

add TEST SCORE twice, one for MAX and one for MIN

edit:
no subtotals
no totals
report layout in table form

 

[Peter_SSs]

sure

Thanks. I'm glad I only said "I don't think" in post 10, never-the-less I withdraw the comment.

 

[sandy666]

Thanks.

You are welcome

Have a nice day

 

[Excelself]

That is what I did and it worked.

Got another question for you.

How can i figure out on what date the highest score was obtained and on what date the lowest score was obtained? I need a column with the highest score with the date it was obtained on the next column and same thing for the lowest score.

Thank you

A	B	C	D	E	F	G	H	I	J
LAST NAME	FIRST NAME	LAST NAME	FIRST NAME
SMITH	ALLEN	SMITH	ALLEN
ABIL	NOE	ABIL	NOE
VILLA	MARY	VILLA	MARY
SMITH	ALLEN	ESQUIVEL	MARIO
SMITH	ALLEN
ESQUIVEL	MARIO
VILLA	MARY

<tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]1[/TD]
[TD="align: right"]ID[/TD]

[TD="align: right"]TEST SCORE[/TD]
[TD="align: right"]TEST DATE[/TD]
[TD="align: right"]ID[/TD]

[TD="align: right"]HIGHEST SCORE[/TD]
[TD="align: right"]LAST TEST DATE[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]2[/TD]
[TD="align: right"]1771234[/TD]

[TD="align: right"]450[/TD]
[TD="align: right"]4/08/2019[/TD]
[TD="align: right"]1771234[/TD]

[TD="align: right"]600[/TD]
[TD="align: right"]8/10/2019[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]3[/TD]
[TD="align: right"]1771235[/TD]

[TD="align: right"]562[/TD]
[TD="align: right"]4/09/2019[/TD]
[TD="align: right"]1771235[/TD]

[TD="align: right"]562[/TD]
[TD="align: right"]4/09/2019[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]4[/TD]
[TD="align: right"]1771236[/TD]

[TD="align: right"]489[/TD]
[TD="align: right"]1/10/2019[/TD]
[TD="align: right"]1771236[/TD]

[TD="align: right"]596[/TD]
[TD="align: right"]1/10/2019[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]5[/TD]
[TD="align: right"]1771234[/TD]

[TD="align: right"]545[/TD]
[TD="align: right"]8/10/2019[/TD]
[TD="align: right"]1771239[/TD]

[TD="align: right"]520[/TD]
[TD="align: right"]25/09/2019[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]6[/TD]
[TD="align: right"]1771234[/TD]

[TD="align: right"]600[/TD]
[TD="align: right"]2/09/2019[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]7[/TD]
[TD="align: right"]1771239[/TD]

[TD="align: right"]520[/TD]
[TD="align: right"]25/09/2019[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]8[/TD]
[TD="align: right"]1771236[/TD]

[TD="align: right"]596[/TD]
[TD="align: right"]30/08/2019[/TD]

</tbody>

 

[sandy666]

to whom you are talking?

 

[sandy666]

with Power Query

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]ID[/td][td=bgcolor:#70AD47]LAST NAME[/td][td=bgcolor:#70AD47]FIRST NAME[/td][td=bgcolor:#70AD47]Max[/td][td=bgcolor:#70AD47]TEST DATE[/td][td=bgcolor:#70AD47]Min[/td][td=bgcolor:#70AD47]TEST DATE.1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

1771234​

[/td][td=bgcolor:#E2EFDA]SMITH[/td][td=bgcolor:#E2EFDA]ALLEN[/td][td=bgcolor:#E2EFDA]

600​

[/td][td=bgcolor:#E2EFDA]

09/02/2019​

[/td][td=bgcolor:#E2EFDA]

450​

[/td][td=bgcolor:#E2EFDA]

08/04/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

1771235​

[/td][td]ABIL[/td][td]NOE[/td][td]

562​

[/td][td]

09/04/2019​

[/td][td]

562​

[/td][td]

09/04/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

1771236​

[/td][td=bgcolor:#E2EFDA]VILLA[/td][td=bgcolor:#E2EFDA]MARY[/td][td=bgcolor:#E2EFDA]

596​

[/td][td=bgcolor:#E2EFDA]

30/08/2019​

[/td][td=bgcolor:#E2EFDA]

489​

[/td][td=bgcolor:#E2EFDA]

10/01/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

1771239​

[/td][td]ESQUIVEL[/td][td]MARIO[/td][td]

520​

[/td][td]

25/09/2019​

[/td][td]

520​

[/td][td]

25/09/2019​

[/td][/tr]
[/table]

but formula solution maybe someone else

 

[Excelself]

That is what I did and it worked perfectly thanks to you. I pulled a chunk of the database I handle at work and I am having problems with the followings:
1. I need to add a column to what you already helped me with that would tell me on what day did the student score low and on what day did the student score high along with their respective scores I also need the respective EFL (Educational Functional Level) on these days. A good example is Christopher Aguirre :his highest score was on 8/12/19 with a score of 550 and an EFL of 4. His lowest was obtained on 9/20/19 with a score of 482 and an EFL of 2. Therefore, I need column for date, high score, EFL for low and high scores.

Thank you for helping me. I really do appreciate it

<tbody>
[TD="width: 11%, bgcolor: #c0c0c0"] Student Id
[/TD]
[TD="width: 19%, bgcolor: #c0c0c0"] Student Last Name
[/TD]
[TD="width: 21%, bgcolor: #c0c0c0"] Student First Name
[/TD]
[TD="width: 21%, bgcolor: #c0c0c0"] Test Type Cd
[/TD]
[TD="width: 11%, bgcolor: #c0c0c0"] Test Dt
[/TD]
[TD="width: 12%, bgcolor: #c0c0c0"] Scale Score
[/TD]
[TD="width: 5%, bgcolor: #c0c0c0"] Efl
[/TD]

[TD="width: 11%, bgcolor: #ffffff"] 1793528
[/TD]
[TD="width: 19%, bgcolor: #ffffff"] Acevedo
[/TD]
[TD="width: 21%, bgcolor: #ffffff"] Blessing
[/TD]
[TD="width: 21%, bgcolor: #ffffff"] TABE 11/12 Reading
[/TD]
[TD="width: 11%, bgcolor: #ffffff"] 9/18/2019
[/TD]
[TD="width: 12%, bgcolor: #ffffff"] 493
[/TD]
[TD="width: 5%, bgcolor: #ffffff"] 2
[/TD]

[TD="width: 11%, bgcolor: #ffffff"] 1775820
[/TD]
[TD="width: 19%, bgcolor: #ffffff"] Acker
[/TD]
[TD="width: 21%, bgcolor: #ffffff"] Christopher
[/TD]
[TD="width: 21%, bgcolor: #ffffff"] TABE 11/12 Reading
[/TD]
[TD="width: 11%, bgcolor: #ffffff"] 8/8/2019
[/TD]
[TD="width: 12%, bgcolor: #ffffff"] 482
[/TD]
[TD="width: 5%, bgcolor: #ffffff"] 2
[/TD]

[TD="width: 11%, bgcolor: #ffffff"] 1812454
[/TD]
[TD="width: 19%, bgcolor: #ffffff"] Acosta
[/TD]
[TD="width: 21%, bgcolor: #ffffff"] Brittany
[/TD]
[TD="width: 21%, bgcolor: #ffffff"] TABE 11/12 Reading
[/TD]
[TD="width: 11%, bgcolor: #ffffff"] 10/9/2019
[/TD]
[TD="width: 12%, bgcolor: #ffffff"] 486
[/TD]
[TD="width: 5%, bgcolor: #ffffff"] 2
[/TD]

[TD="width: 11%, bgcolor: #ffffff"] 1797562
[/TD]
[TD="width: 19%, bgcolor: #ffffff"] Acosta
[/TD]
[TD="width: 21%, bgcolor: #ffffff"] Bryan
[/TD]
[TD="width: 21%, bgcolor: #ffffff"] TABE 11/12 Reading
[/TD]
[TD="width: 11%, bgcolor: #ffffff"] 7/19/2019
[/TD]
[TD="width: 12%, bgcolor: #ffffff"] 477
[/TD]
[TD="width: 5%, bgcolor: #ffffff"] 2
[/TD]

[TD="width: 11%, bgcolor: #ffffff"] 1801415
[/TD]
[TD="width: 19%, bgcolor: #ffffff"] Addison Jr
[/TD]
[TD="width: 21%, bgcolor: #ffffff"] Delaney
[/TD]
[TD="width: 21%, bgcolor: #ffffff"] TABE 11/12 Reading
[/TD]
[TD="width: 11%, bgcolor: #ffffff"] 9/11/2019
[/TD]
[TD="width: 12%, bgcolor: #ffffff"] 493
[/TD]
[TD="width: 5%, bgcolor: #ffffff"] 2
[/TD]

[TD="width: 11%, bgcolor: #ccffff"] 1790581

[/TD]
[TD="width: 19%, bgcolor: #ccffff"] Aguirre
[/TD]
[TD="width: 21%, bgcolor: #ccffff"] Christopher
[/TD]
[TD="width: 21%, bgcolor: #ccffff"] TABE 11/12 Reading
[/TD]
[TD="width: 11%, bgcolor: #ccffff"] 7/9/2019
[/TD]
[TD="width: 12%, bgcolor: #ccffff"] 487
[/TD]
[TD="width: 5%, bgcolor: #ccffff"] 2
[/TD]

[TD="width: 11%, bgcolor: #ccffff"] 1789539
[/TD]
[TD="width: 19%, bgcolor: #ccffff"] Aguirre

[/TD]
[TD="width: 21%, bgcolor: #ccffff"] Christopher
[/TD]
[TD="width: 21%, bgcolor: #ccffff"] TABE 11/12 Reading
[/TD]
[TD="width: 11%, bgcolor: #ccffff"] 9/20/2019
[/TD]
[TD="width: 12%, bgcolor: #ccffff"] 482
[/TD]
[TD="width: 5%, bgcolor: #ccffff"] 2
[/TD]

[TD="width: 11%, bgcolor: #ffffff"] 1782665

[/TD]
[TD="width: 19%, bgcolor: #ccffff"] Aguirre
[/TD]
[TD="width: 21%, bgcolor: #ccffff"] Christopher
[/TD]
[TD="width: 21%, bgcolor: #ffffff"] TABE 11/12 Reading
[/TD]
[TD="width: 11%, bgcolor: #ffffff"] 8/12/2019
[/TD]
[TD="width: 12%, bgcolor: #ffffff"] 550
[/TD]
[TD="width: 5%, bgcolor: #ffffff"] 4
[/TD]

[TD="width: 11%, bgcolor: #ccffff"] 1618791

[/TD]
[TD="width: 19%, bgcolor: #ccffff"] Albarran
[/TD]
[TD="width: 21%, bgcolor: #ccffff"] Jackielynn
[/TD]
[TD="width: 21%, bgcolor: #ccffff"] TABE 11/12 Reading
[/TD]
[TD="width: 11%, bgcolor: #ccffff"] 7/9/2019
[/TD]
[TD="width: 12%, bgcolor: #ccffff"] 572
[/TD]
[TD="width: 5%, bgcolor: #ccffff"] 4

[/TD]

</tbody>

 

[sandy666]

why the same student has different ID ?

btw, it would be good to know who you are addressing your posts to, eg. @Peter_SSs or @sandy666 etc...