Minimum value and date it occurred against a person

[kreases]

I have a simple table showing a list of golf scores with the name of the person and the date of the round. From this list I want to be able to put in a separate table the date and lowest score of each of the players

Name Score Date Name Date Best Score
Terry 73 01/05/2019 Terry 01/05/2019 73
Jack 71 01/05/2019 Jack
Jack 69 03/06/2019 Pete
Terry 77 03/06/2019
Pete 80 01/05/2019
Terry 75 21/06/2019
Jack 68 21/06/2019

Could someone please advise how I could do this please, thanks in advance,

John

 

[Aryatect]

Hi,

Below should work if you have MINIFS in your version of excel. Let me know if you don't:

Book1
	A	B	C	D	E	F	G	H
1	Name	Score	Date			Name	Date	Best
2	Terry	73	5/1/2019			Terry	5/1/2019	73
3	Jack	71	5/1/2019			Jack	6/21/2019	68
4	Jack	69	6/3/2019			Pete	5/1/2019	80
5	Terry	77	6/3/2019
6	Pete	80	5/1/2019
7	Terry	75	6/21/2019
8	Jack	68	6/21/2019
Sheet2

Cell Formulas
Range	Formula
G2	=SUMIFS($C$2:$C$8,$A$2:$A$8,F2,$B$2:$B$8,H2)
H2	=MINIFS($B$2:$B$8,$A$2:$A$8,F2)

 

[Aryatect]

above will not work well with duplicates, to handle duplicates please use below:

Book1
	A	B	C	D	E	F	G	H
1	Name	Score	Date			Name	Date	Best
2	Terry	73	5/1/2019			Terry	5/1/2019	73
3	Jack	71	5/1/2019			Jack	6/21/2019	68
4	Jack	69	6/3/2019			Pete	5/1/2019	80
5	Terry	73	6/3/2019
6	Pete	80	5/1/2019
7	Terry	75	6/21/2019
8	Jack	68	6/21/2019
Sheet2

Cell Formulas
Range	Formula
G2	{=INDEX($C$2:$C$8,MATCH(K2,($B$2:$B$8)*($A$2:$A$8=F2),0))}
H2	{=SMALL(IF($A$2:$A$8=F2,$B$2:$B$8),1)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[kreases]

Hi Aryatect,

In your G2 formula you refer to 'K2' which doesn't appear on the table, what cell should that be referencing. When I have entered this formula the date I get back is 00 January 1900 when it should be 08 July 2019,

Thanks for your help so far,

John

 

[Aryatect]

Sorry, there was date format issue (I have mm/dd/yyyy) so I moved some stuff, updated formula:

Book1
	A	B	C	D	E	F	G	H
1	Name	Score	Date			Name	Date	Best
2	Terry	73	5/1/2019			Terry	5/1/2019	73
3	Jack	71	5/1/2019			Jack	6/21/2019	68
4	Jack	69	6/3/2019			Pete	5/1/2019	80
5	Terry	78	6/3/2019
6	Pete	80	5/1/2019
7	Terry	75	6/21/2019
8	Jack	68	6/21/2019
Sheet2

Cell Formulas
Range	Formula
G2	{=INDEX($C$2:$C$8,MATCH(H2,($B$2:$B$8)*($A$2:$A$8=F2),0))}
H2	{=SMALL(IF($A$2:$A$8=F2,$B$2:$B$8),1)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[kreases]

Aryatect,

Thank you that works perfectly, thank you for taking the time to help me,

Regards,

John