This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
We've recently made an accessibility improvement to the community and therefore posts without any content are no longer allowed. Please use the spoiler feature or add a short message in the message body in order to submit your weekly challenge.
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.
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).