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