Microsoft Excel Tutorial: Which Products Contain Both Of These Items?
Today, an odd data problem. I have a solution, but I suspect there are much easier ways to do this. I have a list of songs in workouts from Supernatural VR. Over 500 workouts are Quick Hits with just two songs. Each Quick Hits workout should be a subset of a longer workout. Given the two lists, how can you use Excel to find the parent workout?
My solution involves an XLOOKUP with a concatenated key and a concatenated lookup vector. This gives me a list of the Workout IDs that contain song 1 and the Workout ID's that contain song 2. Given these two list of workout IDs, how can you find which workout ID is in common between the two cells?
I was surprised to find multiple matches. I called Marla Bright and we talked about Supernatural and how to narrow the choices down.
Download the data from here:
I was a guest on Marla's For the Love of the Map podcast today. Watch that episode at
In today's episode (#2661), I have a data problem, and I think there’s a more efficient way to solve it! I've got two lists of workout songs from a VR fitness app called Supernatural. One list is of shorter, two-song workouts called "Quick Hits," and the other list includes the longer workouts, which can be up to twelve songs. The theory is each Quick Hit has its songs taken from one of the longer workouts. My challenge? If I give you a Quick Hit, can you help me identify which longer workout contains both songs?
To solve this, I’m using Excel's XLOOKUP and FILTER functions to cross-check each Quick Hit with the longer workouts. I use artist and song names to match these up, and I create a unique key using the workout ID and track number to keep it all organized. But even with some clever formula tricks—like TEXTJOIN and LET—it’s getting messy. I get partial results, but some surprises are popping up. Certain song pairs are showing up in multiple workouts, which is throwing off my process.
After some trial and error (and a great conversation with Marla on the Supernatural VR podcast!), I tried adding an extra filter. Now, I’m cross-referencing to ignore any workouts with a higher ID than the Quick Hit workout ID. This helps narrow down the list, but I’m still not fully satisfied. There are cases where songs match, but they don’t necessarily match by coach or difficulty level, making it even trickier to determine the exact workout that each Quick Hit is derived from.
This project pushed my Excel formulas to the max, and I’m wondering if there’s a better approach. I’ve included the workbook in the description so you can download it and give it a try. If you have ideas on how to streamline this process or know of a simpler solution, let me know! I’d love to feature a new solution on an upcoming podcast.
Check out the Supernatural VR podcast episode featuring me as a guest, and download the workbook below. Let’s work together to solve this data mystery in Excel!
Buy Bill Jelen's latest Excel book: MrExcel 2024 Igniting Excel
Table of Contents
(0:00) Looking for a better solution today
(0:15) Supernatural VR workouts and Data
(1:17) Trailer For The Love Of The Map
(2:19) Problem Description
(3:04) Finding both songs for each Quick Hit
(3:52) XLOOKUP with Concatenated Key & Concatenated Lookup Vector
(4:42) Data for other lookups with more tracks
(5:17) Matches for Song with FILTER, SORT, ARRAYTOTEXT
(6:23) Which IDs are in both lists using LET, TEXTSPLIT and XLOOKUP
(7:30) Results are finding multiple matches
(8:23) Discussion with Marla Bright
(12:43) Formula changes to throw out later workouts
(13:54) Consolidating 3 formulas into 1
(14:10) Download the data
(14:21) Join the membership
(14:40) Thanks for watching
This video answers these search terms:
Excel XLOOKUP for data matching
Excel FILTER formula tutorial
Supernatural VR workout data analysis in Excel
Quick Hits vs. long workouts data problem
Advanced Excel formula techniques for matching lists
Using TEXTJOIN and LET in Excel
Identifying duplicates in multiple Excel lists
Airtable to Excel data workflow
Data cross-referencing with Excel functions
Solving complex data problems with Excel
Today, an odd data problem. I have a solution, but I suspect there are much easier ways to do this. I have a list of songs in workouts from Supernatural VR. Over 500 workouts are Quick Hits with just two songs. Each Quick Hits workout should be a subset of a longer workout. Given the two lists, how can you use Excel to find the parent workout?
My solution involves an XLOOKUP with a concatenated key and a concatenated lookup vector. This gives me a list of the Workout IDs that contain song 1 and the Workout ID's that contain song 2. Given these two list of workout IDs, how can you find which workout ID is in common between the two cells?
I was surprised to find multiple matches. I called Marla Bright and we talked about Supernatural and how to narrow the choices down.
Download the data from here:
I was a guest on Marla's For the Love of the Map podcast today. Watch that episode at
In today's episode (#2661), I have a data problem, and I think there’s a more efficient way to solve it! I've got two lists of workout songs from a VR fitness app called Supernatural. One list is of shorter, two-song workouts called "Quick Hits," and the other list includes the longer workouts, which can be up to twelve songs. The theory is each Quick Hit has its songs taken from one of the longer workouts. My challenge? If I give you a Quick Hit, can you help me identify which longer workout contains both songs?
To solve this, I’m using Excel's XLOOKUP and FILTER functions to cross-check each Quick Hit with the longer workouts. I use artist and song names to match these up, and I create a unique key using the workout ID and track number to keep it all organized. But even with some clever formula tricks—like TEXTJOIN and LET—it’s getting messy. I get partial results, but some surprises are popping up. Certain song pairs are showing up in multiple workouts, which is throwing off my process.
After some trial and error (and a great conversation with Marla on the Supernatural VR podcast!), I tried adding an extra filter. Now, I’m cross-referencing to ignore any workouts with a higher ID than the Quick Hit workout ID. This helps narrow down the list, but I’m still not fully satisfied. There are cases where songs match, but they don’t necessarily match by coach or difficulty level, making it even trickier to determine the exact workout that each Quick Hit is derived from.
This project pushed my Excel formulas to the max, and I’m wondering if there’s a better approach. I’ve included the workbook in the description so you can download it and give it a try. If you have ideas on how to streamline this process or know of a simpler solution, let me know! I’d love to feature a new solution on an upcoming podcast.
Check out the Supernatural VR podcast episode featuring me as a guest, and download the workbook below. Let’s work together to solve this data mystery in Excel!
Buy Bill Jelen's latest Excel book: MrExcel 2024 Igniting Excel
Table of Contents
(0:00) Looking for a better solution today
(0:15) Supernatural VR workouts and Data
(1:17) Trailer For The Love Of The Map
(2:19) Problem Description
(3:04) Finding both songs for each Quick Hit
(3:52) XLOOKUP with Concatenated Key & Concatenated Lookup Vector
(4:42) Data for other lookups with more tracks
(5:17) Matches for Song with FILTER, SORT, ARRAYTOTEXT
(6:23) Which IDs are in both lists using LET, TEXTSPLIT and XLOOKUP
(7:30) Results are finding multiple matches
(8:23) Discussion with Marla Bright
(12:43) Formula changes to throw out later workouts
(13:54) Consolidating 3 formulas into 1
(14:10) Download the data
(14:21) Join the membership
(14:40) Thanks for watching
This video answers these search terms:
Excel XLOOKUP for data matching
Excel FILTER formula tutorial
Supernatural VR workout data analysis in Excel
Quick Hits vs. long workouts data problem
Advanced Excel formula techniques for matching lists
Using TEXTJOIN and LET in Excel
Identifying duplicates in multiple Excel lists
Airtable to Excel data workflow
Data cross-referencing with Excel functions
Solving complex data problems with Excel
Transcript of the video:
This is one of those days where I have a problem.
And I know one solution. But my solution can't possibly be the best solution. I'm hoping some of you can help.
MrExcel podcast episode 2661, which kits have both parts in them?
For the last year, ever since my cardiologist had a serious talk with me.
I've been using a workout program in a VR headset called Supernatural.
I've had amazing results and the doctor's super happy.
Over at Supernatural, there's a community of a hundred thousand tech savvy people playing this game every day. I have a workout crew that I work out with most mornings. And I find that they're data people.
Either they love data. Or they have data questions.
And frankly, nothing makes me happier than a data question coming up during a workout that I can go use my Excel skills to solve. Now, today, Wednesday, November 13th, I'm super excited to be on the leading podcast about Supernatural VR.
Marla had me as her guest. And we just had a lot of fun talking about a lot of things including Excel. Let me show you the trailer.
It'll take about a minute. There's a lot of behind the scenes, MrExcel stuff there. And maybe you'd be interested in watching the whole episode. I'll put the link in the in the top right hand corner. But if you're just here to help me with my data problem, skip ahead 59 seconds. Thanks.
In the morning crew. They know that if you ask me a data question.
I love data questions. I love to go get the Airtable.
I love to do all kinds of crazy stuff in Excel. [ Marla: ] I'm coming for your group.
All you had to say was you have a club and I'm down.
I'm coming. [ Bill: ] We are about 12 miles away from the launch pads right there. I want the spreadsheet of all of Coach Mark's dad jokes. I want to know… [ Marla: ] that would be incredible. [ Bill: Where is that joke, right?
That was a hilarious joke. I want to go back and listen to that.
I'm going to whisper this because my friends already know I'm thinking it.
They can see my face. They know what I'm thinking.
Marla, “Skip Song on the Korn song! [ Marla: ] No, I can’t!” [ Bill: ] No, don't tell anyone. Just don't tell anyone.
They won’t know. I have a photo of myself at Stonehenge, right?
So even though when I was at Stonehenge, I had no idea what Supernatural was.
Where she got me was: ‘they have a spreadsheet!’ I'm like, what? I could be on the spreadsheet?
[ Marla: ] Exactly - that's all it takes. [ Bill: ] Okay, so here's the deal.
There's 584 workouts in Supernatural that are called Quick Hits.
They are two songs each. Just for when you don't have time for a full workout. And then there's another list of 2,345 workouts that are a little longer 3, 4, 5, 6 up to 12 songs.
The theory is that every Quick Hit is a subset of one of the longer workouts.
They took two songs out of that workout and made it into the Quick Hit.
Which means that if you get to do a Quick Hit. And you really like that music and that choreography. Then you might like the longer workout.
So can we figure out from these two lists. If I give you a Quick Hit, which longer workout has both songs from that workout? That's the data problem.
Okay, let's talk about the data that we have. Now, this comes from something called the Community Airtable. Someone else creates that.
I export it to CSV, bring it into Excel. What we have over here is a workout id.
That's what I'm going to be working with primarily.
So here's the 581 Quick Hit workouts. Each of these have two songs.
And in order to get that, there's a second table in the AirTable.
That has, here's the Workout ID. Track one, track two.
And right here the Artist | Song is what it takes to get uniqueness out of this.
So I'm looking at for each Workout ID in column C. What's the artist and song for Song one and song two? Alright, now this is a great use of XLOOKOUP.
Something that I haven't covered on the podcast before.
So in this formula I'm joining the workout ID and a pipe with the number one.
And then where am I looking up? I'm looking up the Quick Hits catalog, column C.
All of those joined with a track number from column B.
Let's go take a look at the Quick Hits catalog again.
So workout id plus a pipe plus the track number gives me something that I can look up into.
And then I'm returning column K from the Quick Hits catalog, which is the artist and song.
Now, the reason that I'm getting song one and song two is because here I've concatenated a 1.
And over here I've concatenated a 2, right? So it's a great use of XLOOKUP and it gets me song one and song two. Now the next thing we have is we have basically the Quick Hits catalog, but for everything that's not a Quick Hit.
So you see that like this one, there's four tracks in workout ID 157.
But here there's six tracks. And I have the same basic information over there in column K with the artist and song. So the question is, for all of these 500 plus items. That have Song 1 and Song 2.
Go find which items in this list have the same two songs in column K.
Now I'm pretty happy with this formula here. This formula at its base is using the FILTER function. I'm filtering all of the Workout IDs in column C.
Where the artist and title in column K is equal to I4.
That's returning multiple items. So I'm sorting them ascending.
And then using ARRAYTOTEXT in order to get a comma-delimited list of those.
Over here, the only difference is that I'm checking to see if it's equal to J4.
So now here's a list of all the workout IDs that have that first song Sugar We're Going Down by Fallout Boy. And here's a list of all of the workout IDs that have the second song. I Write Sins Not Tragedies by Panic at the Disco.
From here, it sounds really simple. I need to find the items that are in both.
So it looks like workout ID 304 is in both. But then very quickly I noticed that 2959 is in both. That's not something I expected.
I didn't expect that there'd be two matches. So I need a way.
Faster than eyeballing it. To go through and figure this out.
And that's where I came up with this formula that I believe cannot be the most effective formula.
Let's start on the inside. So first off, inside the LET.
I'm TEXTSPLIT-ting out column K and storing that in a variable called A.
And then, I'm TEXTSPLIT-ting out column L. And storing that in a variable called B.
You can kind of picture A is a list of everything that has Song 1.
B is a list of everything that has Song 2. I wanted to do a COUNTIFS here.
But we run into this problem in the past. That the first argument in COUNTIFS has to be a range. Not a formula.
So I did an XLOOKUP. Go look up all of the workouts that have song A.
In all of the workouts that have song B. If you find it, then return the Workout ID.
Otherwise return Quote Quote. So what this is going to give me is a whole bunch of things that are probably quote quote. But then beautifully here, the TEXTJOIN function.
I say put each of these with a comma in between. And ignore Empty.
That Ignore empty is really useful. Because it ignores the quote quote.
Alright, and this is like I'm home free. Let's go back and tell Marla that we have the list. But we don't have the list.
This one is exactly what I expected it would be. There was exactly one workout that both of those songs are in. And that workout must have been the parent workout. From which this Quick Hit was selected.
But then, well first off, there's some that have no matches.
What's up with that? I didn't think that's how it would work.
And then the thing that really confuses me. Is there's sometimes like here.
We have Take a Look around by Limp Bizkit and Duality by Slip Knot.
Is appearing, both of those songs are appearing in three different workouts.
Shoot, okay, now this is workout ID 478. So maybe in theory 690 is out because 690 didn't exist before 498. So we would throw out anything that's higher maybe. But I'm not an expert on this.
Let's see if we can bring in Marla. Hey, this is amazing.
I reached out to Marla. And I said, Hey Marla, any chance you can come on my podcast? And look who's here - It's Marla.
[ Marla Bright ] Hi [ Bill: ] Marla. these are all of my Excel friends watching today. And I gave them a really hard Excel challenge. And I solved it.
But now I've hit a wall. And I need your knowledge of Supernatural.
For everyone watching Marla is awesome with Supernatural.
Okay, so here's the premise. We have like 500 some Quick Hit workouts in Supernatural that are two songs each. [ Marla: ] Yes.
[ Bill: ] And in theory, if you find a Quick Hit that you like.
The question is: can we go find the longer workout that that Quick Hit was taken from?
So maybe there's a six track workout that has those same two songs and we want to go find it.
So we've been working on Excel formulas to do that.
Now first off, the first question I have is that a valid premise that the Quick Hit is always a subset of the longer workout. [ Marla ] A hundred percent.
Unless it is a holiday. On holidays, they occasionally release Quick Hits of brand new songs. It is a whole new workout unto itself.
The Shamrock workouts. Holiday, I believe 2023.
Those are original. They don't have a full workout they come from.
[ Bill ] Okay, so that's good. That means if I find no match, it's possible.
[ Marla ] It's possible. Okay, so I have my formula working here.
It's not the best formula ever. It's very convoluted.
But I have what's working. And the thing that surprised me is I find a lot that match. And I'm happy about those.
But then I found a couple where there's two matches.
Here's an example, there's a workout 558. It's Frankie Valle and the Four Seasons.
And then Richie Valens are both in that workout. And I found two matching workouts.
Not just one, but two other workouts that have that exact same pair of songs.
And in this case, remember it is Quick Hit 558. The matches are ID 492 and 3614.
So you would think that 492, the earlier one must be the winner.
[ Marla: ] Correct. [ Bill ] Okay, so I'm okay with that.
I can figure that out. But then I find….
Here's one where it's Workout 488. It's Mike Posner singing, “I Took a Pill in Ibiza”. And then Clean Bandit and Julia Michael singing, “I Miss You”. And those two songs are in THREE other workouts.
[ Marla: ] Okay. The workout IDs 313, 358, 388, all predate 488.
So now I've got three possibilities. If I had the artwork, that would solve it.
But I don't have the artwork. What else might I be able to use to try and narrow this down? Is it safe to assume that the location is the same? [ Marla ] No, definitely not.
They change up the location from the original workout to the Quick Hit version.
[ Bill: ] Okay. How about the coach?
What if the original song was coached by Coach Dwana?
Will the Quick Hit always be coached by Coach Dwana as well?
[ Marla ] No sir. They change up the coaches as well.
The only exception for that is the Mad House. Coach Mark does coach the Quick Hit version of Welcome to the Madhouse, the two songs in there. That is the only exception to the rule.
[ Bill: ] Okay, now this is data I don't have. The actual choreography of the targets.
Boom, boom, boom, boom, boom coming at you. Will that be the same from one to the next?
Typically a Quick Hit is made from the same songs and choreography as a full workout.
However, if it's older, they can be a little updated.
What they call a reshuffle. But if it's in the Quick Hit, they would've pushed it out to the original. If that makes sense.
So that could be helpful, maybe [ Bill: ] It could.
Unfortunately that's data that I do not have. That data's not in the Airtable.
So yeah, too bad, so sad. I think I'm probably just going to probably go with the earliest one that I find. Any other thoughts on that?
[ Marla: ] No, I have no other thoughts, but I feel like your audience of Big Brained Excel peeps might know how to figure it out. [ Bill ] Marla, they show me up all the time, all the time. They always have a better solution.
So we'll see what they come up with. Alright, that's good.
Marla, thanks for taking time out of your day. And I told everyone that I'm going to be on your episode today. And I'm sending people out to watch, so hopefully they'll get to see a lot of cool stuff. [ Marla: ] Appreciate that.
[ Bill: ] Yeah, thanks Marla. Thanks for having me on your show.
And thanks for coming on my show. I appreciate it.
[ Marla: ] Yes, thanks for having me. [ Bill: ] Oh, okay.
After talking to Marla. The best that I can see is if the workout ID is 438. Then any of these matching things that are greater than 438. Should be thrown out.
That 2959 shouldn't be considered. So my After Marla sheet here.
I made a couple of changes. I changed the FILTER to have two sets of criteria.
First off, is this equal to the right artist and song title?
And then also is the Workout Id less than the Workout ID over in column B.
I did that here and here, which gets me to this list.
There are some that just don't match now. But there's more that have just a single match.
For the ones that have two matches. I went through and compared various fields.
And as Marla suggested, the coach can change. Or… both of these will be a match.
Both Coach Leanne, both Medium, both Flow. So it's just not conclusive.
And I hate that it's not conclusive. Okay.
Now one thing that would make this formula simpler.
Is rather than doing TEXTTOARRAY here. And then splitting it back out here.
Right in this formula, just take the formulas that previously were in those two columns.
And just build them right into this. Although, a lot harder to explain.
Down in the YouTube description, you can download this workbook.
If you look at this and say, Bill, you're just crazy.
There is something far easier than this. Feel free to download it and let me know and we'll cover that on a future podcast. Hey, what’s this?
A new Join button. Yes, there is a membership section.
There are special loyalty badges, emojis. Members-only videos – one-off videos that I did for specific people. Or, when I screw something up and there is an outtake, it shows you how to recover from the outtake.
Member shout-outs and a live stream every quarter, at least.
Check it out. Thanks.
Huge shout out to Marla for appearing on this podcast and for hosting me on hers.
I want to thank you for stopping by. We'll see you next time for another net cast from MrExcel.
And I know one solution. But my solution can't possibly be the best solution. I'm hoping some of you can help.
MrExcel podcast episode 2661, which kits have both parts in them?
For the last year, ever since my cardiologist had a serious talk with me.
I've been using a workout program in a VR headset called Supernatural.
I've had amazing results and the doctor's super happy.
Over at Supernatural, there's a community of a hundred thousand tech savvy people playing this game every day. I have a workout crew that I work out with most mornings. And I find that they're data people.
Either they love data. Or they have data questions.
And frankly, nothing makes me happier than a data question coming up during a workout that I can go use my Excel skills to solve. Now, today, Wednesday, November 13th, I'm super excited to be on the leading podcast about Supernatural VR.
Marla had me as her guest. And we just had a lot of fun talking about a lot of things including Excel. Let me show you the trailer.
It'll take about a minute. There's a lot of behind the scenes, MrExcel stuff there. And maybe you'd be interested in watching the whole episode. I'll put the link in the in the top right hand corner. But if you're just here to help me with my data problem, skip ahead 59 seconds. Thanks.
In the morning crew. They know that if you ask me a data question.
I love data questions. I love to go get the Airtable.
I love to do all kinds of crazy stuff in Excel. [ Marla: ] I'm coming for your group.
All you had to say was you have a club and I'm down.
I'm coming. [ Bill: ] We are about 12 miles away from the launch pads right there. I want the spreadsheet of all of Coach Mark's dad jokes. I want to know… [ Marla: ] that would be incredible. [ Bill: Where is that joke, right?
That was a hilarious joke. I want to go back and listen to that.
I'm going to whisper this because my friends already know I'm thinking it.
They can see my face. They know what I'm thinking.
Marla, “Skip Song on the Korn song! [ Marla: ] No, I can’t!” [ Bill: ] No, don't tell anyone. Just don't tell anyone.
They won’t know. I have a photo of myself at Stonehenge, right?
So even though when I was at Stonehenge, I had no idea what Supernatural was.
Where she got me was: ‘they have a spreadsheet!’ I'm like, what? I could be on the spreadsheet?
[ Marla: ] Exactly - that's all it takes. [ Bill: ] Okay, so here's the deal.
There's 584 workouts in Supernatural that are called Quick Hits.
They are two songs each. Just for when you don't have time for a full workout. And then there's another list of 2,345 workouts that are a little longer 3, 4, 5, 6 up to 12 songs.
The theory is that every Quick Hit is a subset of one of the longer workouts.
They took two songs out of that workout and made it into the Quick Hit.
Which means that if you get to do a Quick Hit. And you really like that music and that choreography. Then you might like the longer workout.
So can we figure out from these two lists. If I give you a Quick Hit, which longer workout has both songs from that workout? That's the data problem.
Okay, let's talk about the data that we have. Now, this comes from something called the Community Airtable. Someone else creates that.
I export it to CSV, bring it into Excel. What we have over here is a workout id.
That's what I'm going to be working with primarily.
So here's the 581 Quick Hit workouts. Each of these have two songs.
And in order to get that, there's a second table in the AirTable.
That has, here's the Workout ID. Track one, track two.
And right here the Artist | Song is what it takes to get uniqueness out of this.
So I'm looking at for each Workout ID in column C. What's the artist and song for Song one and song two? Alright, now this is a great use of XLOOKOUP.
Something that I haven't covered on the podcast before.
So in this formula I'm joining the workout ID and a pipe with the number one.
And then where am I looking up? I'm looking up the Quick Hits catalog, column C.
All of those joined with a track number from column B.
Let's go take a look at the Quick Hits catalog again.
So workout id plus a pipe plus the track number gives me something that I can look up into.
And then I'm returning column K from the Quick Hits catalog, which is the artist and song.
Now, the reason that I'm getting song one and song two is because here I've concatenated a 1.
And over here I've concatenated a 2, right? So it's a great use of XLOOKUP and it gets me song one and song two. Now the next thing we have is we have basically the Quick Hits catalog, but for everything that's not a Quick Hit.
So you see that like this one, there's four tracks in workout ID 157.
But here there's six tracks. And I have the same basic information over there in column K with the artist and song. So the question is, for all of these 500 plus items. That have Song 1 and Song 2.
Go find which items in this list have the same two songs in column K.
Now I'm pretty happy with this formula here. This formula at its base is using the FILTER function. I'm filtering all of the Workout IDs in column C.
Where the artist and title in column K is equal to I4.
That's returning multiple items. So I'm sorting them ascending.
And then using ARRAYTOTEXT in order to get a comma-delimited list of those.
Over here, the only difference is that I'm checking to see if it's equal to J4.
So now here's a list of all the workout IDs that have that first song Sugar We're Going Down by Fallout Boy. And here's a list of all of the workout IDs that have the second song. I Write Sins Not Tragedies by Panic at the Disco.
From here, it sounds really simple. I need to find the items that are in both.
So it looks like workout ID 304 is in both. But then very quickly I noticed that 2959 is in both. That's not something I expected.
I didn't expect that there'd be two matches. So I need a way.
Faster than eyeballing it. To go through and figure this out.
And that's where I came up with this formula that I believe cannot be the most effective formula.
Let's start on the inside. So first off, inside the LET.
I'm TEXTSPLIT-ting out column K and storing that in a variable called A.
And then, I'm TEXTSPLIT-ting out column L. And storing that in a variable called B.
You can kind of picture A is a list of everything that has Song 1.
B is a list of everything that has Song 2. I wanted to do a COUNTIFS here.
But we run into this problem in the past. That the first argument in COUNTIFS has to be a range. Not a formula.
So I did an XLOOKUP. Go look up all of the workouts that have song A.
In all of the workouts that have song B. If you find it, then return the Workout ID.
Otherwise return Quote Quote. So what this is going to give me is a whole bunch of things that are probably quote quote. But then beautifully here, the TEXTJOIN function.
I say put each of these with a comma in between. And ignore Empty.
That Ignore empty is really useful. Because it ignores the quote quote.
Alright, and this is like I'm home free. Let's go back and tell Marla that we have the list. But we don't have the list.
This one is exactly what I expected it would be. There was exactly one workout that both of those songs are in. And that workout must have been the parent workout. From which this Quick Hit was selected.
But then, well first off, there's some that have no matches.
What's up with that? I didn't think that's how it would work.
And then the thing that really confuses me. Is there's sometimes like here.
We have Take a Look around by Limp Bizkit and Duality by Slip Knot.
Is appearing, both of those songs are appearing in three different workouts.
Shoot, okay, now this is workout ID 478. So maybe in theory 690 is out because 690 didn't exist before 498. So we would throw out anything that's higher maybe. But I'm not an expert on this.
Let's see if we can bring in Marla. Hey, this is amazing.
I reached out to Marla. And I said, Hey Marla, any chance you can come on my podcast? And look who's here - It's Marla.
[ Marla Bright ] Hi [ Bill: ] Marla. these are all of my Excel friends watching today. And I gave them a really hard Excel challenge. And I solved it.
But now I've hit a wall. And I need your knowledge of Supernatural.
For everyone watching Marla is awesome with Supernatural.
Okay, so here's the premise. We have like 500 some Quick Hit workouts in Supernatural that are two songs each. [ Marla: ] Yes.
[ Bill: ] And in theory, if you find a Quick Hit that you like.
The question is: can we go find the longer workout that that Quick Hit was taken from?
So maybe there's a six track workout that has those same two songs and we want to go find it.
So we've been working on Excel formulas to do that.
Now first off, the first question I have is that a valid premise that the Quick Hit is always a subset of the longer workout. [ Marla ] A hundred percent.
Unless it is a holiday. On holidays, they occasionally release Quick Hits of brand new songs. It is a whole new workout unto itself.
The Shamrock workouts. Holiday, I believe 2023.
Those are original. They don't have a full workout they come from.
[ Bill ] Okay, so that's good. That means if I find no match, it's possible.
[ Marla ] It's possible. Okay, so I have my formula working here.
It's not the best formula ever. It's very convoluted.
But I have what's working. And the thing that surprised me is I find a lot that match. And I'm happy about those.
But then I found a couple where there's two matches.
Here's an example, there's a workout 558. It's Frankie Valle and the Four Seasons.
And then Richie Valens are both in that workout. And I found two matching workouts.
Not just one, but two other workouts that have that exact same pair of songs.
And in this case, remember it is Quick Hit 558. The matches are ID 492 and 3614.
So you would think that 492, the earlier one must be the winner.
[ Marla: ] Correct. [ Bill ] Okay, so I'm okay with that.
I can figure that out. But then I find….
Here's one where it's Workout 488. It's Mike Posner singing, “I Took a Pill in Ibiza”. And then Clean Bandit and Julia Michael singing, “I Miss You”. And those two songs are in THREE other workouts.
[ Marla: ] Okay. The workout IDs 313, 358, 388, all predate 488.
So now I've got three possibilities. If I had the artwork, that would solve it.
But I don't have the artwork. What else might I be able to use to try and narrow this down? Is it safe to assume that the location is the same? [ Marla ] No, definitely not.
They change up the location from the original workout to the Quick Hit version.
[ Bill: ] Okay. How about the coach?
What if the original song was coached by Coach Dwana?
Will the Quick Hit always be coached by Coach Dwana as well?
[ Marla ] No sir. They change up the coaches as well.
The only exception for that is the Mad House. Coach Mark does coach the Quick Hit version of Welcome to the Madhouse, the two songs in there. That is the only exception to the rule.
[ Bill: ] Okay, now this is data I don't have. The actual choreography of the targets.
Boom, boom, boom, boom, boom coming at you. Will that be the same from one to the next?
Typically a Quick Hit is made from the same songs and choreography as a full workout.
However, if it's older, they can be a little updated.
What they call a reshuffle. But if it's in the Quick Hit, they would've pushed it out to the original. If that makes sense.
So that could be helpful, maybe [ Bill: ] It could.
Unfortunately that's data that I do not have. That data's not in the Airtable.
So yeah, too bad, so sad. I think I'm probably just going to probably go with the earliest one that I find. Any other thoughts on that?
[ Marla: ] No, I have no other thoughts, but I feel like your audience of Big Brained Excel peeps might know how to figure it out. [ Bill ] Marla, they show me up all the time, all the time. They always have a better solution.
So we'll see what they come up with. Alright, that's good.
Marla, thanks for taking time out of your day. And I told everyone that I'm going to be on your episode today. And I'm sending people out to watch, so hopefully they'll get to see a lot of cool stuff. [ Marla: ] Appreciate that.
[ Bill: ] Yeah, thanks Marla. Thanks for having me on your show.
And thanks for coming on my show. I appreciate it.
[ Marla: ] Yes, thanks for having me. [ Bill: ] Oh, okay.
After talking to Marla. The best that I can see is if the workout ID is 438. Then any of these matching things that are greater than 438. Should be thrown out.
That 2959 shouldn't be considered. So my After Marla sheet here.
I made a couple of changes. I changed the FILTER to have two sets of criteria.
First off, is this equal to the right artist and song title?
And then also is the Workout Id less than the Workout ID over in column B.
I did that here and here, which gets me to this list.
There are some that just don't match now. But there's more that have just a single match.
For the ones that have two matches. I went through and compared various fields.
And as Marla suggested, the coach can change. Or… both of these will be a match.
Both Coach Leanne, both Medium, both Flow. So it's just not conclusive.
And I hate that it's not conclusive. Okay.
Now one thing that would make this formula simpler.
Is rather than doing TEXTTOARRAY here. And then splitting it back out here.
Right in this formula, just take the formulas that previously were in those two columns.
And just build them right into this. Although, a lot harder to explain.
Down in the YouTube description, you can download this workbook.
If you look at this and say, Bill, you're just crazy.
There is something far easier than this. Feel free to download it and let me know and we'll cover that on a future podcast. Hey, what’s this?
A new Join button. Yes, there is a membership section.
There are special loyalty badges, emojis. Members-only videos – one-off videos that I did for specific people. Or, when I screw something up and there is an outtake, it shows you how to recover from the outtake.
Member shout-outs and a live stream every quarter, at least.
Check it out. Thanks.
Huge shout out to Marla for appearing on this podcast and for hosting me on hers.
I want to thank you for stopping by. We'll see you next time for another net cast from MrExcel.
