• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help on set up football data analysis

Readings

New Member
HI

First up, big respect to Chandoo, I have used it before now and it is an awesome site, keep up the good work.

Im hoping someone can help me please, I need to up-skill myself and happy to do work and learning. I am looking to analyse football results from the English Premier League so will have columns eg; Date, home team, Away Team, Home team goals, Away Team goals, Result etc

I want to take my source table and split the information in to individual tables so that all the results for each team have their own worksheet and I want this to automatically update itself whenever new records are added. What would people advise of the best way to do this?

I did try a pivot table but it doesnt seem to work for this type of thing. I can do it for individual home team =IF(ROWS(A$12:A12)>$E$1,"",INDEX('Source data'!A$2:A$5417,SMALL(IF(Table1[HomeTeam]=$B$1,ROW(Table1[HomeTeam])-ROW('Source data'!$D$2)+1),ROWS(A$12:A150)))) but I cannot figure out how to change the formula to do both home and away games. In all honesty I am not sure if I am setting this up in the right way to start with or whether I should be learning how to put the table in to an Access Database and learn about that!?!

Any help will help me keep my sanity...

Thanks a million
m
 
Personally, I think advanced filter would be easiest solution here.

See here for example.
http://chandoo.org/wp/2012/11/27/extract-subset-of-data/

Instead of using drop down, set filter criteria (Home team & Away team, I assume you want team stat for both case) for each sheet and use record macro function to record advanced filter operation for all sheets (assign short cut key if so desired). This can update entire workbook in one shot if new data is entered in source table.
 
Back
Top