Last week I was helping a friend build a World Cup pool in Excel. It was fun debating the pool’s scoring rules. I also taught him a few Excel tricks along the way.
Data Cleanup (
I found a country list with FIFA rank. I used Flash Fill as it appeared to be a simple pattern (remove 1st three letters). Further down ‘IRNIR Iran’ was converted to ‘IR Iran’. That’s not right! Learn more from J-Walk!
Any Normal Person….
With such a small list and only 1 exception any normal person would manually correct the entry. I guess I’m not normal. I enjoy these puzzles. And what if the list had been much longer? A formula can solve it quicker than manually fixing entries.
What’s The Pattern?
Flash Fill made the first mistake. I made the second mistake.
Find The Last Upper Case Letter
I thought that this would work. I obviously didn’t scan the data. “CODCongo DR” breaks the pattern.
Find The First Lower Case Letter & Go Back One Space
This worked! In a nutshell, look for all lower case letters inside each text. We want the position of the 1st lower case letter. See my Excel file (above) for a full explanation of this array:
World Cup Pool Scoring System
The original idea was that everyone would select 2 teams from each group. However, everyone would pick mostly the same teams. Where’s the fun in this? I recommended the following:
- You get 1500 points to select 2 teams from each group
- Points are based on FIFA rating for each country
This way there’s more strategy in selecting teams. See my World Cup Template above.
Data Validation Trick
Manually creating 8 different data validation lists seemed too tedious. Unlocking the column reference allows us to create only 1 data validation list and then copy / paste it for all other groups 🙂
I’ve been a Data Analyst since 2001. I work in Toronto Canada.
I’m a big fan of Microsoft Excel !