How to organise data sequentially in groups

[excelbeginner344]

hello,

Just to give a bit of background - i have a college project to do on game states involving football (/ soccer).

I have been given some data and for a season where the home & away goals sit in a binary string of 90 chars. 0= no goal / 1 = goal (the position signifying the minute scored). I have been able to extract the minutes the goals are scored & i have a grid per attached (H S = Home Scored # of goals / A S = Away Scored # of goals).

The difficulty im having is organsing the data into the score sequentially. I need to record the score in order of time (eg 1-0 / 19 minutes, 1-1 / 38 minutes etc)

Its easy where its 0-0, or 1-0, or 0-1. the difficulty im having is to build a picture where there are multiple goals scored.

For example - per the image for Queens Park vs Stoke; i need to reference the code to return the following:

0-1 / 27
0-2 / 31
1-2 / 34
2-2 / 38
3-2 / 71
4-2 / 90

Would be massively grateful if anyone has any ideas. Have tried to use COUNTIF but cant organise it how i need to & have scoured the web but cant see any similar problems that have been solved.

TIA !

 

[Fluff]

How about

ZFluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1			HS1	HS2	HS3	HS4	HS5	HS6	HS7	HS8	HS9	HS10	AS1	AS2	AS3	AS4	AS5	AS6	AS7	AS8	AS9	AS10
2
3
4			34	38	71	90							27	31	76
5
6
7
8
9		27	0-1 / 27
10		31	0-2 / 31
11		34	1-2 / 34
12		38	2-2 / 38
13		71	3-2 / 71
14		76	3-3 / 76
15		90	4-3 / 90
16
17
Main

Cell Formulas
Range	Formula
B9:B16	B9	=IFERROR(AGGREGATE(15,6,$C$4:$V$4/($C$4:$V$4<>""),ROWS(C$9:C9)),"")
C9:C16	C9	=IF(B9="","",COUNTIF($C$4:$L$4,"<="&B9)&"-"&COUNTIF($M$4:$V$4,"<="&B9)&" / " &B9)

 

[DanteAmor]

Hi and welcome to MrExcel!

You did not comment on where you want the result or what data you have to perform the search.
With the following array formula, if you put the home and away teams in cells X1 and Y1 you will get the results of those teams in cells X2 down.

Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

varios 17feb2020.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1	Home	Away	H S 1	H S 2	H S 3	H S 4	H S 5	H S 6	H S 7	H S 8	H S 9	H S 10	A S 1	A S 2	A S 3	A S 4	A S 5	A S 6	A S 7	A S 8	A S 9	A S 10		Queens Park	Stoke
2	Norw	Live											78											0-1 / 27
3	Sout	Burn	18										2	60										0-2 / 31
4	Midd	Luto											17											1-2 / 34
5	West	Nott	37	65									45	90										2-2 / 38
6	Shef	Read											21	72	90									3-2 / 71
7	Queens Park	Stoke	34	38	71	90							27	31										4-2 / 90
8	Pres	Mill											78
sheet

Cell Formulas
Range	Formula
X2:X8	X2	=IFERROR(COUNTIF(INDEX($C$1:$L$8,MATCH($X$1&"|"&$Y$1,$A$1:$A$8&"|"&$B$1:$B$8,0),0),"<="& SMALL(INDEX($C$1:$V$8,MATCH($X$1&"|"&$Y$1,$A$1:$A$8&"|"&$B$1:$B$8,0),0),ROWS($Y$1:Y1)))&"-"& COUNTIF(INDEX($M$1:$V$8,MATCH($X$1&"|"&$Y$1,$A$1:$A$8&"|"&$B$1:$B$8,0),0),"<="& SMALL(INDEX($C$1:$V$8,MATCH($X$1&"|"&$Y$1,$A$1:$A$8&"|"&$B$1:$B$8,0),0),ROWS($Y$1:Y1)))&" / "& SMALL(INDEX($C$1:$V$8,MATCH($X$1&"|"&$Y$1,$A$1:$A$8&"|"&$B$1:$B$8,0),0),ROWS($Y$1:Y1)),"")
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[excelbeginner344]

cheers for your prompt replys.

fluff - exactly what i am looking for (and i see using countif as well...), but dante you make a v valid point too re the format.

i was hoping to keep this information attached to the row (with the game data). is there anyway to convert the ROWS command to read horizontally ?

i can get it working as you have it set out, but unable to when i try and keep it within the row.

any ideas ?

 

[DanteAmor]

For the example of your image in post #4 it would be something like this:

varios 17feb2020.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
1	Home	Away	H S 1	H S 2	H S 3	H S 4	H S 5	H S 6	H S 7	H S 8	H S 9	H S 10	A S 1	A S 2	A S 3	A S 4	A S 5	A S 6	A S 7	A S 8	A S 9	A S 10	gl1	gl2	gl3	gl4	gl5	gl6	gl7	gl8	gl9	gl10
2	Norw	Live											78										78
3	Sout	Burn	18										2	60									2	18	60
4	Midd	Luto											17										17
5	West	Nott	37	65									45	90									37	45	65	90
6	Shef	Read											21	72	90								21	72	90
7	Queens Park	Stoke	34	38	71	90							27	31									27	31	34	38	71	90
8	Pres	Mill
Hoja2 (2)

Cell Formulas
Range	Formula
W2:AF8	W2	=IFERROR(SMALL($C2:$V2,COLUMNS($W$1:W$1)),"")

 

[DanteAmor]

Or if you prefer:

dante amor.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
1	Home	Away	H S 1	H S 2	H S 3	H S 4	H S 5	H S 6	H S 7	H S 8	H S 9	H S 10	A S 1	A S 2	A S 3	A S 4	A S 5	A S 6	A S 7	A S 8	A S 9	A S 10	gl1	gl2	gl3	gl4	gl5	gl6	gl7	gl8	gl9	gl10
2	Norw	Live											78										0-1 / 78
3	Sout	Burn	18										2	60									0-1 / 2	1-1 / 18	1-2 / 60
4	Midd	Luto											17										0-1 / 17
5	West	Nott	37	65									45	90									1-0 / 37	1-1 / 45	2-1 / 65	2-2 / 90
6	Shef	Read											21	72	90								0-1 / 21	0-2 / 72	0-3 / 90
7	Quee	Stok	34	38	71	90							27	31									0-1 / 27	0-2 / 31	1-2 / 34	2-2 / 38	3-2 / 71	4-2 / 90
8	Pres	Mill
DAM

Cell Formulas
Range	Formula
W2:AF8	W2	=IFERROR(COUNTIF($C2:$L2,"<="&SMALL($C2:$V2,COLUMNS($W$1:W$1)))&"-"&COUNTIF($M2:$V2,"<="&SMALL($C2:$V2,COLUMNS($W$1:W$1)))&" / "&SMALL($C2:$V2,COLUMNS($W$1:W$1)),"")

 

[excelbeginner344]

got it. sorry - must've missed the S of columnS when i was trying to transpose it.

excellent - thanks v much both !!

 

[DanteAmor]

Glad we could help & thanks for the feedback

 

[Fluff]

With my way

ZFluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE
1			HS1	HS2	HS3	HS4	HS5	HS6	HS7	HS8	HS9	HS10	AS1	AS2	AS3	AS4	AS5	AS6	AS7	AS8	AS9	AS10
2
3
4			34	38	71	90							27	31	76								0-1 / 27	0-2 / 31	1-2 / 34	2-2 / 38	3-2 / 71	3-3 / 76	4-3 / 90
5
6
Main

Cell Formulas
Range	Formula
W4:AE4	W4	=IFERROR(COUNTIF($C$4:$L$4,"<="&AGGREGATE(15,6,$C$4:$V$4/($C$4:$V$4<>""),COLUMNS($W4:W4)))&"-"&COUNTIF($M$4:$V$4,"<="&AGGREGATE(15,6,$C$4:$V$4/($C$4:$V$4<>""),COLUMNS($W4:W4)))&" / " &AGGREGATE(15,6,$C$4:$V$4/($C$4:$V$4<>""),COLUMNS($W4:W4)),"")