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]

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?

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

 

[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
1	ID	LAST NAME	FIRST NAME	TEST SCORE	TEST DATE	ID	LAST NAME	FIRST NAME	HIGHEST SCORE	LAST TEST DATE
2	1771234	SMITH	ALLEN	450	4/08/2019	1771234	SMITH	ALLEN	600	8/10/2019
3	1771235	ABIL	NOE	562	4/09/2019	1771235	ABIL	NOE	562	4/09/2019
4	1771236	VILLA	MARY	489	1/10/2019	1771236	VILLA	MARY	596	1/10/2019
5	1771234	SMITH	ALLEN	545	8/10/2019	1771239	ESQUIVEL	MARIO	520	25/09/2019
6	1771234	SMITH	ALLEN	600	2/09/2019
7	1771239	ESQUIVEL	MARIO	520	25/09/2019
8	1771236	VILLA	MARY	596	30/08/2019

<tbody>
</tbody>

 

[sandy666]

to whom you are talking?

 

[sandy666]

with Power Query

ID	LAST NAME	FIRST NAME	Max	TEST DATE	Min	TEST DATE.1
1771234​	SMITH	ALLEN	600​	09/02/2019​	450​	08/04/2019​
1771235​	ABIL	NOE	562​	09/04/2019​	562​	09/04/2019​
1771236​	VILLA	MARY	596​	30/08/2019​	489​	10/01/2019​
1771239​	ESQUIVEL	MARIO	520​	25/09/2019​	520​	25/09/2019​

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

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
1789539	Aguirre	Christopher	TABE 11/12 Reading	9/20/2019	482	2
1782665	Aguirre	Christopher	TABE 11/12 Reading	8/12/2019	550	4
1618791	Albarran	Jackielynn	TABE 11/12 Reading	7/9/2019	572	4

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