The 2018 edition of Commonwealth games are on for a week now. Both of my homes – India and New Zealand have done so well. Naturally, I wanted to gather games data and make something fun and creative from it. Here is my attempt to amuse you on this Friday.
Looks interesting? Want to know how to make something like this on your own? Then read on…
1. Gather data thru live connection to gc2018.com
We want to set up a refreshable visualization. So the data will be fetched thru PowerQuery. All we need to know about medal standings, medalists and country participation data is available at gc2018.com website.
Latest Medal Standings: This is available at https://results.gc2018.com/en/all-sports/medal-standings.htm page as an HTML table (the first table on the page).
So we can get the data using below M:
= Web.Page(Web.Contents("https://results.gc2018.com/en/all-sports/medal-standings.htm")){0}[Data]
This is extracting data column of first row of the Web.Page.
Number of participants by country: There is no one page where this information is available. Instead you need to visit each country’s page on gc2018.com to get the data from participants table. For example, the page for Bermuda (available at https://results.gc2018.com/en/all-sports/entries-bermuda.htm) looks like this:
Fortunately, all URLs follow the same pattern. https://results.gc2018.com/en/all-sports/entries-<country name>.htm
So given a URL in column [URL], we can use this custom column formula to get the total number of participants.
=Table.SelectRows(Web.Page(Web.Contents([URL])){0}[Data], each ([Discipline] = "Total")))
This is extracting the first table on URL and then filtering it for Total Row.
Arranging everything in one table:
We can use a bit of built-in operations in Power Query to arrange all the necessary data in one tidy table. I am not going to explain all steps, but here is the final output. Try to come up with this on your own.
Now that our medal data is in Excel, in a table named medal_standings, let’s go ahead to next step.
2. Calculations to show medal standings by any criteria
The calculation engine for our little medal standings has few key things:
- Fetching and sorting by a column
- Slicer selection for sort options (Gold, Silver, Bronze, Total Medals, M/P)
Let’s go thru them:
Fetching and sorting by a column:
We would like to see countries by Gold, Silver, Bronze, Total medals and Medals per participant.
As per our medal_standings table, these are 3,4,5,6 and 9 respectively.
Assuming the column we want to sort is given by a named range – sort.option.num, we can use INDEX formula to fetch values, like this:
=IFERROR(INDEX(medal_standings,<row number>,sort.option.num),-10)
The -10 ensures that if we poll for a row that doesn’t exit, we get a negative value rather than 0. As some countries have 0 medals, having negative ensures that when sorting such rows are always at bottom.
Once we fetch a column, you can use LARGE() to re-order them top to bottom.
As there will be ties (few countries getting same number of medals), we can use de-duplication logic. This is when you add a very small unique fraction to each row before calling LARGE(). It is an elegant way to deal with ties and overcome Excel’s lookup formula limitations of returning only first match. See this decade old post by Robert discussing deduplication technique.
After this, just re-arrange original data (only columns needed for output) using another set of INDEX formulas.
A slicer to allow user to pick sort option
Now let’s just link up sort.option.num to a slicer so user can tell calculation engine what the sort order should be.
Start by making a pivot from a range like this:
But when you add a slicer on sort option, you realize the folly of your plan. The slicer buttons are out of order.
Technically, they are in order – alphabetical. But that is not what we want. We want them in the order – Gold, Silver, Bronze, Total Medals and M/P.
So what now?
Simple, we can ask Jackie Chan to karate chop the slicer and re-arrange it.
Alas, my summonJackie() macro was subscript out of ranging. So we need something else.
So we cheat Excel. We can pre-fix empty spaces – CHAR(129) to the slicer items. Since these are empty spaces, we just add 1 space for Gold, 2 for Silver etc. and make a slicer from these new values.
Note: In Power BI, you can simply order the sort option column by index number and that will fix the slicer problem. In fact, we wouldn’t bother with a slicer as Power BI tables are sortable by clicking on header.
That is better. Now simply style it and give it a buzz cut and you get this.
Related: Comprehensive guide and tutorial on all things Slicer – MUST READ
3. Preparing the viz
Now that everything we need is ready, simply bring calculated table to a blank worksheet (using Copy, Paste links) and arrange it in a neat table. Add Conditional formatting > Databars on medal and M/P columns. Position slicer neatly where these columns headers should be and you are gold.
Every now and then press Ctrl+Alt+F5 and go make a cuppa. When you are back, the medal table would be updated. Of course, come 16th of April 2018, there is no need to refresh it as the games would have ended.
Download the Commonwealth 2018 games medal tracker
Click here to download the Excel file. Play with it to learn more. Examine the query definition, control sheet and viz sheet to understand how it is put together. Make changes to the query (but duplicate it first, otherwise you will break the calculations) to fetch other data and make your own charts.
Ways to enhance this – adding past performance etc.
You can use the data from https://thecgf.com/ (Commonwealth Games Federation) to see historical performance and contestant data. They do not yet have 2018 values (as the games are ongoing) but you can see how countries have done in 2014 or 2010. Or you could combine this with performance in Olympics. How about combining this with demographic and well-being data (Gini scores or HDI ranks)? There are several ways you can mash-up this.
Love Games and Excel? Check out these visualizations
Me too. I like sport and I like data. Guess what, I build a lot of charts and cool visualizations on sport. Check out below and have fun.
- Roger Federer’s wimbledon win – Visualized
- Sachin Tendulkar’s cricketing awesomeness in one chart
- MLB Pitching Stats Dashboard in Excel+VBA
- Excel steeplechase keyboard shortcuts game
Want to learn how to build awesome worksheets – Check out 50 ways to analyze data course
If you liked this, you are going to love our 50 ways to analyze data online class. This program helps you analyze and visualize business data in myriad ways. Learn all about statistical analysis, financial analysis, analytical modeling, data sciency stuff (clustering, outlier detection, optimization etc.) from the comfort of your office or home. Next batch enrollments will begin soon.
Visit 50 ways course page to know more and join the waiting list.
30 Responses to “Rescue oddly shaped data – Battle between Formulas, VBA and Power Query”
Nice use of Power Query! Power Query is simply awesome! But somehow a lot of people are punishing themselves by not using it (not learning it).
An imperfect 4th approach for consideration... no codes at all...
Select myrange.
Go to Special --> Blank
Delete Cell --> Shift cell left
90% done... now we just need to move the data of 2nd column to the bottom of 1st column
Of course... Power Query is the best.
Cheers,
There is another way but it involves multiple steps:
Copy the values in column E, move the cursor to F5, Paste Special with Skip Blanks, OK
Copy the values in column D, move the cursor to F8, Paste Special with Skip Blanks, OK
And so on.
This works perfectly, albeit a little clumsily apart from the values in B17 and C16, which can be moved with simple copy and paste
Power Query Forever! I do not know how I survived for so long without knowing and using this tool, I can not recommend it to my colleagues, but by the way they prefer to suffer to learn.
My congratulations here from Brazil.
I rolled my eyes when I saw that data
Using decimal places is a nice trick to order data, thanks for that
And tweaking the first formula a bit, you can use OFFSET instead of INDIRECT
=OFFSET($A$1, MIN(IF(myrange, ROW(myrange)), ROWS(A$1:A1))-1, RIGHT(TEXT(MIN(IF(myrange, ROW(myrange) + COLUMN(myrange)*0.00001), ROWS(A$1:A1)), ".00000"), 5)-1)
Tried the above formula with the downloaded oddly shaped data file and I could not get it to work. I get #value without ctrl+shift+enter, and #ref with ctrl+shift+enter.
Sorry, it was SMALL, not MIN.
Add with CTRL+SHIFT+ENTER.
Thank you for your formula. Like the indirect formula I tested this one in older versions of EXCEL and it worked without ALTERATION in EXCEL 95. Very impressive.
Too complicated
Use =Sum to summarize all the sells to the left and Bobs Your Uncle
@Bertie... I am afraid that won't work when you have more than one value in a row.
I tested this formula in versions of Excel all the way back to Excel 95
=IF(ISERROR(INDIRECT("R"&SUBSTITUTE(TEXT(SMALL(IF(MyRange"",ROW(MyRange)+COLUMN(MyRange)*0.00001),ROWS(A$1:A9)),"00000.00000"),".","C"),FALSE)),"",(INDIRECT("R"&SUBSTITUTE(TEXT(SMALL(IF(MyRange"",ROW(MyRange)+COLUMN(MyRange)*0.00001),ROWS(A$1:A9)),"00000.00000"),".","C"),FALSE)))
So there are multiple ways of cleaning up messy data by formulas.
Wow.. Excel 95. Who knew people still use that. But as you have shown, Excel has all these beautiful and powerful functions for 23 years. It has data sciency stuff before DS was even a thing.
I had a problem with pasting the formula in the original post.
Formula should be: =IF(ISERROR(INDIRECT("R"&SUBSTITUTE(TEXT(SMALL(IF(myrange"",ROW(myrange)+COLUMN(myrange)*0.00001),ROWS(A$1:A1)),"00000.00000"),".","C"),FALSE)),"",(INDIRECT("R"&SUBSTITUTE(TEXT(SMALL(IF(myrange"",ROW(myrange)+COLUMN(myrange)*0.00001),ROWS(A$1:A1)),"00000.00000"),".","C"),FALSE)))
EXCEL even in a 16 bit version, is a very robust and capable program.
I don't like the VBA code. If you have a blank row in MyRange, the last entry in the range is doubled up in the paste.here range.
Not really. The macro is writing one cell at a time from paste.here. You have to clean the range before, which I was too lazy to write. But a line like Range(range("paste.here"), range("paste.here").end(xldown)).clearcontents should do the trick.
Adding Range(range("paste.here"), range("paste.here").end(xldown)).clearcontents fixed the problem.
for step split column by delimiter i am not getting option of split into rows or columns. Can you help me in this
Thanks Chandoo for promoting Power Query.
To simplify further, you can "Unpivot Columns" instead of right click on the newly created column and split it by comma in to rows in step 3 of Power Query.
i used
=LOOKUP(10000,B5:F5)
and got the answers. I just plagiarized this formula somewhere and use it, maybe you can explain why it works.
Regards
@Johan... I am not sure if the formula works correctly. When I tested it with the sample data in this post, it showed #N/As in two cells. Essentially, it will only give first value in each row. So if a row has multiple values, then subsequent values are missed. LOOKUP() function goes thru a list and finds the first value that is less than or equal to the input - in this case 10000 in B5:F5.
I have the need to convert pdf's to excel on occasion and they often come out a mess like this. I have used:
Cell G2 =COUNT(myrange)
Cell G3 =IFERROR(IF(G2-1<1,"",G2-1),"") copied down to G100
Cell H2 =IFERROR(LARGE(myrange,G2),"") copied down to H100
Waouw...
=IFERROR(INDIRECT("R" & SUBSTITUTE(TEXT(SMALL(IF(myrange "", ROW(myrange) + COLUMN(myrange)*0.00001),
ROWS(A$1:A1)), "00000.00000"), ".", "C"), FALSE), "")
but CTRL Shift Enter with {} before and after 🙂 😀
Here's a way with pivot table
https://www.bookkempt.com/2018/02/aligning-non-contiguous-data.html
This is brilliant. Bookmarked 🙂
Another possibility.
This assumes that you have a row index 'k' to use in the SMALL function and a column index 'h' to identify the columns of 'myRange'.
If you define 'coord' to refer to
=k+h/10 [assuming h<10]
then it will be possible to recover values later based upon location within 'myRange'. The formula 'nb' that identifies non-blanks by coordinates is given by
= SMALL( IF(myRange"", coord), k )
Finally, to unpick the pieces
= INDEX( myRange, INT(nb), 10*MOD(nb, 1) )
Whilst I am here and making trouble the PQ solution is also a tad over-complicated. All that is needed is to unpivot the entire table and remove the Attribute column.
The advanced editor would show
let
Source = Excel.CurrentWorkbook(){[Name="myRange"]}[Content],
#"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"})
in
#"Removed Columns"
1.fill the blank cells with 0
2.the requested column value=sum of those mess number column
but this can be used in only one column has value
Chandoo
And if we use the formula SEARCH (100000000, B5: F5)
JC
Another approach with Power Query, it will still work if the number of columns changed:
let
Source = Excel.CurrentWorkbook(){[Name="myrange"]}[Content],
#"Added Custom" = Table.AddColumn(Source, "List", each Record.ToList(_)),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"List"}),
#"Expanded LIst" = Table.ExpandListColumn(#"Removed Other Columns", "List"),
#"Filtered Rows" = Table.SelectRows(#"Expanded LIst", each ([List] null))
in
#"Filtered Rows"
Cool idea to use Record.ToList as added column. Thanks for sharing this.
Nowadays, you can just use TOCOL on Excel 2024, MS 365, and Web Excel. It has a parameter to ignore blanks/errors/both.