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
Another really fun one! I wanted to make a solution that's as dynamic as possible, so it's easy to add in new beer and pop tart pairings
Spoiler
Separated the columns into 3 types, using dynamic selects
-stream 1 is for the key fields
-stream 2 is for the columns that were titled with the pairings. A transpose into a Regex parse separated the beer and pop tart name
-stream 3 is for the columns that listed beer, pop-tart, and score one after the other. Similar approach to stream 2 with a transpose, then cleaned up the "name" column to just have pop-tart/beer/score. Needed a new key field for each unique set within the transposed data (multi-row formula took care of that), and then cross-tabbed the data set back into proper columns.
a RecordID tool before splitting the streams made it easy to rejoin.
-stream 1 is for the key fields
-stream 2 is for the columns that were titled with the pairings. A transpose into a Regex parse separated the beer and pop tart name
-stream 3 is for the columns that listed beer, pop-tart, and score one after the other. Similar approach to stream 2 with a transpose, then cleaned up the "name" column to just have pop-tart/beer/score. Needed a new key field for each unique set within the transposed data (multi-row formula took care of that), and then cross-tabbed the data set back into proper columns.
a RecordID tool before splitting the streams made it easy to rejoin.
My solution! Should be dynamic for any number of beer & pop-tart combinations. Slight difference in results from posted solution because I removed any records that didn't have a score (some of the records had a pop-tart identified, for example, but no beer or score. I figured those were superfluous to any analysis that would be done on the results).
A nice easy one for a post Sunday roast dinner chill
