I like it when this happens: out of the blue someone says, can you do this? I always say, let’s have a look.

I opened an email today, from Dennis, which asked me if I could prepare a spreadsheet to automate the recording of the scores for a football tournament to be held very soon: the UK Africa Cup, with the final being played at Upton Park.

I finished work a bit early today and had a little bit of spare time so I took a look at the fixture list:

  • four groups
  • four teams in each group
  • four quarter finals
  • two (surprise!) semi finals
  • a 3rd place play off
  • a final

At first I thought, this is very complex but as I walked about a bit I thought … if I create a few helper areas, create league tables … use =RANK() rather than sorting each table. Then I can use =VLOOKUP() to go and find the teams for the quarters. ==IF()+IF()+IF()+IF() will give me the games won, drawn and lost … =SUM() will tell me how many games were played …

The work flowed fairly easily but it still took me about three hours to finish it. The best part was how I was able to get half way through the exercise and suddenly realise that the way they had organised the group stages, all I had to do to finish the spreadsheet was to copy and paste groups C and D into a copy of the work sheet I had created for groups A and B … then put them back together again …

After that it was just a matter of getting the look and feel of it right. I think I did that.

I teased Dennis at the half way stage by saying, you could finish it yourself now … ten minutes later I sent him the finished article as he said it was a bit complicated for him.

I have to say I enjoyed the job and felt quite satisfied as I tested it and found that it seems to have come together well. I know there are a couple of things that could be done to enhance it even further but what the heck, they are not needed!

Here are some screen shots for you! In reverse order …

Screen Shot 2013-05-22 at 21.34.11 Screen Shot 2013-05-22 at 21.33.34 Screen Shot 2013-05-22 at 21.33.11

 

Duncan Williamson

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: