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.

Excel Files

There are 2 files. Cleaning FIFA country list  and  World-Cup-Template-2018v2.

Data Cleanup (Flash Fill)

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 🙂 

About Me

I’ve been a Data Analyst since 2001. I work in Toronto Canada.

I’m a big fan of Microsoft Excel !

Visit my blog and my recommended Excel training page.