Sorting Goal Times to correct score

[RedPed]

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

 

[jasonb75]

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]

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 Minutes	Away Goal Minutes
4;20;33;88;	12;30;66;

Hope this helps and appreciate any advice possible

 

[jasonb75]

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]

ok, thanks for the reply

 

[tusharm]

Given the small amount of data, I'd reorganize it by hand and then use analyze it.

Book1
	B	C	D
2	Home Goal Minutes	Away Goal Minutes
3	4;20;33;88;	12;30;66;
4
5	4		1-0
6		12	1-1
7	20		2-1
8		30	2-2
9	33		3-2
10		66	3-3
11	88		4-3
Sheet2

Cell Formulas
Range	Formula
D5:D11	D5	=COUNT($B$5:B5)&"-"&COUNT($C$5:C5)

 

[jasonb75]

First attempt,

Book1.xlsx
	A	B	C	D
1	Home Goal Minutes	Away Goal Minutes	Combined	Result
2	4;20;33;88;	12;30;66;
3	4	12	4	1-0
4	20	30	12	1-1
5	33	66	20	2-1
6	88		30	2-2
7			33	3-2
8			66	3-3
9			88	4-3
10
Sheet2

Cell Formulas
Range	Formula
A3:B10	A3	=IFERROR(--TRIM(MID(SUBSTITUTE(A$2,";",REPT(" ",LEN(A$2))),(ROWS(A$3:A3)-1)*LEN(A$2)+1,LEN(A$2))),"")
C3:C10	C3	=IFERROR(SMALL($A$3:$B$10,ROWS(C$3:C3)),"")
D3:D10	D3	=IF(C3="","",COUNTIF($A$3:$A$12,"<="&C3)&"-"&COUNTIF($B$3:$B$12,"<="&C3))