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
Types of Macros in Designer
Creating a Batch Macro
Good luck!
The Academy Team
Source: Challenge creator's own private collection spreadsheet he put together over a number of years.
