Weekly Challenges
Solve the challenge, share your solution and summit the ranks of our Community!Also available in | Français | Português | Español | 日本語
IDEAS WANTED
Want to get involved? We're always looking for ideas and content for Weekly Challenges.
SUBMIT YOUR IDEA
Hello Community Members,
A solution to last week’s challenge can be found here.
This challenge was submitted by Mark Thompson @Watermark . Thank you, Mark, for this Oscar-worthy challenge!
Every year as the Oscars roll around, John reviews and organizes his movie collection. He has a multi-tab spreadsheet with information about movies, including:
- Name
- Year of release
- Format (DVD, BR, Disc, etc.)
- Movie rating
- Director
- Ownership status (OWN indicates John has a copy, 1 means he intends to purchase)
The spreadsheet is divided into several tabs: Oscar best picture (BP), best director (BD), best actress, and best actor, among others. There are also "Top Lists" from various sources, including, but not limited to: IMDB, Variety, Time Magazine, and the British Academy of Film and Television Arts (BAFTA).
There is also another spreadsheet that has a list of the sheet names.
As you can imagine, the data is messy. It became too much work for John to maintain the spreadsheet, and he decided to leverage Alteryx to help him organize the data. John also wants to remove the On Line FilmTV Assoc and Warren Miller tabs from his spreadsheet, as he no longer wishes to maintain them.
Your task for this challenge is to create a macro that consolidates all Excel tabs into one spreadsheet. Include these categories:
- Movie title
- Year the movie was released
- Movie rating
- Status (whether John owns the movie or not)
- Original spreadsheet tab it comes from
Need a refresher? Review the following lessons in Academy to gear up:
Introduction to Macros in Designer
Good luck!
The Academy Team
Source: Challenge creator's own private collection spreadsheet he put together over a number of years.
I enjoyed this! Took me a minute to get my head around the requirements but hey that represents the real world.
For anyone finding that their checks explode, it's because there are a couple of duplicate lines. You can remedy it with a unique tool for a sanity check:
Great challenge
Good practice for batch macros :)
