Using LOOKUP ? For Football Data

[Onceageordie]

Afternoon all.

I have a spreadsheet which includes all data from 2000/01 for all the major European leagues. I have each league as a separate worksheet and copy & paste relevant functions/calculations etc. between them. I use the English Premier as my "Master" sheet, doing all experiments etc there first.

Currently on my iPad so I can't attach a file to my post, but an .xls with the Premier league data I use is at https://www.dropbox.com/s/5amfqa0v9y...20Example.xlsx

I now wish to add 3 extra fields for each match - Head2Head, Home Form & Away Form.

Head2Head
For each match I want to look back to the previous six (max) games in the data between these two teams and produce a text value for H, D, & A eg. ---HHD, HHHAAD, ADAHAD, -----D etc.

HomeForm
For each match I want to look back to the previous six (max) games, Home & Away in the data for the Home team and produce a text value for W, D, & L eg. ---WWD, WWWLLD, LDLWLD, -----D etc.

AwayForm
For each match I want to look back to the previous six (max) games, Home & Away in the data for the Away team and produce a text value for W, D, & L eg. ---WWD, WWWLLD, LDLWLD, -----D etc.

For all fields the rightmost character will be the latest (most recent).

For the Home & Away fields the value should be reset at the start of the season - I normally use 01 July 20xx as the start finish date for my seasons.

If anybody is able to help me with this I'd be really grateful.

Cheers

Onceageordie

[aydeegee]

Hi Onceageordie

This seems a reasonably easy issue for a vba solution, depending on how your data is laid out. However your link does not work, so I am unable to see how your data is laid out.


Regards
Alastair
(Neverageordie)

[Onceageordie]

Sorry about the link, I don't know what happened. Anyway I have attached the file directly.

Thanks in advance.

[aydeegee]

Hi Onceageordie

Sorry about the delay - some things have distracted me and this job has proved more challenging than I thought.

The Head2Head seems to be working OK - but it does take 3 minutes to run

The Home Form seems to be OK, but there is something wrong with the Away Form. However, before I fix it, I need to know the rules. When looking at (say) Sunderland they played at home on 12/4/14 and away on 16/4/14. Should the Home form take into account the subsequent result?

Regards
Alastair

[Onceageordie]

Brilliant, many thanks for looking at it.

To be honest the Home form and Away form were something I was going to ask about later cos I knew it was going to be more difficult!

What I want is for the Home Team - The last 3 games they played, either Home or Away, again with the most recent to the right.

Again for the Away Team, the last 3 games they played either Home or Away, with the most recent to the right.

Thanks again for looking at this for me, I shall have a look at your file tomorrow.

Cheers

[aydeegee]

Hi Onceageordie

I also looked at it today and have completed the Home Form and Away Form - I have done the last 6 matches, but it is very easy to reduce it to 3. Both of these run quite quickly, so I have not put a counter in the lower left as I did with the Head2Head.

Please note that the Head2Head macro turns off calculation while it is running - this reduces the running time to about 2 min 30 sec. It then turns on automatic calculating when it gets to the end. Hence, if it does not get to the end for any reason, you will need to manually reset the automatic calculation (File > Options > Formulas > Calculate options )

Regards
Alastair

[Onceageordie]

Alastair,

Many thanks for your work on this, I hope you're finding it as challenging as I am!

I have tried to understand what happens when I click the Head2Head "Update" button, but I'm still not really with it. At the moment it only seems to update Coventry v Southampton. When I look at the first occurrence of Coventry v Southampton (Row 181) the Head2Head field shows ----AD. As this is the first occurrence in the list of this pairing there should only be one result not 2.

In fact I think it would be better if I try and explain what I actually want to do with the data to try and help.

On an on-going basis I would add the fixture information for upcoming matches. Obviously there will be no result data there yet. I would like to look at the Head2Head data for this pairing to decide whether to include this forthcoming match in my analysis. Therefore the Head2Head field has to show the PREVIOUS (up to 6) meetings between these 2 teams at the Home venue only, so only eg Coventry v Southampton not Southampton v Coventry. If selected for analysis I would then look at the Home Form & Away Form for the 2 teams to decide whether to select the match for betting purposes. What would be nice (cos I love the Update button idea) is to enter the upcoming fixtures, hit the button and the Head2Head, Home Form & Away Form fields would be populated with the up to date data. I also wish to run historical analysis on the whole file to check systems, ideas etc. so I need all of the fields in the 3 columns populated. Once they are populated there will be no need to check or change them in the future.

I suggested using Arsenal v Aston Villa as an example fixture earlier in the thread as both these teams have been in the premier league every season so we have the maximum amount of data available. Here is a screenshot of these two with filters applied which shows what should be in the Head2Head field.

Example 1.jpg

I use 01 Jul 20xx as the start date for each season, and therefore 30 Jun 20xx as the end of the season. Some teams have spent every season in the premier league, others have only had one season eg Coventry. The first season 2000/01 will have no entries in the Head2Head field as there is no previous data available. Season 2001/02 will have a maximum of 1 entry in the Head2Head field for teams where both were in the league the previous season. Season 2002/03 will have a maximum of 2 entries in the Head2Head field for teams where both were in the league the previous 2 seasons and so on.

I hope this gives you more of an idea how I wish to use the data and will allow you to help me get there!

Thanks again

Dale

[aydeegee]

Hi Dale

I think that I understand a little more. I thought that I was being quite clever in considering eg Coventry v Southampton and Southampton v Coventry. You have to understand that I know very little about football (and care even less!)

I am not able to understand what you have done to only see Coventry v Southampton. I have just run the file attached to #6 and it works perfectly (according to my misunderstood rules). I will now work on getting something closer to your Example1.jpg. In considering Home Team and Away Team Form, does it matter (for this purpose) if they are playing at home or away?

And what do you mean by challenging? It's a walk in the park (compared to say understanding the buying decisions of those with different chromosomes than me).

Regards
Alastair

[Onceageordie]

Hi Alastair,

We have two different scenarios really. The Head2Head is HISTORICAL over previous seasons, to see how these two teams perform against each other at the same venue. It's surprising how many teams have a great record in a single tie.

The Home Form and Away Form is CURRENT form eg how well are they doing at the moment. So for form purposes, no it doesn't matter if they are playing at home or away I just want to know how well they have done in the last 3 (or 6) matches.

I have just tried to run the file from #6 again and I still don't think it's working right. I have to enable content and enable editing, then hit the Head2Head update button. When it has finished, using your misunderstood rules, I would have expected to see something in every Head2Head cell however Coventry v Southampton is the first cell that has anything in it. Am I doing something wrong? Might it be cos I'm using Excel 2013?

Cheers for now.

Dale

[aydeegee]

Hi Dale

The penny has just dropped and I understand what you are looking at.

The macro only records the history of the last Head2Head meeting. Thus, if you look at the bottom of the list, you will notice that there is considerably more data filled in! Anyway - I'm working on the correct answer.

Regards
Alastair

[aydeegee]

Hi Dale

I am a little pushed for time on this, so it has not been through the final quality control check. However, I think it is doing what you asked.

The Head2Head now runs in a few seconds, but the Home and Away Form take a little longer, so I have put a counter in the lower left so that you can see the progress (they go up to about 76)

I have a thought as to how this can be speeded up, but I will await your confirmation that all is well.

Regards
Alastair

[Onceageordie]

Yay, a lot quicker!

A couple of quick observations:

1). Both Head2Head & Home/Away Form are one step out. They should not include the current match, it should be for the PREVIOUS six matches. If I add a new fixture there will obviously not be a result until the game is played!

2). The Head2Head entries are all H or A no D!! I think you must be pulling in the value from the "Fav" column (I) instead of the "Result" column (F).

I find the easiest way to check is to filter Arsenal v Aston Villa and compare the results to the example I posted earlier. We're nearly there!!

Cheers
Dale

[aydeegee]

Hi Dale

I was wrong. The Home and Away Forms were not correct. They are now - both run from the Update button on Column K.

I have fixed The Head2Head being out of sync.

Let me know if you find anything else wrong

I have left the Home and Away form as 3 games, but this can be easily amended to any number you wish.

Regards
Alastair

[Onceageordie]

Alastair,

Head2Head appears to work correctly - thank you!

The form calculation appears a little slow(?!) - I clicked the button at 11:30 and it's still running at 13:12! The counter is now up to 108, what will it go up to?

Cheers for now

Dale

[Onceageordie]

Alastair,

Something's still not quite right with the form calculation. I have removed all rows but the first month (29 rows) and it's still taking forever. Currently the count is up to 1860 and has taken 10 minutes. I tried earlier with the first season data and gave up when the count reached 5500 after about 40 minutes.

Dale

[Onceageordie]

Right, my fault! I see you have a COUNTA at the bottom of column I - I had been copying stuff into it and overwriting it.

First 29 rows run fine.

How can I use the worksheet to analyse different leagues? Do I just copy & paste columns A to I and will it pick up the new names?

Cheers Dale

[Onceageordie]

Had to pop out, count now 18405 - running 2hrs 10mins. Time to kill it methinks!

Dale

[aydeegee]

Hi Dale

Your #17 was on sheet 2 and I did not see it, but one of my suggestions was to check for anything after row 5278. The COUNTA is now redundant - it was used with a previous version progress bar. I think that the problem you caused was with the calculation of the last row and possible it was getting into a loop.

You certainly have much more patience than I. I generally pull the plug after 2 minutes!

To analyse different leagues, you could start a new sheet - you will need all the data that you put in to columns A:I and you will also need the formulas in all cells M2:S2 (the actual column is vital.

Another way of starting a new league would be to save your original data and then replace your data in columns A:I and save as another name.

Let me know how you get on.

If you are happy that your original question has been answered, you might mark this thread as closed. (Go to your first posting - you can amend the title.) (I am still happy to take questions on this.)

Regards
Alastair

[Onceageordie]

Hi Alastair,

I've had some fun playing around this morning and everything works as expected. I'd now like to fine tune things to finish off so I can get some form of order into the way I do things.

I appreciate you don't know much about football so I'll try and explain what I want to do-

Each season a certain number of teams are promoted from one division/league into a higher one, and a number are relegated to a lower one. If two teams are currently in the premier but were both in the championship last season, I would like to include that result in my Head2Head. I have therefore tried to use the analysis sheet to calculate Head2Head figures for all English ties by copying in all data from my Premiership, Championship, League 1, League 2 & Conference sheets. The total number of rows is in excess of 33000. When I hit the update button it goes wrong. I've spent some time trying to narrow down the maximum number of rows and have found that 20773 works okay, 28501 craps out. Is there a maximum number of rows? Can it be changed ?

Once I get this working I can then move on to other European countries which work in a similar way.

Cheers again

Dale

[aydeegee]

Hi Dale

I have just run this with 42217 rows and it works ok. There is no maxumum, so I am not sure why it should be. Does it always crash out atthe same row? Is there anything wrong with the data (eg date not being a numnerical field. (Can you tell I am clutching at straws?). Does the "year" in column S look right? It consists of "20" plus the number of years from 1 July 2000. If you are using older data, the system may get indigestion.

Regards
Alastair

[Onceageordie]

Alastair,

Is there any way I can get the file to you - it's 5.2mb and the forum won't let me upload a file that big.

Dale

[Onceageordie]

Alastair,

I have tried to "clean" the file by deleting all columns after S and all rows below the last one. I have then used column Z as a "League" tracker. When I copy in the data I need to keep track of which league it is from so I can copy it back to the correct one.

When I copy in premier data & press the button it works fine. When I copy in the Championship data & press the button it works fine. Same with League 1. This gives me a file of 20773 rows. When I copy in the League 2 data it goes screwy.

What I get in the Head2Head field is =IF(RC[5]=R[-1]C[5],RIGHT(R[-1]C&R[-1]C[-4],6),"-----"). It's the same in every field.

I'm not sure where the problem is.

Dale

[Onceageordie]

Alastair,

I think I've narrowed the problem down to the League 2 data. I've looked through it and can't see anything obvious but when I copy these cells into the analysis sheet it bombs out with the above symptoms.

I include a copy of the League 2 data to see if you can spot anything that might be causing the problem.

Football League 2 Data.xlsx

Cheers

Dale

[aydeegee]

Hi Dale

Bad news, I'm afraid. The file works perfectly for me.

I have PM'd you my email address for the 5.2 mb file - you may have to send it in 2 sections.

Regards
Alastair

[Onceageordie]

All sorted - many thanks Alastair.

Dale