Breaking down Tennis score

I

indygo

Board Regular
Joined
Dec 2, 2013
Messages
126
Office Version
  1. 365
Platform
  1. Windows
hi all,

so I have this data in excel and I would like to simply count number of games and sets won by a player.
Any idea how we could make such calculations in columns C2,C3,D2,D3,E2,E3,F2,F3 ?
I've tried using split to columns, =Left and right but I can't split it nice and clean...

The problem is that Bob sometimes is listed as first and sometimes as second plus scoring system is not the easiest to work with.

In the first set Bob won in tie break vs Sally 7-6 with small notation of *8 (meaning the score was 10-8) but we don't count that so that's another thing to consider.

Any idea guys?

MatchScoreBob sets playedBob sets wonBob games playedBob games won
Bob - David6-2, 7-5222013
Sally - Bob7-68, 4-6,10-7314019
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
It's 6+2+7+5
I suspect that B2 should be 7-6,4-6,10-7
 
Upvote 0
Book1
ABCDEFGHI
1Requirement
2
3MatchScoreBob sets playedBob sets wonBob games playedBob games won
4Bob - David6-2, 7-5222013
5Sally - Bob7-6, 4-6, 10-7314019
6
7
8MatchWhere is BOBScoreBob sets playedBob sets won
9Bob - David16-2, 7-522
10Sally - Bob27-6,4-6,10-1131
11
12
Sheet1
Cell Formulas
RangeFormula
D9:D10D9=LEN(C9)-LEN(SUBSTITUTE(C9,",",""))+1
E9:E10E9=SUM(IF(SIGN(IF(B9=1,IFERROR(MID(TRIM(MID(SUBSTITUTE(B4,", ",REPT(" ",LEN(B4))),((ROW(INDIRECT("A1:A"&LEN(B4)))-ROW($A$1))*LEN(B4))+1,LEN(B4))),1,IFERROR(SEARCH("-",TRIM(MID(SUBSTITUTE(B4,", ",REPT(" ",LEN(B4))),((ROW(INDIRECT("A1:A"&LEN(B4)))-ROW($A$1))*LEN(B4))+1,LEN(B4)))),0)-1)+0,0)-IFERROR(MID(TRIM(MID(SUBSTITUTE(B4,", ",REPT(" ",LEN(B4))),((ROW(INDIRECT("A1:A"&LEN(B4)))-ROW($A$1))*LEN(B4))+1,LEN(B4))),SEARCH("-",TRIM(MID(SUBSTITUTE(B4,", ",REPT(" ",LEN(B4))),((ROW(INDIRECT("A1:A"&LEN(B4)))-ROW($A$1))*LEN(B4))+1,LEN(B4))))+1,4),0)+0,IFERROR(MID(TRIM(MID(SUBSTITUTE(B4,", ",REPT(" ",LEN(B4))),((ROW(INDIRECT("A1:A"&LEN(B4)))-ROW($A$1))*LEN(B4))+1,LEN(B4))),SEARCH("-",TRIM(MID(SUBSTITUTE(B4,", ",REPT(" ",LEN(B4))),((ROW(INDIRECT("A1:A"&LEN(B4)))-ROW($A$1))*LEN(B4))+1,LEN(B4))))+1,4),0)+0-IFERROR(MID(TRIM(MID(SUBSTITUTE(B4,", ",REPT(" ",LEN(B4))),((ROW(INDIRECT("A1:A"&LEN(B4)))-ROW($A$1))*LEN(B4))+1,LEN(B4))),1,IFERROR(SEARCH("-",TRIM(MID(SUBSTITUTE(B4,", ",REPT(" ",LEN(B4))),((ROW(INDIRECT("A1:A"&LEN(B4)))-ROW($A$1))*LEN(B4))+1,LEN(B4)))),0)-1)+0,0)))>=1,1,0))
B9:B10B9=IF(SEARCH("BOB",A9)<SEARCH("-",A9),1,2)



I have tried solving the first two criteria. I am figuring out to solvbe the other two
 
Upvote 0
Book1
ABCDEF
3MatchScoreBob sets playedBob sets wonBob games playedBob games won
4Bob - David6-2, 7-5222013
5Sally - Bob7-6, 4-6, 10-7314019
6
7
8MatchWhere is BOBScoreBob sets playedBob sets wonBob games Played
9Bob - David16-2, 7-522 20.00
10Sally - Bob27-6, 4-6, 10-1131 44.00
Sheet1
Cell Formulas
RangeFormula
D9:D10D9=LEN(C9)-LEN(SUBSTITUTE(C9,",",""))+1
E9:E10E9=SUM(IF(SIGN(IF(B9=1,IFERROR(MID(TRIM(MID(SUBSTITUTE(B4,", ",REPT(" ",LEN(B4))),((ROW(INDIRECT("A1:A"&LEN(B4)))-ROW($A$1))*LEN(B4))+1,LEN(B4))),1,IFERROR(SEARCH("-",TRIM(MID(SUBSTITUTE(B4,", ",REPT(" ",LEN(B4))),((ROW(INDIRECT("A1:A"&LEN(B4)))-ROW($A$1))*LEN(B4))+1,LEN(B4)))),0)-1)+0,0)-IFERROR(MID(TRIM(MID(SUBSTITUTE(B4,", ",REPT(" ",LEN(B4))),((ROW(INDIRECT("A1:A"&LEN(B4)))-ROW($A$1))*LEN(B4))+1,LEN(B4))),SEARCH("-",TRIM(MID(SUBSTITUTE(B4,", ",REPT(" ",LEN(B4))),((ROW(INDIRECT("A1:A"&LEN(B4)))-ROW($A$1))*LEN(B4))+1,LEN(B4))))+1,4),0)+0,IFERROR(MID(TRIM(MID(SUBSTITUTE(B4,", ",REPT(" ",LEN(B4))),((ROW(INDIRECT("A1:A"&LEN(B4)))-ROW($A$1))*LEN(B4))+1,LEN(B4))),SEARCH("-",TRIM(MID(SUBSTITUTE(B4,", ",REPT(" ",LEN(B4))),((ROW(INDIRECT("A1:A"&LEN(B4)))-ROW($A$1))*LEN(B4))+1,LEN(B4))))+1,4),0)+0-IFERROR(MID(TRIM(MID(SUBSTITUTE(B4,", ",REPT(" ",LEN(B4))),((ROW(INDIRECT("A1:A"&LEN(B4)))-ROW($A$1))*LEN(B4))+1,LEN(B4))),1,IFERROR(SEARCH("-",TRIM(MID(SUBSTITUTE(B4,", ",REPT(" ",LEN(B4))),((ROW(INDIRECT("A1:A"&LEN(B4)))-ROW($A$1))*LEN(B4))+1,LEN(B4)))),0)-1)+0,0)))>=1,1,0))
F9:F10F9=SUM(IFERROR(TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C9,"-","+"),", ","+"),"+",REPT(" ",LEN(SUBSTITUTE(SUBSTITUTE(C9,"-","+"),", ","+")))),(ROW(INDIRECT("A1:A"&LEN(SUBSTITUTE(SUBSTITUTE(C9,"-","+"),", ","+"))))-ROW($A$1))*LEN(SUBSTITUTE(SUBSTITUTE(C9,"-","+"),", ","+"))+1,LEN(SUBSTITUTE(SUBSTITUTE(C9,"-","+"),", ","+"))))+0,0))
B9:B10B9=IF(SEARCH("BOB",A9)<SEARCH("-",A9),1,2)
 
Upvote 0
CA_Punit said:
Book1
ABCDEF
3MatchScoreBob sets playedBob sets wonBob games playedBob games won
4Bob - David6-2, 7-5222013
5Sally - Bob7-6, 4-6, 10-7314019
6
7
8MatchWhere is BOBScoreBob sets playedBob sets wonBob games Played
9Bob - David16-2, 7-522 20.00
10Sally - Bob27-6, 4-6, 10-1131 44.00
Sheet1
Cell Formulas
RangeFormula
D9:D10D9=LEN(C9)-LEN(SUBSTITUTE(C9,",",""))+1
E9:E10E9=SUM(IF(SIGN(IF(B9=1,IFERROR(MID(TRIM(MID(SUBSTITUTE(B4,", ",REPT(" ",LEN(B4))),((ROW(INDIRECT("A1:A"&LEN(B4)))-ROW($A$1))*LEN(B4))+1,LEN(B4))),1,IFERROR(SEARCH("-",TRIM(MID(SUBSTITUTE(B4,", ",REPT(" ",LEN(B4))),((ROW(INDIRECT("A1:A"&LEN(B4)))-ROW($A$1))*LEN(B4))+1,LEN(B4)))),0)-1)+0,0)-IFERROR(MID(TRIM(MID(SUBSTITUTE(B4,", ",REPT(" ",LEN(B4))),((ROW(INDIRECT("A1:A"&LEN(B4)))-ROW($A$1))*LEN(B4))+1,LEN(B4))),SEARCH("-",TRIM(MID(SUBSTITUTE(B4,", ",REPT(" ",LEN(B4))),((ROW(INDIRECT("A1:A"&LEN(B4)))-ROW($A$1))*LEN(B4))+1,LEN(B4))))+1,4),0)+0,IFERROR(MID(TRIM(MID(SUBSTITUTE(B4,", ",REPT(" ",LEN(B4))),((ROW(INDIRECT("A1:A"&LEN(B4)))-ROW($A$1))*LEN(B4))+1,LEN(B4))),SEARCH("-",TRIM(MID(SUBSTITUTE(B4,", ",REPT(" ",LEN(B4))),((ROW(INDIRECT("A1:A"&LEN(B4)))-ROW($A$1))*LEN(B4))+1,LEN(B4))))+1,4),0)+0-IFERROR(MID(TRIM(MID(SUBSTITUTE(B4,", ",REPT(" ",LEN(B4))),((ROW(INDIRECT("A1:A"&LEN(B4)))-ROW($A$1))*LEN(B4))+1,LEN(B4))),1,IFERROR(SEARCH("-",TRIM(MID(SUBSTITUTE(B4,", ",REPT(" ",LEN(B4))),((ROW(INDIRECT("A1:A"&LEN(B4)))-ROW($A$1))*LEN(B4))+1,LEN(B4)))),0)-1)+0,0)))>=1,1,0))
F9:F10F9=SUM(IFERROR(TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C9,"-","+"),", ","+"),"+",REPT(" ",LEN(SUBSTITUTE(SUBSTITUTE(C9,"-","+"),", ","+")))),(ROW(INDIRECT("A1:A"&LEN(SUBSTITUTE(SUBSTITUTE(C9,"-","+"),", ","+"))))-ROW($A$1))*LEN(SUBSTITUTE(SUBSTITUTE(C9,"-","+"),", ","+"))+1,LEN(SUBSTITUTE(SUBSTITUTE(C9,"-","+"),", ","+"))))+0,0))
B9:B10B9=IF(SEARCH("BOB",A9)<SEARCH("-",A9),1,2)
Click to expand...


Thank you so much for this.
There are two issues - first one is that "Bob games won" is missing.
Second is related to those tie breaks with notations. Here's an example using your formula:

MatchScoreWhere is BobBob Sets PlayedBob Sets WonBob Games PlayedBob Games won
Michael - Bob7-68, 6-422185
Bob - Sally6-2, 7-512220

Bob - Sally is counting correctly but because Michael vs Bob had a tie break in set1 excel is taking into account that notation which should ignore. This affects Bob Sets won and Bob Games played. Whenever score in tennis is 7-6 it means that it ended up with 6-6 and there was a tie break to decide the winner of the set. Example on screenshot.
Probably the best solution would be to split score into separate cells and ignore any notations - any idea how to do this please?
 

Attachments

  • score example.JPG
    score example.JPG
    10 KB · Views: 12
Upvote 0
indygo said:
Whenever score in tennis is 7-6 it means that it ended up with 6-6 and there was a tie break to decide the winner of the set. Example on screenshot.
Click to expand...

How does 8 comes up. Is there any condition for 8 to be there. I thought it to be a typo error. (7-68)
 
Upvote 0
CA_Punit said:
How does 8 comes up. Is there any condition for 8 to be there. I thought it to be a typo error. (7-68)
Click to expand...
no it's not a typo, that's the database I have. I believe in original data 8 was superscripted
 
Upvote 0
indygo said:
Whenever score in tennis is 7-6 it means that it ended up with 6-6 and there was a tie break to decide the winner of the set. Example on screenshot.
Click to expand...

How many such combination can be there. if this only 1 condition than we can find and replace that value. It will be an easy process otherwise we need to go devise a formula for eeach of such conditions.
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question
Auto Scoring Matrix
Replies
4
Views
264
Eric W
Eric W
T
  • Question
cross country meet scoring
Replies
7
Views
150
Joe4
Joe4
JTL9161
  • Question
Looking for IF statement formula
3 4 5
Replies
41
Views
2K
JTL9161
JTL9161
JenniferMurphy
  • Question
Best implementation of scoring sheet for grandkids
2 3
Replies
21
Views
1K
JenniferMurphy
JenniferMurphy
M
  • Question
Recursive LAMBDA for progressive SMALL calculations
Replies
1
Views
102
macfuller
M

Forum statistics

Threads
1,215,430
Messages
6,124,852
Members
449,194
Latest member
HellScout

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top