Help with formula

Donjayok

New Member
Joined
Dec 11, 2019
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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)
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

mabbutt

Board Regular
Joined
Oct 4, 2014
Messages
71
Hi Donjayok,

Welcome to the forum.

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

Donjayok

New Member
Joined
Dec 11, 2019
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,651
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Make sure you close any file explorer windows when using the add-in.
 

Donjayok

New Member
Joined
Dec 11, 2019
Messages
4
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Nice one Rory, that worked!

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

Book1
ABCDEFGHIJKLMN
1DateGameVersusVenueResultW/L/DGoalsGoal StreakMan Of The MatchScorersGames Played12
218/08/2019Jubilee Cup Group StageBroughty UtdEsplanade1-6W00Kayne ToshKayne Tosh(3), Tyler Smith(1), Charlie Bonella(1), Josh Myszor(1)Won11
325/08/2019Jubilee Cup Group StageDundee ThistleClaypotts1-12W61Kyle JoinerKyle Joiner(6), James Breen(2), Kayne Tosh(1), Harley Drinnan(1), Brayden Cumming(1), Josh Myszor(1)Lost1
428/08/2019Jubilee Cup Group StageDundee SportingGlamis Road0-10W62Kyle JoinerKyle Joiner(6), Kayne Tosh(1), James Breen(1), Tyler Smith(1), Chris Laing(1)Drawn0
501/09/2019Jubilee Cup Group StageNewtyleCharlotte Street3-2W23Harley DrinnanKyle Joiner(2), Tyler Smith(1)Win Percentage92%
603/09/2019Jubilee Cup Group StageDundee CelticCharlotte Street7-2W24Dylan CartlidgeKyle Joiner(2), James Breen(2), Tyler Smith(1), Kayne Tosh(1), Josh Myszor(1)Loss Percentage8%
722nd September 2019Scottish Cup 2nd RoundJeanfield SwiftsCharlotte Street3-6L15Kyle Joiner(1), Josh Myszor(2)Draw Percentage0%
813th October 2019Café Royale LeagueNewtyleNewtyle Park2-4W16Dylan CartlidgeKyle Joiner(1), Kayne Tosh(1), Josh Myszor(1), Findlay Christie(1)Total Team Goals For76
920th October 2019Café Royale LeagueKirrie ThistleMartin Park2-6W27Dylan CartlidgeKyle Joiner(2), Tyler Smith(1), Kyle McNamee(1), James Breen(1), Brayden Cumming(1)Total Team Goals Against28
1027th October 2019East Region Cup 2nd RoundEast Fife CFCCharlotte Street5-1W38Brayden CummingKyle Joiner(3), Kayne Tosh(1), Brayden Cumming(1)Goal Difference48
1110th November 2019Jubilee Cup Quarter FinalKirrie ThistleCharlotte Street10-5W19Kayne ToshKyle Joiner(1), Kayne Tosh(2), Josh Myszor(3), Brayden Cumming(1), James Breen(2), Dylan Cartlidge(1)% of Team goals scored by Kyle37%
1217th November 2019Café Royale LeagueFerry AthleticDawson Park2-4W110Kyle McNameeKyle Joiner(1), Josh Myszor(2), Theo Brown(1)Number of Goals scored by Kyle28
1301/12/2019Jubilee Cup Semi FinalA.C.S.C.Charlotte Street6-4W311James BreenKyle Joiner(3), Brayden Cumming(1), Dylan Kane(1), Josh Myszor(1)Total Goals For Season (inc Friendlies)40
1415th December 2019Café Royale LeagueDW YellowsCharlotte Street-Average Goals Per Game2.33
15-Most goals scored in a game6
16-Current Goal Streak (in games)11
17-Longest Goal Streak (in games)11
Dundee West Reds 07 Results
Cell Formulas
RangeFormula
J2:J13J2=IF(I2>0,J1+1,0)
N1N1=COUNTA($H:$H)-1
N2N2=COUNTIF($H:$H,"W")
N3N3=COUNTIF($H:$H,"L")
N4N4=COUNTIF($H:$H,"D")
N5N5=COUNTIF(H:H,"W")/N1
N6N6=COUNTIF(H:H,"L")/N1
N7N7=COUNTIF(H:H,"D")/N1
N8N8=SUMIF(D:D,"Charlotte Street",E:E)+SUMIF(D:D,"<>Charlotte Street",G:G)
N9N9=SUMIF(D:D,"Charlotte Street",G:G)+SUMIF(D:D,"<>Charlotte Street",E:E)
N10N10=IF(N8-N9>0,"+"&N8-N9,"-"&N8-N9)
N11N11=N12/N8
N12N12=SUM(I:I)
N13N13=SUM(N12,Friendlies!L11)
N14N14=AVERAGE(I:I)
N15N15=LARGE(I:I,1)
N16N16=LOOKUP(2,1/(ISNUMBER(J1:J100)),J1:J100)
N17N17=LARGE(J:J,1)


Book1
BCDEF
1RankPlayerGoals ScoredCurrent RunLongest Run of Games Scoring
21Kyle Joiner
32Josh Myszor
43Kayne Tosh
54James Breen
65Brayden Cumming
76Tyler Smith
87Dylan Kane
98Theo Brown
109Dylan Cartlidge
1110Kyle McNamee
1211Kyle McNamee
1312Chris Laing
1413Harley Drinnan
1514Charlie Bonella
1615Lennon Harper
1716Evan Young
Top Scorers
Cell Formulas
RangeFormula
C2:C10, C12:C17C2=IFERROR(HLOOKUP($A2,Players!$2:$6,3,0),"")
 

mabbutt

Board Regular
Joined
Oct 4, 2014
Messages
71
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

New Member
Joined
Dec 11, 2019
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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! :)

Cell Formulas
RangeFormula
E1E1=0.0001
F1, N1:T1F1=E1+0.0001
G1G1=L1+0.0001
H1, J1H1=I1+0.0001
I1, M1I1=F1+0.0001
K1:L1K1=G1+0.0001
E2:T2E2=E5+E1
E3:T3E3=E6+E1
E5:T5E5=SUM(E7:E51)
E6:T6E6=COUNTIF('Dundee West Reds 07 Results'!$K:$K,E$4)
E7:T13, E17:T18, E14:T16E7=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:C16C7=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:D16D7=SUM(E7:AB7)
B7:B13, B17:B18, B14:B16B7='Dundee West Reds 07 Results'!C2
A7:A15, A17:A18A7='Dundee West Reds 07 Results'!A2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D7:D18Cell Value<>$C7textNO


Cell Formulas
RangeFormula
C2:C10, C12:C17C2=IFERROR(HLOOKUP($A2,Players!$2:$6,3,0),"")
D2:D11, D12:D17D2=IFERROR(HLOOKUP($A2,Players!$2:$6,4,0),"")
A2:A17A2=IFERROR(LARGE(Players!$2:$2,$B2),"")
 

mabbutt

Board Regular
Joined
Oct 4, 2014
Messages
71
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,084
Messages
5,545,863
Members
410,711
Latest member
Josh324
Top