Seeking advice on where to begin - Searching for team form football data

[widgewilliams]

Hi guys and gals.

At this stage Im not looking for a specific solution to a problem but rather ideas on how to approach something.

I have several spreadsheets of football data covering several seasons of results and odds.

eg:

<tbody>
[TD="class: xl66"]Date[/TD]
[TD="class: xl66"]HomeTeam[/TD]
[TD="class: xl66, width: 64"]AwayTeam[/TD]
[TD="class: xl66, width: 64"]Home Goals[/TD]
[TD="class: xl66, width: 64"]Away Goals[/TD]
[TD="class: xl66, width: 64"]Result[/TD]
[TD="class: xl66, width: 64"]Home Odds[/TD]
[TD="class: xl66, width: 64"]Draw Odds[/TD]
[TD="class: xl66, width: 64"]Away Odds[/TD]
[TD="class: xl66, width: 64"]H[/TD]
[TD="class: xl66, width: 64"]A[/TD]

[TD="class: xl66"]18/08/12[/TD]
[TD="class: xl66"]Arsenal[/TD]
[TD="class: xl66"]Sunderland[/TD]
[TD="class: xl66"]0[/TD]
[TD="class: xl66"]0[/TD]
[TD="class: xl66"]D[/TD]
[TD="class: xl66"]1.44[/TD]
[TD="class: xl66"]4.89[/TD]
[TD="class: xl66"]9.5[/TD]
[TD="class: xl66"]D[/TD]
[TD="class: xl66"]D[/TD]

[TD="class: xl66"]18/08/12[/TD]
[TD="class: xl66"]Fulham[/TD]
[TD="class: xl66"]Norwich[/TD]
[TD="class: xl66"]5[/TD]
[TD="class: xl66"]0[/TD]
[TD="class: xl66"]H[/TD]
[TD="class: xl66"]1.85[/TD]
[TD="class: xl66"]3.82[/TD]
[TD="class: xl66"]4.8[/TD]
[TD="class: xl66"]W[/TD]
[TD="class: xl66"]L[/TD]

[TD="class: xl66"]18/08/12[/TD]
[TD="class: xl66"]Newcastle[/TD]
[TD="class: xl66"]Tottenham[/TD]
[TD="class: xl66"]2[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]H[/TD]
[TD="class: xl66"]2.7[/TD]
[TD="class: xl66"]3.47[/TD]
[TD="class: xl66"]2.85[/TD]
[TD="class: xl66"]W[/TD]
[TD="class: xl66"]L[/TD]

[TD="class: xl66"]18/08/12[/TD]
[TD="class: xl66"]QPR[/TD]
[TD="class: xl66"]Swansea[/TD]
[TD="class: xl66"]0[/TD]
[TD="class: xl66"]5[/TD]
[TD="class: xl66"]A[/TD]
[TD="class: xl66"]2.1[/TD]
[TD="class: xl66"]3.55[/TD]
[TD="class: xl66"]4.2[/TD]
[TD="class: xl66"]L[/TD]
[TD="class: xl66"]W[/TD]

[TD="class: xl66"]18/08/12[/TD]
[TD="class: xl66"]Reading[/TD]
[TD="class: xl66"]Stoke[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]D[/TD]
[TD="class: xl66"]2.45[/TD]
[TD="class: xl66"]3.4[/TD]
[TD="class: xl66"]3.26[/TD]
[TD="class: xl66"]D[/TD]
[TD="class: xl66"]D[/TD]

[TD="class: xl66"]18/08/12[/TD]
[TD="class: xl66"]West Brom[/TD]
[TD="class: xl66"]Liverpool[/TD]
[TD="class: xl66"]3[/TD]
[TD="class: xl66"]0[/TD]
[TD="class: xl66"]H[/TD]
[TD="class: xl66"]4.38[/TD]
[TD="class: xl66"]3.61[/TD]
[TD="class: xl66"]2[/TD]
[TD="class: xl66"]W[/TD]
[TD="class: xl66"]L[/TD]

[TD="class: xl66"]18/08/12[/TD]
[TD="class: xl66"]West Ham[/TD]
[TD="class: xl66"]Aston Villa[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]0[/TD]
[TD="class: xl66"]H[/TD]
[TD="class: xl66"]2.3[/TD]
[TD="class: xl66"]3.46[/TD]
[TD="class: xl66"]3.51[/TD]
[TD="class: xl66"]W[/TD]
[TD="class: xl66"]L[/TD]

[TD="class: xl66"]19/08/12[/TD]
[TD="class: xl66"]Man City[/TD]
[TD="class: xl66"]Southampton[/TD]
[TD="class: xl66"]3[/TD]
[TD="class: xl66"]2[/TD]
[TD="class: xl66"]H[/TD]
[TD="class: xl66"]1.21[/TD]
[TD="class: xl66"]7.55[/TD]
[TD="class: xl66"]19.38[/TD]
[TD="class: xl66"]W[/TD]
[TD="class: xl66"]L[/TD]

[TD="class: xl66"]19/08/12[/TD]
[TD="class: xl66"]Wigan[/TD]
[TD="class: xl66"]Chelsea[/TD]
[TD="class: xl66"]0[/TD]
[TD="class: xl66"]2[/TD]
[TD="class: xl66"]A[/TD]
[TD="class: xl66"]6.75[/TD]
[TD="class: xl66"]4[/TD]
[TD="class: xl66"]1.67[/TD]
[TD="class: xl66"]L[/TD]
[TD="class: xl66"]W[/TD]

[TD="class: xl66"]20/08/12[/TD]
[TD="class: xl66"]Everton[/TD]
[TD="class: xl66"]Man United[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]0[/TD]
[TD="class: xl66"]H[/TD]
[TD="class: xl66"]4.45[/TD]
[TD="class: xl66"]3.71[/TD]
[TD="class: xl66"]1.91[/TD]
[TD="class: xl66"]W[/TD]
[TD="class: xl66"]L[/TD]

[TD="class: xl66"]22/08/12[/TD]
[TD="class: xl66"]Chelsea[/TD]
[TD="class: xl66"]Reading[/TD]
[TD="class: xl66"]4[/TD]
[TD="class: xl66"]2[/TD]
[TD="class: xl66"]H[/TD]
[TD="class: xl66"]1.29[/TD]
[TD="class: xl66"]6.2[/TD]
[TD="class: xl66"]14[/TD]
[TD="class: xl66"]W[/TD]
[TD="class: xl66"]L[/TD]

[TD="class: xl66"]25/08/12[/TD]
[TD="class: xl66"]Aston Villa[/TD]
[TD="class: xl66"]Everton[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]3[/TD]
[TD="class: xl66"]A[/TD]
[TD="class: xl66"]3.25[/TD]
[TD="class: xl66"]3.4[/TD]
[TD="class: xl66"]2.6[/TD]
[TD="class: xl66"]L[/TD]
[TD="class: xl66"]W[/TD]

[TD="class: xl66"]25/08/12[/TD]
[TD="class: xl66"]Chelsea[/TD]
[TD="class: xl66"]Newcastle[/TD]
[TD="class: xl66"]2[/TD]
[TD="class: xl66"]0[/TD]
[TD="class: xl66"]H[/TD]
[TD="class: xl66"]1.53[/TD]
[TD="class: xl66"]4.55[/TD]
[TD="class: xl66"]7[/TD]
[TD="class: xl66"]W[/TD]
[TD="class: xl66"]L[/TD]

[TD="class: xl66"]25/08/12[/TD]
[TD="class: xl66"]Man United[/TD]
[TD="class: xl66"]Fulham[/TD]
[TD="class: xl66"]3[/TD]
[TD="class: xl66"]2[/TD]
[TD="class: xl66"]H[/TD]
[TD="class: xl66"]1.33[/TD]
[TD="class: xl66"]5.52[/TD]
[TD="class: xl66"]12[/TD]
[TD="class: xl66"]W[/TD]
[TD="class: xl66"]L[/TD]

[TD="class: xl66"]25/08/12[/TD]
[TD="class: xl66"]Norwich[/TD]
[TD="class: xl66"]QPR[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]D[/TD]
[TD="class: xl66"]2.38[/TD]
[TD="class: xl66"]3.52[/TD]
[TD="class: xl66"]3.25[/TD]
[TD="class: xl66"]D[/TD]
[TD="class: xl66"]D[/TD]

[TD="class: xl66"]25/08/12[/TD]
[TD="class: xl66"]Southampton[/TD]
[TD="class: xl66"]Wigan[/TD]
[TD="class: xl66"]0[/TD]
[TD="class: xl66"]2[/TD]
[TD="class: xl66"]A[/TD]
[TD="class: xl66"]2.2[/TD]
[TD="class: xl66"]3.53[/TD]
[TD="class: xl66"]3.65[/TD]
[TD="class: xl66"]L[/TD]
[TD="class: xl66"]W[/TD]

[TD="class: xl66"]25/08/12[/TD]
[TD="class: xl66"]Swansea[/TD]
[TD="class: xl66"]West Ham[/TD]
[TD="class: xl66"]3[/TD]
[TD="class: xl66"]0[/TD]
[TD="class: xl66"]H[/TD]
[TD="class: xl66"]2.15[/TD]
[TD="class: xl66"]3.47[/TD]
[TD="class: xl66"]3.85[/TD]
[TD="class: xl66"]W[/TD]
[TD="class: xl66"]L[/TD]

[TD="class: xl66"]25/08/12[/TD]
[TD="class: xl66"]Tottenham[/TD]
[TD="class: xl66"]West Brom[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]D[/TD]
[TD="class: xl66"]1.54[/TD]
[TD="class: xl66"]4.53[/TD]
[TD="class: xl66"]7.5[/TD]
[TD="class: xl66"]D[/TD]
[TD="class: xl66"]D[/TD]

[TD="class: xl66"]26/08/12[/TD]
[TD="class: xl66"]Liverpool[/TD]
[TD="class: xl66"]Man City[/TD]
[TD="class: xl66"]2[/TD]
[TD="class: xl66"]2[/TD]
[TD="class: xl66"]D[/TD]
[TD="class: xl66"]3.15[/TD]
[TD="class: xl66"]3.45[/TD]
[TD="class: xl66"]2.48[/TD]
[TD="class: xl66"]D[/TD]
[TD="class: xl66"]D[/TD]

</tbody>

Data extends up to 3000 rows for some leagues.

Somehow, I would like to do two similar things.

The first is to identify only those games where the home team has WON 4 out of their 5 PREVIOUS home games, AND where the away team has LOST 4 out of PREVIOUS last 5 away games.

Specifically any game where the home teams previous form is WWWWD, WWWDW, WWDWW, WDWWW, DWWWW. The away team form combination is abit more complex with 112 possibilities but basically no more than 1 win in their last 5 games

While the second is to identify only those games where there have been 2 or more goals in 4 of the home teams last 5 games, AND 2 or more goals in 4 of the away teams last 5 away games.

Is this something that is even possible in Excel?

If it is, I'd really appreciate some general advice on how you would go about approaching the task?

 

[jmacleary]

Hi there. I haven't provided a full solution, as you only wanted some guidance, but I have cobbled together a couple of columns that can go towards what you want. I assume your data starts in A1 - for the formulas to work there needs to be an extra blank row at the top, so I have shown data starting in row3. The formula in L3, copied down, give the last 5 results (in reverse order) for the home team - the formula in M3 copied down gives the number of wins in that set. I notice you didn't include examples such as"WWWWL" - if you only want to cater for win/draw scenraios then you will need to modify that formula. A modification of those formulas in N and O gives the away team form, and column P gives a match if both criteria are satisfied. It would be possible to condense the formulae into a single cell, but it would be very complex, so I thought it better to set it out in columns.

Excel 2007 32 bit

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
Date	HomeTeam	AwayTeam	Home Goals	Away Goals	Result	Home Odds	Draw Odds	Away Odds	H	A	Home pattern	Away Pattern
Arsenal	Sunderland	D	D	D	D	D
Chelsea	Swansea	H	W	L	W	L
Chelsea	Swansea	H	W	L	WW	LL
QPR	Swansea	A	L	W	L	WLL
Chelsea	Stoke	D	D	D	DWW	D
West Brom	Swansea	H	W	L	W	LWLL
Chelsea	Aston Villa	H	W	L	WDWW	L
Man City	Southampton	H	W	L	W	L
Wigan	Chelsea	A	L	W	L	W
Everton	Man United	H	W	L	W	L
Chelsea	Swansea	H	W	L	WWDWW	LLWLL	Match
Chelsea	Liverpool	A	L	W	LWWDW	W

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]18/08/2012[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]1.44[/TD]
[TD="align: right"]4.89[/TD]
[TD="align: right"]9.5[/TD]

[TD="align: right"]0[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]18/08/2012[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]1.85[/TD]
[TD="align: right"]3.82[/TD]
[TD="align: right"]4.8[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]18/08/2012[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]2.7[/TD]
[TD="align: right"]3.47[/TD]
[TD="align: right"]2.85[/TD]

[TD="align: right"]2[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]18/08/2012[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]

[TD="align: right"]2.1[/TD]
[TD="align: right"]3.55[/TD]
[TD="align: right"]4.2[/TD]

[TD="align: right"]0[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]18/08/2012[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]2.45[/TD]
[TD="align: right"]3.4[/TD]
[TD="align: right"]3.26[/TD]

[TD="align: right"]2[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]18/08/2012[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]4.38[/TD]
[TD="align: right"]3.61[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]3[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]18/08/2012[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]2.3[/TD]
[TD="align: right"]3.46[/TD]
[TD="align: right"]3.51[/TD]

[TD="align: right"]3[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]19/08/2012[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"]1.21[/TD]
[TD="align: right"]7.55[/TD]
[TD="align: right"]19.38[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]19/08/2012[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"]6.75[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1.67[/TD]

[TD="align: right"]0[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]20/08/2012[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]4.45[/TD]
[TD="align: right"]3.71[/TD]
[TD="align: right"]1.91[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]22/08/2012[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"]1.29[/TD]
[TD="align: right"]6.2[/TD]
[TD="align: right"]14[/TD]

[TD="align: right"]4[/TD]

[TD="align: right"]4[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]25/08/2012[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]

[TD="align: right"]3.25[/TD]
[TD="align: right"]3.4[/TD]
[TD="align: right"]2.6[/TD]

[TD="align: right"]3[/TD]

[TD="align: right"]0[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #E0E0F0"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]L3[/TH]
[TD="align: left"]=IF([COLOR=rgb(255]ISNA(LOOKUP([COLOR=0)]2,1/([COLOR=rgb(0]B$1:B2=B3[/COLOR]),L$1:L2[/COLOR])),J3,MID(J3&LOOKUP([COLOR=0)]2,1/([COLOR=rgb(0]B$1:B2=B3[/COLOR]),L$1:L2[/COLOR]),1,5)[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]M3[/TH]
[TD="align: left"]=LEN([COLOR=rgb(255]L3[/COLOR])-LEN([COLOR=rgb(255]SUBSTITUTE(L3,"W","")[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]N3[/TH]
[TD="align: left"]=IF([COLOR=rgb(255]ISNA(LOOKUP([COLOR=0)]2,1/([COLOR=rgb(0]C$1:C2=C3[/COLOR]),N$1:N2[/COLOR])),K3,MID(K3&LOOKUP([COLOR=0)]2,1/([COLOR=rgb(0]C$1:C2=C3[/COLOR]),N$1:N2[/COLOR]),1,5)[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]O3[/TH]
[TD="align: left"]=LEN([COLOR=rgb(255]N3[/COLOR])-LEN([COLOR=rgb(255]SUBSTITUTE(N3,"L","")[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]P3[/TH]
[TD="align: left"]=IF([COLOR=rgb(255]AND(M3=4,O3=4),"Match",""[/COLOR])[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[jmacleary]

It looks like those formulas adidntcome out right so here they are:
L3 =IF(ISNA(LOOKUP(2,1/(B$1:B2=B3),L$1:L2)),J3,MID(J3&LOOKUP(2,1/(B$1:B2=B3),L$1:L2),1,5))
M3 =LEN(L3)-LEN(SUBSTITUTE(L3,"W",""))
N3 =IF(ISNA(LOOKUP(2,1/(C$1:C2=C3),N$1:N2)),K3,MID(K3&LOOKUP(2,1/(C$1:C2=C3),N$1:N2),1,5))
O3 =LEN(N3)-LEN(SUBSTITUTE(N3,"L",""))
P3 =IF(AND(M3=4,O3=4),"Match","")