Fixtures List Creator
Posted on May 9, 2009
Filed Under Sports |
There are some comments and requests through email asking about the possibility of having automatic fixtures list in my football standing league creator table. So, if they finished filling the name of the teams, fixtures list will be automatically created, so they don’t have to manually typing the team’s name and thinking about team’s matches schedules.
In the beginning, I created that spreadsheet to automate the standing table based on ready made matches or fixtures. But the people who using this template is correct about asking possibility of creating totally new competition and matches where they have to design all from the scratch.
Understanding that concerns, I tried to figure out about automate this fixtures list. And I found out that creating fixtures list is not an easy task. Especially if it is involved many teams, or when the number of teams are odd where impossible to have all matches at the same time.
It took several nights just to find the logic to create this fixtures list manually, and it took additional nights to find the right formula and function in excel to map the logic without using excel macros. Fortunately Manchester United vs Arsenal and Barcelona vs Chelsea Champion League matches accompany me to stay awake with matches results I expected :-).
This spreadsheet can be used to create matches schedules for other sport events that have competition style, like volley ball, futsal, badminton, basketball or others.
This spreadsheet can create fixtures lists for maximum 40 teams. The reason I limit the teams until 40 teams because of the size of excel file. I don’t feel comfortable creating the template with filesize more than 1.5 Mb, but this is a consequence if you create excel template with many “if condition” without macros. If you want to create the fixtures lists with the number of teams more than 40, you can continue the formula I created inside the spreadsheet.
This spreadsheet only consists of three worksheet, one is setup worksheet where you have to set the number of your teams, the competition type and your team’s name. There is two choices of competition type, full competition where each team will meet twice, or half competition where each team only meet once.
The second worksheet is the fixtures sheet where all the matches will be generated automatically after you completed your setup worksheet.
The third worksheet is an intermediary worksheet between setup worksheet and fixtures worksheet, where all formulas and functions are in this worksheet. Just unhide it to learn about the formula.
You can download the excel file of fixtures list creator here.
I also completed my soccer standing table template with this fixture list creator function. So, if you need more complete excel standing table, you can download it here.
Cheers.
You can read and download my other templates below :
- La Liga Fixtures 2009/2010
- Eredivisie Fixtures 2009/2010
- Ligue 1 Fixtures 2009/2010
- Italian Serie A Fixtures 2009/2010
- Create Your Own Soccer League Fixtures and Table
- Bundesliga Fixtures 2009/2010
- Printable Tournament Bracket
- Champions League Fixtures 2009/2010
Comments
25 Responses to “Fixtures List Creator”
Leave a Reply
i AM HAVING TROUBLE GETTING THE FIXTURES TO WORK OUT, EVERYTHING ELSE IS FINE. bUT fIXTURES KEEP COMING UP WITH THE NUMBER 1 TEAM ALL THE WAY DOWN THE HOME SIDE,,SIDE AND THE AWAY SIDE. aNY iDEAS HOW TO RECTIFY
Hi Tam,
May I know what Excel version do you use ? It looks normal from my Excel version, but I saw there are several people have the same problem. It could be the excel setting, but I am still looking for what cause that. If there are some people now how to solve the problem feel free to comments here. I will appreciate that.
Regards
Hey man, you made my day. thank you much
i dont know how enything works lo
would it be possible to put fixtures into a grid so then it does away with the long list.
ie. teams listed at top and teams listed at side and where they cross a space for score to be inserted.
Thanks in advance
Hi, would you please assist me to create fixtures list of playing chess. We have chess club but until now the score is prepared still in manualy .Higly appreciate for your attention .
Regards,
Juharto Sembiring
I’m having the same problem and I’m using Excel 2002
Sorry, but quite frankly this template doesn’t work! As soon as you change the number of teams it messes up…
Hi Tam, Dave and Greg,
Thank you for using my template, and I think I found what’s your problem. You should activate the analysis toolpak add-ins. Go to excel menu > tools > add-ins and tick the analysis toolpak.
And all the function will work as expected.
Regards
Hi,
I tested the 2009/10 fixtures and came across a problem. I first sorted the teams alphabetically then next added theoretical results and noticed that the league table did not display resuts for Wigan or Everton. Their position in the table was correct but showed all zeroes
Further testing leaving initial set up - I filled in test results for the next set of fixtures only and WIGAN and LIVERPOOL showed zeroes only. And so on and so on for each block of fixtures there are always 2 teams whose reults don’t show up on the League table
Hi
Thanx so much for the template
v1.1 really works a lot better than the first few i tried
if you update it again, can you let me know?
regards
Kev B
Fyi function “ISODD” must be localized to make it work.
Oh, sorry, great work!
Hi Marco
I have inserted all the necessary teams and the dates. The problem I’m sitting with is the weeks of the matches. The dates are all scrambled because the team names are in alphabetical order and I’ve tried to match the dates with the week numbers and I totally couldn’t do it because I’m messing up the formulas.
Please guide because the match weeks are according to dates.
Regards
Harry
Dear Mark, Marco, and Harry,
The fixtures matches are created automatically with the formula within the cells with the main reason to give the user all possibly team matches. Once you get all possible matches, you can process the results in other worksheet. Just copy all the matches cells and paste it as value only in other worksheet. And you can do any sort function, adding the dates, and so on.
Please don’t do any sort function in the main fixtures sheet because it will make all the function not working properly.
Regards
Hi.. I am trying to get premiere league fixture 2009/2010, it doesnot seem available. Any idea where I can get it>?
hi has anyone sorted the problem where if you add two teams or decrease by 2 thae fixtures do not update, therefor the table doesnt work. Tis is so so close to being what ive been looking for for years.
Hi Richard,
Have you activate the the analysis toolpak add-ins ? Go to excel menu > tools > add-ins and tick the analysis toolpak. And all function should work correctly.
Regards
Hi I was wondering if you were doing an excel sheet that would have a Europa League set up with the ability to enter your own teams in and select the fixtures, as we run a football competition with 8 groups of 5 teams playing each other once and the winners progressing to the quarter finals.
I am experiencing problems getting the fixtures to work out, fixtures keep coming up with the number 1 team playing home games for the entire fixtures i.e. it is not scheduled to play an away fixture. Any ideas to rectify that?
I am having problems generating my basketball fixtures list.
I have input all the teams for my list but only fixtures for team 1 have been developed.
Can anyone help?
@Jora and John: You should activate analysis toolpak add-ins. Go to excel menu > tools > add-ins and tick the analysis toolpak. And all function should work correctly.
i have already activate analysis toolpack add-ins but like John fixtures keep coming up with the number 1 team playing home games for the entire fixtures i.e. it is not scheduled to play an away fixture.
I’ve tried to access the ’settings’ sheet which is hidden but it doesn’t seem to be in either the V1.1 or V1.6 workbooks. I used the Menu ‘Window’ drop down list but the ‘Unhide’ was grey (indicating nothing was hidden).
I hope you can help me resolve this matter as I want to use it for my flat green bowls league.