Display name of highest score on a particular date

[MrNoobster]

Good Evening All,

I have myself a customised version of the below formula which gives me the information I require, however I would like to add in date.
So as per the example below, we have Thomas who scored 128. I want it to read Thomas scored 128 on (date). The date would be on a range of cells but I'm not sure where to add this into the corresponding formula.

My formula is =INDEX(B18:B3000,MATCH(MAX(AB18:AB3000),AB18:AB3000,FALSE),)&" with "&MAX(AB18:AB3000)&" trailer moves on " I want to add "on (specific date in a range of cells that correspond to the name)


Can anyone help?

Many thanks




Please enter this formula: =INDEX(A2:A14,MATCH(MAX(B2:B14),B2:B14,FALSE),)&" Scored "&MAX(B2:B14) into a blank cell where you want to display the name, and then press Enter key to return the result as follows:

 

[Peter_SSs]

Wouldn't it be possible for more than one person to have equal highest score on a particular date?
If so, you could try this formula, copied down.

Book1
	A	B	C	D	E	F
1	Name	Date	Score		Date	Name(s)
2	Name 16	22/09/2017	42		24/09/2017	Name 10
3	Name 29	22/09/2017	74			Name 28
4	Name 46	22/09/2017	40
5	Name 2	22/09/2017	62
6	Name 78	22/09/2017	52
7	Name 43	23/09/2017	42
8	Name 69	23/09/2017	77
9	Name 56	24/09/2017	67
10	Name 10	24/09/2017	80
11	Name 58	24/09/2017	78
12	Name 28	24/09/2017	80
13	Name 72	24/09/2017	73
14	Name 32	25/09/2017	48
15	Name 71	25/09/2017	46
16	Name 75	25/09/2017	65
17	Name 35	25/09/2017	44
18	Name 64	25/09/2017	80
19	Name 62	25/09/2017	44
20
High Score on Date

Cell Formulas
Range	Formula
F2:F5	F2	=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(A$2:A$19)/((B$2:B$19=E$2)*(C$2:C$19=AGGREGATE(14,6,C$2:C$19/(B$2:B$19=E$2),1))),ROWS(F$2:F2))),"")

 

[Peter_SSs]

.. or perhaps like this

Book1
	A	B	C	D	E	F	G
1	Name	Date	Score		Date	Max for date	Name(s)
2	Name 16	22/09/2017	42		24/09/2017	80	Name 10
3	Name 29	22/09/2017	74				Name 28
4	Name 46	22/09/2017	40
5	Name 2	22/09/2017	62
6	Name 78	22/09/2017	52
7	Name 43	23/09/2017	42
8	Name 69	23/09/2017	77
9	Name 56	24/09/2017	67
10	Name 10	24/09/2017	80
11	Name 58	24/09/2017	78
12	Name 28	24/09/2017	80
13	Name 72	24/09/2017	73
14	Name 32	25/09/2017	48
15	Name 71	25/09/2017	46
16	Name 75	25/09/2017	65
17	Name 35	25/09/2017	44
18	Name 64	25/09/2017	80
19	Name 62	25/09/2017	44
20
High Score on Date

Cell Formulas
Range	Formula
F2	F2	=AGGREGATE(14,6,C2:C19/(B2:B19=E2),1)
G2:G5	G2	=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(A$2:A$19)/((B$2:B$19=E$2)*(C$2:C$19=F$2)),ROWS(G$2:G2))),"")

 

[MrNoobster]

Hi Peter, and thanks for your reply. I don't think I explained what i needed very well however that formula is useful for something else I'm working on so I thank you for that. I'll try to be a bit more specific with my actual data:

I have this table:

Name	Total Moves to Date	Total Shunt Hours	% of Moves	Moves per hour	Number of Shifts	% of Shifts
Ollie Calcott	356	104	47.28%	3.42	14	31.82%
Mark Whitehead	65	48	8.63%	1.35	6	13.64%
Alan Smith	83	48	11.02%	1.73	6	13.64%
Kai Fluke	127	88	16.87%	1.44	11	25.00%
Nigel Southwood	59	19	7.84%	3.11	2	4.55%
Mark Wagstaff	0	0	0.00%	#DIV/0!	0	0.00%
Bernard Coyle	0	0	0.00%	#DIV/0!	0	0.00%
Wayne Smith	10	8	1.33%	1.25	1	2.27%
John Black	53	32	7.04%	1.66	4	9.09%
Arek Dregier	0	0	0.00%	#DIV/0!	0	0.00%
Thomas Mulvany	0	0	0.00%	#DIV/0!	0	0.00%

What i'm trying to create is a cell which states simply that (name) with (number) trailer moves on (date).

At the moment it looks like this:

	Most moves on a single shift:
	Nigel Southwood with 51 trailer moves on

The cell that says "Nigel Southwood with 51 trailer moves on" has the following formula:

=INDEX(B18:B3001,MATCH(MAX(AB18:AB3001),AB18:AB3001,FALSE),)&" with "&MAX(AB18:AB3001)&" trailer moves on "

I have a separate column with dates corresponding to the trailer moves a driver has completed, which is A18:A3000.

I would like the cell to read "Nigel Southwood with 51 trailer moves on Monday 6th January" so with the original formula I believe something needs adding to it to make that happen but I can't wrap my brain around it.

Many thanks in advance

 

[Peter_SSs]

What rows and columns have you shown us there? (would be clearer if you could use XL2BB like I did above. )

And what relationship does that sample data have to getting the result? I can't see 51 anywhere in that data & I can't see any dates. Don't we need to be able to see a small sample of columns A and AB and how they relate to this shown data and/or the actual result required? Those columns are used in your formula but from what I can tell they are not part of the table you have shown.

Also, isn't my previous question still valid? Isn't it 'possible' for the maximum value in column AB to occur on more than one row of column AB?

 

[MrNoobster]

Hi Peter,

Thanks for the advice, I've just installed XL2BB...please see below. I apologise if it looks messy but I'm still rather new to posting on forums. This is my entire front sheet which generates information on other tabs however this is my master tab.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
1	Name	Total Moves to Date	Total Shunt Hours	% of Moves	Moves per hour	Number of Shifts	% of Shifts			Most moves on a single shift:				07:00 - 15:00
2	Ollie Calcott	356	104	47.28%	3.42	14	31.82%			Nigel Southwood with 51 trailer moves on				22:00 - 06:00
3	Mark Whitehead	65	48	8.63%	1.35	6	13.64%							06:00 - 14:00
4	Alan Smith	83	48	11.02%	1.73	6	13.64%							14:00 - 22:00
5	Kai Fluke	127	88	16.87%	1.44	11	25.00%		YTD AVG Hourly Trailer Moves					15:00 - 22:00
6	Nigel Southwood	59	19	7.84%	3.11	2	4.55%
7	Mark Wagstaff	0	0	0.00%	#DIV/0!	0	0.00%	00:00 - 01:00	01:00 - 02:00	02:00 - 03:00	03:00 - 04:00	04:00 - 05:00	05:00 - 06:00	06:00 - 07:00	07:00 - 08:00	08:00 - 09:00	09:00 - 10:00	10:00 - 11:00	11:00 - 12:00	12:00 - 13:00	13:00 - 14:00	14:00 - 15:00	15:00 - 16:00	16:00 - 17:00	17:00 - 18:00	18:00 - 19:00	19:00 - 20:00	20:00 - 21:00	21:00 - 22:00
8	Bernard Coyle	0	0	0.00%	#DIV/0!	0	0.00%	3.80	2.87	2.73	3.00	3.64	2.64	2.00	1.63	2.38	1.00	1.63	0.88	1.13	1.19	0.50	0.75	1.77	1.46	0.92	2.92	2.62	2.85
9	Wayne Smith	10	8	1.33%	1.25	1	2.27%
10	John Black	53	32	7.04%	1.66	4	9.09%		YTD Total Trailer Moves
11	Arek Dregier	0	0	0.00%	#DIV/0!	0	0.00%	00:00 - 01:00	01:00 - 02:00	02:00 - 03:00	03:00 - 04:00	04:00 - 05:00	05:00 - 06:00	06:00 - 07:00	07:00 - 08:00	08:00 - 09:00	09:00 - 10:00	10:00 - 11:00	11:00 - 12:00	12:00 - 13:00	13:00 - 14:00	14:00 - 15:00	15:00 - 16:00	16:00 - 17:00	17:00 - 18:00	18:00 - 19:00	19:00 - 20:00	20:00 - 21:00	21:00 - 22:00
12	Thomas Mulvany	0	0	0.00%	#DIV/0!	0	0.00%	57	43	41	42	51	37	32	26	38	16	26	14	18	19	6	9	23	19	12	38	34	37
13		753	347			44
14
15
16
17	Date	Shunter	Shift	00:00 - 01:00	01:00 - 02:00	02:00 - 03:00	03:00 - 04:00	04:00 - 05:00	05:00 - 06:00	06:00 - 07:00	07:00 - 08:00	08:00 - 09:00	09:00 - 10:00	10:00 - 11:00	11:00 - 12:00	12:00 - 13:00	13:00 - 14:00	14:00 - 15:00	15:00 - 16:00	16:00 - 17:00	17:00 - 18:00	18:00 - 19:00	19:00 - 20:00	20:00 - 21:00	21:00 - 22:00	22:00 - 23:00	23:00 - 00:00	Total moves for shift	Total shift time
18	Wednesday 1st January	Ollie Calcott	22:00 - 06:00	6	2	4	2	0	8																	7	1	30	8
19	Thursday 2nd January	Alan Smith	06:00 - 14:00							4	8	0	2	2	0	0	2											18	8
20	Thursday 2nd January	Mark Whitehead	14:00 - 22:00															0	4	0	2	0	1	0	4			11	8
21	Thursday 2nd January	Ollie Calcott	22:00 - 06:00	0	0	9	5	2	7																	10	1	34	8
22	Friday 3rd January	Alan Smith	06:00 - 14:00							2	2	4	0	7	3	0	1											19	8
23	Friday 3rd January	Mark Whitehead	14:00 - 22:00															0	0	0	0	0	5	1	1			7	8
24	Saturday 4th January	Mark Whitehead	06:00 - 14:00							4	3	0	2	0	3	2	7											21	8
25	Sunday 5th January	Ollie Calcott	22:00 - 06:00	5	1	1	0	5	1																	1	4	18	8
26	Monday 6th January	Kai Fluke	06:00 - 14:00							0	0	0	1	2	0	0	0											3	8
27	Monday 6th January	Mark Whitehead	14:00 - 22:00															0	2	3	0	5	0	1	0			11	8
28	Monday 6th January	Ollie Calcott	22:00 - 06:00	14	0	2	6	4	2																	4	7	39	8
29	Tuesday 7th January	Kai Fluke	06:00 - 14:00							0	0	0	0	4	0	3	0											7	8
30	Tuesday 7th January	Alan Smith	14:00 - 22:00															1	0	0	4	2	4	3	4			18	8
31	Tuesday 7th January	Ollie Calcott	22:00 - 06:00	3	0	2	2	7	1																	8	8	31	8
32	Wednesday 8th January	Kai Fluke	06:00 - 14:00							0	3	3	0	4	0	0	2											12	8
33	Wednesday 8th January	Alan Smith	14:00 - 22:00															1	0	4	2	0	4	2	3			16	8
34	Wednesday 8th January	Ollie Calcott	22:00 - 06:00	2	4	0	3	5	2																	4	5	25	8
35	Thursday 9th January	Kai Fluke	06:00 - 14:00							0	0	6	0	0	0	5	0											11	8
36	Thursday 9th January	Alan Smith	14:00 - 22:00															1	0	1	0	0	0	3	0			5	8
37	Thursday 9th January	Ollie Calcott	22:00 - 06:00	2	2	4	4	6	0																	7	13	38	8
38	Friday 10th January	Kai Fluke	06:00 - 14:00							0	0	8	0	0	3	0	0											11	8
39	Friday 10th January	Alan Smith	14:00 - 22:00															0	0	2	0	2	0	3	0			7	8
40	Friday 10th January	Nigel Southwood	22:00 - 06:00	1	3	1	1	0	0																	2	0	8	8
41	Saturday 11th January	Mark Whitehead	06:00 - 14:00							4	1	0	5	0	0	2	0											12	8
42	Sunday 12th January	Ollie Calcott	22:00 - 06:00	3	2	1	1	3	0																	9	0	19	8
43	Monday 13th January	Mark Whitehead	06:00 - 14:00							0	0	3	0	0	0	0	0											3	8
44	Monday 13th January	Kai Fluke	14:00 - 22:00															0	2	2	0	0	5	1	2			12	8
45	Monday 13th January	Ollie Calcott	22:00 - 06:00	9	10	0	6	2	7																	2	0	36	8
46	Tuesday 14th January	John Black	07:00 - 15:00							1	3	0	1	6	1	3	3											18	8
47	Tuesday 14th January	Kai Fluke	14:00 - 22:00															0	0	0	5	0	2	4	7			18	8
48	Tuesday 14th January	Ollie Calcott	22:00 - 06:00	1	2	2	4	6	4																	4	3	26	8
49	Wednesday 15th January	John Black	07:00 - 15:00							5	1	5	0	0	1	3	0											15	8
50	Wednesday 15th January	Kai Fluke	14:00 - 22:00															0	1	4	0	0	2	1	2			10	8
51	Wednesday 15th January	Ollie Calcott	22:00 - 06:00	4	2	0	2	0	4																	2	4	18	8
52	Thursday 16th January	John Black	07:00 - 15:00							4	1	3	2	0	0	0	0											10	8
53	Thursday 16th January	Kai Fluke	14:00 - 22:00															2	0	3	0	0	4	5	2			16	8
54	Thursday 16th January	Ollie Calcott	22:00 - 06:00	0	6	6	0	7	0																	0	5	24	8
55	Friday 17th January	John Black	07:00 - 15:00							7	0	0	2	0	1	0	0											10	8
56	Friday 17th January	Kai Fluke	14:00 - 22:00															1	0	1	5	0	6	0	5			18	8
57	Saturday 18th January	Wayne Smith	06:00 - 14:00							1	2	3	1	1	0	0	2											10	8
58	Sunday 19th January	Ollie Calcott	22:00 - 06:00	3	0	2	6	4	1																	0	2	18	8
59	Monday 20th January	Kai Fluke	06:00 - 14:00							0	2	3	0	0	2	0	2											9	8
60	Monday 20th January	Nigel Southwood	16:00 - 03:00	4	9	7														3	1	3	5	10	7	2	0	51	11
61	Monday 20th January	Ollie Calcott	03:00 - 06:00																									0	0
Master

Cell Formulas
Range	Formula
B2	B2	=SUMIF(B18:B3003,A2,AB18:AB3003)
C2	C2	=SUMIF(B18:B3003,A2,AC18:AC3003)
D2	D2	=B2/SUM(B2:B12)
E2:E12	E2	=B2/C2
B3	B3	=SUMIF(B18:B3003,A3,AB18:AB3003)
C3	C3	=SUMIF(B18:B3003,A3,AC18:AC3003)
D3	D3	=B3/SUM(B2:B12)
B4	B4	=SUMIF(B18:B3003,A4,AB18:AB3003)
C4	C4	=SUMIF(B18:B3003,A4,AC18:AC3003)
D4	D4	=B4/SUM(B2:B12)
B5	B5	=SUMIF(B18:B3003,A5,AB18:AB3003)
C5	C5	=SUMIF(B18:B3003,A5,AC18:AC3003)
D5	D5	=B5/SUM(B2:B12)
B6	B6	=SUMIF(B18:B3003,A6,AB18:AB3003)
C6	C6	=SUMIF(B18:B3003,A6,AC18:AC3003)
D6	D6	=B6/SUM(B2:B12)
B7	B7	=SUMIF(B18:B3003,A7,AB18:AB3003)
C7	C7	=SUMIF(B18:B3003,A7,AC18:AC3003)
D7	D7	=B7/SUM(B2:B12)
B8	B8	=SUMIF(B18:B3003,A8,AB18:AB3003)
C8	C8	=SUMIF(B18:B3003,A8,AC18:AC3003)
D8	D8	=B8/SUM(B2:B12)
B9	B9	=SUMIF(B18:B3003,A9,AB18:AB3003)
C9	C9	=SUMIF(B18:B3003,A9,AC18:AC3003)
D9	D9	=B9/SUM(B2:B12)
B10	B10	=SUMIF(B18:B3003,A10,AB18:AB3003)
C10	C10	=SUMIF(B18:B3003,A10,AC18:AC3003)
D10	D10	=B10/SUM(B2:B12)
B11	B11	=SUMIF(B18:B3003,A11,AB18:AB3003)
C11	C11	=SUMIF(B18:B3003,A11,AC18:AC3003)
D11	D11	=B11/SUM(B2:B12)
B12	B12	=SUMIF(B18:B3003,A12,AB18:AB3003)
C12	C12	=SUMIF(B18:B3003,A12,AC18:AC3003)
D12	D12	=B12/SUM(B2:B12)
J2	J2	=INDEX(B18:B3001,MATCH(MAX(AB18:AB3001),AB18:AB3001,FALSE),)&" with "&MAX(AB18:AB3001)&" trailer moves on "
H8:AC8	H8	=AVERAGE(D18:D3003)
G2	G2	=F2/SUM(F2:F12)
G3	G3	=F3/SUM(F2:F12)
G4	G4	=F4/SUM(F2:F12)
G5	G5	=F5/SUM(F2:F12)
G6	G6	=F6/SUM(F2:F12)
G7	G7	=F7/SUM(F2:F12)
G8	G8	=F8/SUM(F2:F12)
G9	G9	=F9/SUM(F2:F12)
G10	G10	=F10/SUM(F2:F12)
G11	G11	=F11/SUM(F2:F12)
G12	G12	=F12/SUM(F2:F12)
H12:AC12	H12	=SUM(D18:D3003)
B13:C13, F13	B13	=SUM(B2:B12)
F2	F2	=COUNTIF(B18:B3002,A2)
F3	F3	=COUNTIF(B18:B3002,A3)
F4	F4	=COUNTIF(B18:B3002,A4)
F5	F5	=COUNTIF(B18:B3002,A5)
F6	F6	=COUNTIF(B18:B3002,A6)
F7	F7	=COUNTIF(B18:B3002,A7)
F8	F8	=COUNTIF(B18:B3002,A8)
F9	F9	=COUNTIF(B18:B3002,A9)
F10	F10	=COUNTIF(B18:B3002,A10)
F11	F11	=COUNTIF(B18:B3002,A11)
F12	F12	=COUNTIF(B18:B3002,A12)
AB18:AB61	AB18	=SUM(D18:AA18)
AC18:AC61	AC18	=COUNT(D18:AA18)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E2:E12	Cell Value	top 1 bottom values	text	NO
E2:E12	Cell Value	top 1 values	text	NO
D2:D12	Cell Value	top 1 bottom values	text	NO
D2:D12	Cell Value	top 1 values	text	NO
B2:B12	Cell Value	top 1 bottom values	text	NO
B2:B12	Cell Value	top 1 values	text	NO
G2:G12	Cell Value	top 1 bottom values	text	NO
G2:G12	Cell Value	top 1 values	text	NO
F2:F12	Cell Value	top 1 bottom values	text	NO
F2:F12	Cell Value	top 1 values	text	NO
C2:C12	Cell Value	top 1 bottom values	text	NO
C2:C12	Cell Value	top 1 values	text	NO

Cells with Data Validation
Cell	Allow	Criteria
B18:B61	List	=$A$2:$A$12
C18:C59	List	=$N$1:$N$5

 

[Peter_SSs]

Thanks.
So in that example, you want this result in J2?
Nigel Southwood with 51 trailer moves on Monday 20th January

If so, and assuming the dates in column A are text and not actual dates, try adding this to the end of your formula.
=INDEX(B18:B3001,MATCH(MAX(AB18:AB3001),AB18:AB3001,FALSE),)&" with "&MAX(AB18:AB3001)&" trailer moves on "&INDEX(A18:A3001,MATCH(MAX(AB18:AB3001),AB18:AB3001,FALSE),)


However, you still haven't addressed the question I have asked twice already

Isn't it 'possible' for the maximum value in column AB to occur on more than one row of column AB?

For example, in that sample file, change X28 and Y28 to 6. Then ..

Nigel Southwood with 51 trailer moves on Monday 20th January
and
Ollie Calcott with 51 trailer moves on Monday 6th January

What result(s) do you want in that circumstance?

 

[MrNoobster]

Hi Peter,


That's done it, thank you so much!

Sorry for not answering your question twice, i was just concentrating on the current problem rather than thinking ahead to the next. Yes it is indeed possible for 2 or more people to have the same result.

The result I would like is exactly how you've portrayed it here

Nigel Southwood with 51 trailer moves on Monday 20th January
and
Ollie Calcott with 51 trailer moves on Monday 6th January

However again, I'm not sure how I would go about this. I don't understand your reference to changing X28 and Y28 to 6. Could you explain this please?


Many thanks

 

[Peter_SSs]

The result I would like is exactly how you've portrayed it here

Excel 2010 does not lend itself to a process like that if you wanted multiple results in a single cell. We can do it if it is okay to produce each of the multiple results in its own cell. Is that acceptable?

I don't understand your reference to changing X28 and Y28 to 6. Could you explain this please?

I was just suggesting physically changing those 2 cells on the sample sheet (I've shown them yellow). That way, Ollie Calcott also ended up with 51 moves for the shift. So I was just forcing an example where the maximum value in AB occurred twice (blue cells).

Book1
	A	B	C	D	E	F	G	H	I	J													W	X	Y	Z	AA	AB
28	Monday 6th January	Ollie Calcott	22:00 - 06:00	14	0	2	6	4	2															6	6	4	7	51
60	Monday 20th January	Nigel Southwood	16:00 - 03:00	4	9	7																	5	10	7	2	0	51
High Score on Date (2)

Cell Formulas
Range	Formula
AB28, AB60	AB28	=SUM(D28:AA28)

 

[MrNoobster]

Peter, you've been incredibly helpful and I can't thank you or this forum enough. Thank you so much for all your time and effort with this