Bill "MrExcel" Jelen and Mike "ExcelisFun" Girvin compare data in columns to discern whether 'same' figures exist in both columns, by row.
Bill goes the VBA Route and Mike chooses to use an Amazing Array Formula to get the query solved.
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Bill goes the VBA Route and Mike chooses to use an Amazing Array Formula to get the query solved.
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
Hey welcome back. It's another dueling Excel podcast.
I'm Bill Jelen from MrExcel. Will be joined by Mike Gervin from Excel Is Fun.
This is our Episode 98 - Any of These in Those?
Hey welcome back. This is a heck of a question here, so we have the question.
Any number that's found in C2 is it also found in E2?
So any number in C2 also found in E2. Oh my gosh. This is crazy.
I could go pull out the book Excel Gurus Gone Wild.
I remember there's some crazy array formula there but I sure as I can't remember it would expand all these and break them into words and do this and do that I don't know maybe Michael show us that but for me this is straight VBA so Alt + F11 Insert Module and then type this function.
GetMatch (LookFor, LookIn) So we're gonna pass two arguments to this function the name of the function GetMatch that has to be returned by the function.
So I initialize that to 0 and we start looking from 1 until the end and look to see if we find the - Now that's gonna miss the last pair of numbers.
So I'm going to come back and get that one after the for Next loop here and check to see if this string this 2 digit or 3 digit or 5 digit or 50 digit number is found in the big string that LookIn string.
If it is then I take the old GetMatch which was nothing.
Add this string and then add a comma and a space again get rid of that at the end.
Loop through look for all of the digits and then then finally come back and get the last digit the one that theoretically doesn't have a dash after it.
If we found anything then I want to use the left of the answer.
The total length -2 to get rid of that last comma space.
Last comma space so let's give this a try.
I'll come back here.
=GetMatch of this comma there and 2, 11, 89.
Now I chose to put commas in between I don't know what the heck's are gonna do with this maybe you could put I don't know.
I don't know.
I'm hoping that I might get to use Text to Columns on the answer here.
But I'm not sure where they were headed when they sent in the question =GetMatch of this comma that and same answers. It doesn't sort them. It just gives them to you in the order that they're found here.
I don't know if that's good or bad. All right Mike.
I'm hoping that you pull out some amazing array formula here, and then get the point because I had to cheat and go with VBA.
Thanks MrExcel. Man can't match VBA, but what are you talking about cheating because we had to use VBA?
It's actually the reverse. I'm the one that's goign to have to cheat and use a formula because I don't know VBA.
All right so that means you get the point. All right.
So we have two strings here. I'm actually going to just extract each one of these numbers so I can visually see them here and actually this situation the characters will always be 2 characters long.
They'll always be separated by a - and they'll always be 1 2 3 6 of them All right, so I'm going to use the MID function to extract.
But actually before I do that what I really want is, I want a number increment because I can see the first number starts at position 1, the second number starts at 4 and 7 10 13 16 so I need to inside of a formula ain't have a number increment 1 4 7 etc.
So I'm going to start with the rows.
I'm in B5 so I'm gonna type $B$5:B5 This will give me the numbers 1 2 3 4 5 6 as I copy down.
Not quite what I want F2 and that selected range. Now what if I multiply this by 3?
This will give me 3 6 9 while the increment is correct 1 2 4 is 3, but I really want to start at 1 so I'll simply subtract 2.
Ctrl + Enter to repopulate, so there's then the starting position of the number I want to extract.
F2 and why don't I just put this inside the MID. There's the text.
F4 to lock it, that's the starting number each time comma and the number of characters.
Each character's always too long.
So there Ctrl + Enter to repopulate our edited formula. All right.
So that in essence are the individual numbers from here that I need to lookup and find if it's here.
Now I'm going to think of this like a LOOKUP, right.
But the first trick is if I say hey some LOOKUP function lookup this within this.
This is a single text string and I really want 1 to 6 individual items.
So I'm going to use the MID function to create a little lookup table with the 6 items here.
So the text is right here.
Comma now starting number in this example over here, we had a single starting number, but here we want to give it all of them and we're going to use array syntax of curly bracket 1 comma 4 comma 7 comma 10 comma 13 comma 16. Now if these were varying lengths, then this wouldn't work.
But they're not so that'll work fine there. There's another good aspect of this array constant, means we type the array in array constants.
Usually do not require Ctrl+Shift+ Enter so even though this is an array formula, the array constant does not require Ctrl + Shift + Enter.
Now each character is still going to be 2 here.
So now if I highlight this it'll extract all of them.
F9 So there we have it. That's our lookup range in essence.
Ctrl + Z Now I'm going to use the MATCH which is a lookup function.
I'm going to say hey MATCH lookup that.
It will find the position within that lookup table. Comma I'm going to do exact MATCH.
So now this will deliver a number. Now I can see right now. I'm going to need to lock this one.
Now watch this. I'm going to Ctrl + Enter.
I didn't Ctrl + Shift + Enter. I don't see any curly brackets. They're not needed here.
I double click and send it down.
Now you can leave it like that or if you want to see a TRUE where it's a number, then you just use =ISNUMBER and it will give us a TRUE. So this, this formula is saying is anything in here over there, and we have three TRUEs.
I'm going to just copy this and paste it. Actually I'm going to copy both of these.
Ctrl + C Ctrl + V This one will work if I put it in edit mode and drag this over here.
And I think I have to do the same thing for this one.
Okay, so this formula is saying is that one but this is the incorrect range.
We want to ask are any of the digits we extracted from here, over there. So that ought to work.
Ctrl + Enter Double click and send it down.
Alright.
Throw it back to MrExcel.
Mike that's crazy, that's crazy. First of all I love the increment over here right the getting an increment by 3 and then just subtracting 2 to get it back to what you wanted.
Once you had these numbers, I really thought you're going to do an =FIND Look for this two-digit number within this string up here and you know ofcourse since it's separated by dashes that would tell us whether it was found or not, but you went over and above with the generating that array, and then putting it right inside the MATCH or I guess in theory the VLOOKUP matches. The way to go that is incredibly cool.So 2 very different ways to solve the problem.
And I want to thank everyone for stopping by.
Will see you next week for another dueling excel podcast from MrExcel and Excel Is Fun.
I'm Bill Jelen from MrExcel. Will be joined by Mike Gervin from Excel Is Fun.
This is our Episode 98 - Any of These in Those?
Hey welcome back. This is a heck of a question here, so we have the question.
Any number that's found in C2 is it also found in E2?
So any number in C2 also found in E2. Oh my gosh. This is crazy.
I could go pull out the book Excel Gurus Gone Wild.
I remember there's some crazy array formula there but I sure as I can't remember it would expand all these and break them into words and do this and do that I don't know maybe Michael show us that but for me this is straight VBA so Alt + F11 Insert Module and then type this function.
GetMatch (LookFor, LookIn) So we're gonna pass two arguments to this function the name of the function GetMatch that has to be returned by the function.
So I initialize that to 0 and we start looking from 1 until the end and look to see if we find the - Now that's gonna miss the last pair of numbers.
So I'm going to come back and get that one after the for Next loop here and check to see if this string this 2 digit or 3 digit or 5 digit or 50 digit number is found in the big string that LookIn string.
If it is then I take the old GetMatch which was nothing.
Add this string and then add a comma and a space again get rid of that at the end.
Loop through look for all of the digits and then then finally come back and get the last digit the one that theoretically doesn't have a dash after it.
If we found anything then I want to use the left of the answer.
The total length -2 to get rid of that last comma space.
Last comma space so let's give this a try.
I'll come back here.
=GetMatch of this comma there and 2, 11, 89.
Now I chose to put commas in between I don't know what the heck's are gonna do with this maybe you could put I don't know.
I don't know.
I'm hoping that I might get to use Text to Columns on the answer here.
But I'm not sure where they were headed when they sent in the question =GetMatch of this comma that and same answers. It doesn't sort them. It just gives them to you in the order that they're found here.
I don't know if that's good or bad. All right Mike.
I'm hoping that you pull out some amazing array formula here, and then get the point because I had to cheat and go with VBA.
Thanks MrExcel. Man can't match VBA, but what are you talking about cheating because we had to use VBA?
It's actually the reverse. I'm the one that's goign to have to cheat and use a formula because I don't know VBA.
All right so that means you get the point. All right.
So we have two strings here. I'm actually going to just extract each one of these numbers so I can visually see them here and actually this situation the characters will always be 2 characters long.
They'll always be separated by a - and they'll always be 1 2 3 6 of them All right, so I'm going to use the MID function to extract.
But actually before I do that what I really want is, I want a number increment because I can see the first number starts at position 1, the second number starts at 4 and 7 10 13 16 so I need to inside of a formula ain't have a number increment 1 4 7 etc.
So I'm going to start with the rows.
I'm in B5 so I'm gonna type $B$5:B5 This will give me the numbers 1 2 3 4 5 6 as I copy down.
Not quite what I want F2 and that selected range. Now what if I multiply this by 3?
This will give me 3 6 9 while the increment is correct 1 2 4 is 3, but I really want to start at 1 so I'll simply subtract 2.
Ctrl + Enter to repopulate, so there's then the starting position of the number I want to extract.
F2 and why don't I just put this inside the MID. There's the text.
F4 to lock it, that's the starting number each time comma and the number of characters.
Each character's always too long.
So there Ctrl + Enter to repopulate our edited formula. All right.
So that in essence are the individual numbers from here that I need to lookup and find if it's here.
Now I'm going to think of this like a LOOKUP, right.
But the first trick is if I say hey some LOOKUP function lookup this within this.
This is a single text string and I really want 1 to 6 individual items.
So I'm going to use the MID function to create a little lookup table with the 6 items here.
So the text is right here.
Comma now starting number in this example over here, we had a single starting number, but here we want to give it all of them and we're going to use array syntax of curly bracket 1 comma 4 comma 7 comma 10 comma 13 comma 16. Now if these were varying lengths, then this wouldn't work.
But they're not so that'll work fine there. There's another good aspect of this array constant, means we type the array in array constants.
Usually do not require Ctrl+Shift+ Enter so even though this is an array formula, the array constant does not require Ctrl + Shift + Enter.
Now each character is still going to be 2 here.
So now if I highlight this it'll extract all of them.
F9 So there we have it. That's our lookup range in essence.
Ctrl + Z Now I'm going to use the MATCH which is a lookup function.
I'm going to say hey MATCH lookup that.
It will find the position within that lookup table. Comma I'm going to do exact MATCH.
So now this will deliver a number. Now I can see right now. I'm going to need to lock this one.
Now watch this. I'm going to Ctrl + Enter.
I didn't Ctrl + Shift + Enter. I don't see any curly brackets. They're not needed here.
I double click and send it down.
Now you can leave it like that or if you want to see a TRUE where it's a number, then you just use =ISNUMBER and it will give us a TRUE. So this, this formula is saying is anything in here over there, and we have three TRUEs.
I'm going to just copy this and paste it. Actually I'm going to copy both of these.
Ctrl + C Ctrl + V This one will work if I put it in edit mode and drag this over here.
And I think I have to do the same thing for this one.
Okay, so this formula is saying is that one but this is the incorrect range.
We want to ask are any of the digits we extracted from here, over there. So that ought to work.
Ctrl + Enter Double click and send it down.
Alright.
Throw it back to MrExcel.
Mike that's crazy, that's crazy. First of all I love the increment over here right the getting an increment by 3 and then just subtracting 2 to get it back to what you wanted.
Once you had these numbers, I really thought you're going to do an =FIND Look for this two-digit number within this string up here and you know ofcourse since it's separated by dashes that would tell us whether it was found or not, but you went over and above with the generating that array, and then putting it right inside the MATCH or I guess in theory the VLOOKUP matches. The way to go that is incredibly cool.So 2 very different ways to solve the problem.
And I want to thank everyone for stopping by.
Will see you next week for another dueling excel podcast from MrExcel and Excel Is Fun.
