Sorting Goal Times to correct score

RedPed

New Member
Joined
Jul 10, 2015
Messages
21
Office Version
  1. 2016
Platform
  1. Windows
I am hoping someone may be able to help on the following.

I have football goal time data for a football match that I would like to use to be able to see how the score progress during the match.

e.g. I have Home Goal Minutes in Cell A 4;20;33;88 and Away Goal Minutes in Cell B 12;30;66

This match finished 4-3 and I am trying to find a way of using this data to show how the score progressed during the match.

So I would want to see the result

1-0
1-1
2-2
2-2
3-2
3-3
4-3

Any help or advice appreciated
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,414
Office Version
  1. 365
Platform
  1. Windows
The way that your post reads is that the cells specified contain a time but no other information, so how would you differentiate between goals and times for other events during the match?

A data sample posted with XL2BB (not a screen capture) would be useful so that we have an accurate representation of your data to test any formulas.

It would also be helpful if you would update your user profile to show which version of excel you are using so that we don't suggest things that are not in your version.
 

RedPed

New Member
Joined
Jul 10, 2015
Messages
21
Office Version
  1. 2016
Platform
  1. Windows
The way that your post reads is that the cells specified contain a time but no other information, so how would you differentiate between goals and times for other events during the match?

A data sample posted with XL2BB (not a screen capture) would be useful so that we have an accurate representation of your data to test any formulas.

It would also be helpful if you would update your user profile to show which version of excel you are using so that we don't suggest things that are not in your version.
Hi Jason, apologies.

I am using Excel 2016

The times are all goal times and are not relating to any other events.

Home Goal MinutesAway Goal Minutes
4;20;33;88;12;30;66;


Hope this helps and appreciate any advice possible
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,414
Office Version
  1. 365
Platform
  1. Windows
Now I follow, I read your first post as A4 having the time of the first home goal, A20 as the time of the second, etc. With the other cells in between them having times for other events.

There is nothing coming immediately to mind that will work, I'll give it some thought but I don't think that it will be possible with formulas.
 

RedPed

New Member
Joined
Jul 10, 2015
Messages
21
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

ok, thanks for the reply
 

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,028
Given the small amount of data, I'd reorganize it by hand and then use analyze it.

Book1
BCD
2Home Goal MinutesAway Goal Minutes
34;20;33;88;12;30;66;
4
541-0
6121-1
7202-1
8302-2
9333-2
10663-3
11884-3
Sheet2
Cell Formulas
RangeFormula
D5:D11D5=COUNT($B$5:B5)&"-"&COUNT($C$5:C5)
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,414
Office Version
  1. 365
Platform
  1. Windows
First attempt,
Book1.xlsx
ABCD
1Home Goal MinutesAway Goal MinutesCombinedResult
24;20;33;88;12;30;66;
341241-0
42030121-1
53366202-1
688 302-2
7  333-2
8  663-3
9  884-3
10    
Sheet2
Cell Formulas
RangeFormula
A3:B10A3=IFERROR(--TRIM(MID(SUBSTITUTE(A$2,";",REPT(" ",LEN(A$2))),(ROWS(A$3:A3)-1)*LEN(A$2)+1,LEN(A$2))),"")
C3:C10C3=IFERROR(SMALL($A$3:$B$10,ROWS(C$3:C3)),"")
D3:D10D3=IF(C3="","",COUNTIF($A$3:$A$12,"<="&C3)&"-"&COUNTIF($B$3:$B$12,"<="&C3))
 

Watch MrExcel Video

Forum statistics

Threads
1,127,107
Messages
5,622,782
Members
415,927
Latest member
vedasinternational

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
Top