Help with formula

[Donjayok]

Hey guys,

First post in the forum and a relative newbie to MS excel (so go easy please! ) and this is way above my station!

I have created a spreadsheet for my sons football team, recording all results etc but need help recording the goal scorers information.

Below is the goal scorers from each game (the list will get bigger as games are played). The format of this I would prefer to keep the same, as it ties in with work that someone else has done.

What I want to do is...

1. Extract each players name from a sheet called Results, to form a list in another sheet called Goalscorers.

2. Calculate the total the number of goals scored for each player and add that to a column in the Goalscorers spreadsheet with the player with the most at the top of the list.

3. Calculate a current scoring streak for each player and add that to a column in the Goalscorers spreadsheet.

4, Calculate a highest scoring streak for each player and add that to a column in the Goalscorers spreadsheet.

5. Be able to add scorers as the games are played into the results spreadsheet and they automatically add the goals and calculate streaks on the goalscorers spreadsheet.

Many thanks in advance for any help. It will be very much appreciated if anyone can help!

Kayne Tosh(3), Tyler Smith(1), Charlie Bonella(1), Josh Myszor(1)

Kyle Joiner(6), James Breen(2), Kayne Tosh(1), Harley Drinnan(1), Brayden Cumming(1), Josh Myszor(1)

Kyle Joiner(6), Kayne Tosh(1), James Breen(1), Tyler Smith(1), Chris Laing(1)

Kyle Joiner(2), Tyler Smith(1)

Kyle Joiner(2), James Breen(2), Tyler Smith(1), Kayne Tosh(1), Josh Myszor(1)

Kyle Joiner(1), Josh Myszor(2)

Kyle Joiner(1), Kayne Tosh(1), Josh Myszor(1), Findlay Christie(1)

Kyle Joiner(2), Tyler Smith(1), Kyle McNamee(1), James Breen(1), Brayden Cumming(1)

Kyle Joiner(3), Kayne Tosh(1), Brayden Cumming(1)

Kyle Joiner(1), Kayne Tosh(2), Josh Myszor(3), Brayden Cumming(1), James Breen(2), Dylan Cartlidge(1)

Kyle Joiner(1), Josh Myszor(2), Theo Brown(1)

Kyle Joiner(3), Dylan Kane(1), Brayden Cumming(1), Josh Myszor(1)

 

[mabbutt]

Hi Donjayok,

Welcome to the forum.

Can you post the spreadsheet that you have created thus far?

 

[Donjayok]

Thanks for the welcome mabbutt!

￿￿

Tried to post the spreadsheet using the capture range addin and can only get the above. Do you know what is wrong or is there another way to post it?

 

[RoryA]

Make sure you close any file explorer windows when using the add-in.

 

[Donjayok]

Nice one Rory, that worked!

ok, first is the Results sheet, then the goal scorers sheet... Thanks!

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Date	Game	Versus	Venue	Result			W/L/D	Goals	Goal Streak	Man Of The Match	Scorers	Games Played	12
2	18/08/2019	Jubilee Cup Group Stage	Broughty Utd	Esplanade	1	-	6	W	0	0	Kayne Tosh	Kayne Tosh(3), Tyler Smith(1), Charlie Bonella(1), Josh Myszor(1)	Won	11
3	25/08/2019	Jubilee Cup Group Stage	Dundee Thistle	Claypotts	1	-	12	W	6	1	Kyle Joiner	Kyle Joiner(6), James Breen(2), Kayne Tosh(1), Harley Drinnan(1), Brayden Cumming(1), Josh Myszor(1)	Lost	1
4	28/08/2019	Jubilee Cup Group Stage	Dundee Sporting	Glamis Road	0	-	10	W	6	2	Kyle Joiner	Kyle Joiner(6), Kayne Tosh(1), James Breen(1), Tyler Smith(1), Chris Laing(1)	Drawn	0
5	01/09/2019	Jubilee Cup Group Stage	Newtyle	Charlotte Street	3	-	2	W	2	3	Harley Drinnan	Kyle Joiner(2), Tyler Smith(1)	Win Percentage	92%
6	03/09/2019	Jubilee Cup Group Stage	Dundee Celtic	Charlotte Street	7	-	2	W	2	4	Dylan Cartlidge	Kyle Joiner(2), James Breen(2), Tyler Smith(1), Kayne Tosh(1), Josh Myszor(1)	Loss Percentage	8%
7	22nd September 2019	Scottish Cup 2nd Round	Jeanfield Swifts	Charlotte Street	3	-	6	L	1	5		Kyle Joiner(1), Josh Myszor(2)	Draw Percentage	0%
8	13th October 2019	Café Royale League	Newtyle	Newtyle Park	2	-	4	W	1	6	Dylan Cartlidge	Kyle Joiner(1), Kayne Tosh(1), Josh Myszor(1), Findlay Christie(1)	Total Team Goals For	76
9	20th October 2019	Café Royale League	Kirrie Thistle	Martin Park	2	-	6	W	2	7	Dylan Cartlidge	Kyle Joiner(2), Tyler Smith(1), Kyle McNamee(1), James Breen(1), Brayden Cumming(1)	Total Team Goals Against	28
10	27th October 2019	East Region Cup 2nd Round	East Fife CFC	Charlotte Street	5	-	1	W	3	8	Brayden Cumming	Kyle Joiner(3), Kayne Tosh(1), Brayden Cumming(1)	Goal Difference	48
11	10th November 2019	Jubilee Cup Quarter Final	Kirrie Thistle	Charlotte Street	10	-	5	W	1	9	Kayne Tosh	Kyle Joiner(1), Kayne Tosh(2), Josh Myszor(3), Brayden Cumming(1), James Breen(2), Dylan Cartlidge(1)	% of Team goals scored by Kyle	37%
12	17th November 2019	Café Royale League	Ferry Athletic	Dawson Park	2	-	4	W	1	10	Kyle McNamee	Kyle Joiner(1), Josh Myszor(2), Theo Brown(1)	Number of Goals scored by Kyle	28
13	01/12/2019	Jubilee Cup Semi Final	A.C.S.C.	Charlotte Street	6	-	4	W	3	11	James Breen	Kyle Joiner(3), Brayden Cumming(1), Dylan Kane(1), Josh Myszor(1)	Total Goals For Season (inc Friendlies)	40
14	15th December 2019	Café Royale League	DW Yellows	Charlotte Street		-							Average Goals Per Game	2.33
15						-							Most goals scored in a game	6
16						-							Current Goal Streak (in games)	11
17						-							Longest Goal Streak (in games)	11
Dundee West Reds 07 Results

Cell Formulas
Range	Formula
J2:J13	J2	=IF(I2>0,J1+1,0)
N1	N1	=COUNTA($H:$H)-1
N2	N2	=COUNTIF($H:$H,"W")
N3	N3	=COUNTIF($H:$H,"L")
N4	N4	=COUNTIF($H:$H,"D")
N5	N5	=COUNTIF(H:H,"W")/N1
N6	N6	=COUNTIF(H:H,"L")/N1
N7	N7	=COUNTIF(H:H,"D")/N1
N8	N8	=SUMIF(D:D,"Charlotte Street",E:E)+SUMIF(D:D,"<>Charlotte Street",G:G)
N9	N9	=SUMIF(D:D,"Charlotte Street",G:G)+SUMIF(D:D,"<>Charlotte Street",E:E)
N10	N10	=IF(N8-N9>0,"+"&N8-N9,"-"&N8-N9)
N11	N11	=N12/N8
N12	N12	=SUM(I:I)
N13	N13	=SUM(N12,Friendlies!L11)
N14	N14	=AVERAGE(I:I)
N15	N15	=LARGE(I:I,1)
N16	N16	=LOOKUP(2,1/(ISNUMBER(J1:J100)),J1:J100)
N17	N17	=LARGE(J:J,1)

Book1
	B	C	D	E	F
1	Rank	Player	Goals Scored	Current Run	Longest Run of Games Scoring
2	1	Kyle Joiner
3	2	Josh Myszor
4	3	Kayne Tosh
5	4	James Breen
6	5	Brayden Cumming
7	6	Tyler Smith
8	7	Dylan Kane
9	8	Theo Brown
10	9	Dylan Cartlidge
11	10	Kyle McNamee
12	11	Kyle McNamee
13	12	Chris Laing
14	13	Harley Drinnan
15	14	Charlie Bonella
16	15	Lennon Harper
17	16	Evan Young
Top Scorers

Cell Formulas
Range	Formula
C2:C10, C12:C17	C2	=IFERROR(HLOOKUP($A2,Players!$2:$6,3,0),"")

 

[mabbutt]

The advice I would give you with limited experience is to redesign the spreadsheet as the way the data is entered and setup does not lend itself well to what you are trying to do.

Having said that you can try this...

A1 = Kyle Joiner(3), Brayden Cumming(1), Dylan Kane(1), Josh Myszor(1)
B2 = =MID(LEFT(A1,FIND("), Josh",A1)-1),FIND("Kane(",A1)+5,LEN(A1))

This will extract the value between the ( ) for the player Kane.

You can then use this theory and formula for all the goals scored column for each player but you will need to create a new range to save the goals for each individual match and then sum the totals for each player to get the running total.

You would need to follow strict input rules to ensure that the formulas did not break but as I said above, it is better to redesign the spreadsheet.

If you do not want to redesign the spreadsheet then this is a good starting point and I would also suggest looking at a VBA solution.

 

[Donjayok]

Many thanks for that Mabutt. I was speaking with a friend about this and he then mentioned it to his friend.

Next thing I know, his friend had made the following player spreadsheet. I have also added the top scorers spreadsheet.

Is this what you meant by redesigning?... Does this help?... I'm very much still learning and trying to understand all the code!

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1					0.0001	0.0002	0.0006	0.0004	0.0003	0.0008	0.0007	0.0005	0.0009	0.001	0.0011	0.0012	0.0013	0.0014	0.0015	0.0016
2				Goal Rank	28.0001	10.0002	8.0006	12.0004	5.0003	5.0008	1.0007	1.0005	1.0009	1.001	1.0011	1.0012	1.0013	0.0014	1.0015	0.0016
3				MoM Rank	2.0001	2.0002	1.0006	0.0004	0.0003	1.0008	1.0007	0.0005	0.0009	1.001	0.0011	3.0012	0.0013	0.0014	0.0015	0.0016
4				Player	Kyle Joiner	Kayne Tosh	James Breen	Josh Myszor	Tyler Smith	Brayden Cumming	Harley Drinnan	Charlie Bonella	Chris Laing	Kyle McNamee	Findlay Christie	Dylan Cartlidge	Theo Brown	Evan Young	Dylan Kane	Lennon Harper
5				Goals>	28	10	8	12	5	5	1	1	1	1	1	1	1	0	1	0
6	Date	Versus		MOM>	2	2	1	0	0	1	1	0	0	1	0	3	0	0	0	0
7	18/08/2019	Broughty Utd	6	6	0	3	0	1	1	0	0	1	0	0	0	0	0	0	0	0
8	25/08/2019	Dundee Thistle	12	12	6	1	2	1	0	1	1	0	0	0	0	0	0	0	0	0
9	28/08/2019	Dundee Sporting	10	10	6	1	1	0	1	0	0	0	1	0	0	0	0	0	0	0
10	01/09/2019	Newtyle	3	3	2	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0
11	03/09/2019	Dundee Celtic	7	7	2	1	2	1	1	0	0	0	0	0	0	0	0	0	0	0
12	22nd September 2019	Jeanfield Swifts	3	3	1	0	0	2	0	0	0	0	0	0	0	0	0	0	0	0
13	13th October 2019	Newtyle	0	0	1	1	0	1	0	0	0	0	0	0	1	0	0	0	0	0
14	20th October 2019	Kirrie Thistle	6	6	2	0	1	0	1	1	0	0	0	1	0	0	0	0	0	0
15	27th October 2019	East Fife CFC	5	5	3	1	0	0	0	1	0	0	0	0	0	0	0	0	0	0
16	10th November 2019	Kirrie Thistle	10	10	1	2	2	3	0	1	0	0	0	0	0	1	0	0	0	0
17	17th November 2019	Ferry Athletic	4	4	1	0	0	2	0	0	0	0	0	0	0	0	1	0	0	0
18	01/12/2019	A.C.S.C.	6	6	3	0	0	1	0	1	0	0	0	0	0	0	0	0	1	0
Players

Cell Formulas
Range	Formula
E1	E1	=0.0001
F1, N1:T1	F1	=E1+0.0001
G1	G1	=L1+0.0001
H1, J1	H1	=I1+0.0001
I1, M1	I1	=F1+0.0001
K1:L1	K1	=G1+0.0001
E2:T2	E2	=E5+E1
E3:T3	E3	=E6+E1
E5:T5	E5	=SUM(E7:E51)
E6:T6	E6	=COUNTIF('Dundee West Reds 07 Results'!$K:$K,E$4)
E7:T13, E17:T18, E14:T16	E7	=IFERROR(VALUE(MID('Dundee West Reds 07 Results'!$L2,FIND(E$4,'Dundee West Reds 07 Results'!$L2,1)+LEN(E$4)+1,1)),0)
C7:C12, C17:C18, C14:C16	C7	=IF('Dundee West Reds 07 Results'!D2="Charlotte Street",'Dundee West Reds 07 Results'!E2,'Dundee West Reds 07 Results'!G2)
D7:D12, D17:D18, D14:D16	D7	=SUM(E7:AB7)
B7:B13, B17:B18, B14:B16	B7	='Dundee West Reds 07 Results'!C2
A7:A15, A17:A18	A7	='Dundee West Reds 07 Results'!A2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D7:D18	Cell Value	<>$C7	text	NO

Book1
	A	B	C	D	E	F	G
1		Rank	Player	Goals Scored	Most Goals Scored In A Game	Current Goal Streak	Longest Run of Games Scoring
2	28.0001	1	Kyle Joiner	28
3	12.0004	2	Josh Myszor	12
4	10.0002	3	Kayne Tosh	10
5	8.0006	4	James Breen	8
6	5.0008	5	Brayden Cumming	5
7	5.0003	6	Tyler Smith	5
8	1.0015	7	Dylan Kane	1
9	1.0013	8	Theo Brown	1
10	1.0012	9	Dylan Cartlidge	1
11	1.0011	10	Finlay Christie	1
12	1.001	11	Kyle McNamee	1
13	1.0009	12	Chris Laing	1
14	1.0007	13	Harley Drinnan	1
15	1.0005	14	Charlie Bonella	1
16	0.0016	15	Lennon Harper	0
17	0.0014	16	Evan Young	0
Top Scorers

Cell Formulas
Range	Formula
C2:C10, C12:C17	C2	=IFERROR(HLOOKUP($A2,Players!$2:$6,3,0),"")
D2:D11, D12:D17	D2	=IFERROR(HLOOKUP($A2,Players!$2:$6,4,0),"")
A2:A17	A2	=IFERROR(LARGE(Players!$2:$2,$B2),"")

 

[mabbutt]

I'm a little confused as to what you are now looking for.

Maybe speak to the friend that amended the spreadsheet because it is still not really helping with the data that you are looking to have as an output.