In today's Dueling Excel Podcast [Episode #1283] Mike "ExcelIsFun" Girvin and Bill "MrExcel" Jelen look at a data set and then attempt to find all of the records that contain the word "Car". Using Excel VBA [Macro] and Formula methods, Mike and Bill produce the desired results - see how they do it in Episode #1283!
Transcript of the video:
Dueling Excel podcast.
Episode 54, Approximate Multiple Lookup.
This is a crazy one he has some lookup values here Car, Truck, Jeep and he wants to look through column A and find all of the cells that contain the word car.
I have two solutions to this both of them are Macros, the Loop Macro have my self selected their press the Loop Macro and there is answer.
Cool and then the Filter Macro, I actually see the screen flash a little bit there, but you get the get the same answer.
Let's take a look at those two Macros, Alt+F11, already have those written, the first one the Loop Macro, fairly basic Macro if you take any course in Excel, VBA, Macros, you're going to get to these loops figure out how many rows we have today for each cell in the selection.
We look for that cell that's the variable look for and we're going to go from Row 2 down to the last row in this case 2 to 10, over in column A, we're going to use the in string function, now this is a VBA function.
it's not a Excel function, in string looks for a certain value in another string that's greater than 0 that means that it's found and once it's found we go to our current cell offset 0 rows, one column over and write the value and then this counter is a variable I increment that by one for anyone who is new to Macros, but I like this one do filter on selected let's run this one step at a time and you'll see how it's going.
So, for each cell over there in column C in our selection I build a variable called look for and that look for is what we're looking for Car, with an equal sign and asterisk before it and then another asterisk afterwards.
So, it builds actually a little criteria there and then I'm going to do this AutoFilter, the AutoFilter says hey, we're going to look through the first column that's column a the criteria is in the cases car.
So, I run that line of code let's go look at the Excel spreadsheet look at that it gave me all of the car entries.
All right, then we do a special cells, visible cells only copy those and look I was smart up here to start in A2, that way don't get the word list I also always get a blank beneath the AutoFilter that way even if nothing's found I still end up with one cell.
So, we've copied that and then I'm going to do a Paste Special, Transpose, press F8.
Let's see how that done.
All right! so now, does it matter which one you do well in this small case it really does not matter because there's hardly any items to look up, but it's going to start to matter when you have lots of items.
So, here, here I have a hundred thousand items over in column A that it has to look through.
All right! so, it's going to start to make a difference let's do a little time test here.
So, we're going to run the Loop Macro you can see the things actually slowly appear took about seven seconds to go through and do all that.
Now, let's compare it to the filter macro about four seconds to go through and do the filter.
So, you start to get a little bit of a difference they're between how many rows of data you have over there in column A.
All right! so, that's my solution that's VBA. Mike, let's see what you have.
Mike: Thanks MrExcel.
Wow! gotta love that VBA that loop is awesome and that filter you got I have a question for me MrExcel, was that a recorded Macro where you actually turned the Macro on did the filter and then turn the Macro off.
If that was the case, that'd be pretty cool.
I might even be able to do that then because I don't know how to write VBA.
That's why I'm stuck with formula.
Hey! So, this is an approximate match, approximate lookup, we're looking for Car that's not an exact match up it's got the word car there, but it doesn't exactly match that cell and we're returning multiple items.
So, I need these two items to be listed across the columns here.
So, first I'm going to Count, I'm going to use the SEARCH function, search is great we can say hey find car and usually you say find it there and it tells you the 8th position character is where car starts, but we're going to give it the whole range of values there and I'm going to hit the F4 key, and what it does now is it'll give us 8 here and six here because that starts at the six character.
All the other ones will give us an error.
So, if we highlight this, it's an array right this argument is expecting a single value, but we give it a range of values.
So, when we hit F9, it gives us an array of numbers when it finds cars and value error when it does not.
We can simply wrap ISNUMBER around that and that will give us a true whenever, it sees a number and to convert those numbers, trues and falses to numbers will use double negative because we need to count, right?
So, F9 gives us 1, 1, we'll put SUMPRODUCT around out and that will count those.
And SUMPRODUCT can handle this array, so we just hit Enter.
Now, I'm going to double click and send this down and we can see we get our count.
So, for example, there are no cows here.
Now, I'm actually going to copy this ISNUMBER search because we'll have to use that in our formula over here.
Now, we need to return multiple items and when we get to them here we only have 2, when we get over here we need a blank.
So, we're going to have to start this off with an IF and we're going to say we're in E2.
So, I'm going to say columns and columns could be used as a number incrementer.
So, I say E2 colon E2, right?
Now, we say from column E2 E how many columns are there? One, but one because this is locked and this is not we'll get 2, 3 etc as we copy over across the columns and we're going to say anytime that number is greater than this and F4 lock the column.
Anytime that's the case we need a value of true double quote for blank comma and the value of false will be our LOOKUP function, INDEX.
Now, INDEX we need to tell what we're looking up, we're looking up these whole values and then the F4 key to lock it and then comma and the only tricky part here is the row number because look at this we have Row 1 and Row 3 for Car, right?
So, we actually need to extract row numbers as we copy across the columns.
Now, I'm in 2010, so I'm going to show you a 2010 solution and then I'll show you the 2007, 2003 solution down here I'm going to use the AGGREGATE function, the AGGREGATE is great.
We ultimately need to use the small function to extract row numbers as we go to the side, but AGGREGATE can the first argument is function and it has the small function is one of its options.
So, I click that and it puts into 15 comma and with just a moment ago we saw that the array from search returns value errors and so, we don't want those to mess up our formula.
So, AGGREGATE has ignore errors as the options and there's lots of different ones but we want 6 comma and here's our array we need to create an array of all the row numbers and then extract just the one we want.
I'm going to in parentheses type ROW.
Now, if I highlight this, and hit the F4 key that will give me 2, 3, 4 etc as an array, but two is not really we want because Toyota car is the first item in the list.
So, from this ROW, I type minus ROW click on that first cell.
Now, right now to give us 2 minus 2 which is 0.
So, we add one back in that little array, close parentheses will give us all the row numbers, but we don't want all of them, we want just for color one and three.
So, I'm going to divide this by and Control+V that's, that ISNUMBER, SEARCH that's already got the trues and falses for where car is located in this column.
So, I'm going to hit the F4 key on this because we need to lock that when going to the side also remember car, but when we go down we need to move to truck.
Now, let's just see what this does right here or F9, look at that one and three those are the two row numbers for our car the divide by zeros will be eliminated by that six, Control+Z right array.
Finally we need our K which says, which item do we want do we want the first second, the second small, the second smallest, the third smallest.
So, we use our same number incrementer from over here in that argument right there I Control+V, I close parenthesis on AGGREGATE and the beauty of the AGGREGATE is it's not going to require Control+Shift+Enter because that argument can handle an array.
All right, INDEX we already have the row number the AGGREGATE is going to deliver it and if I highlight this and hit the F9, it better be Row 1 sure enough it is close parenthesis on the INDEX, close parentheses because the INDEX is the value of false and Enter, no Control+Shift+Enter, even though there's a bunch of arrays stuff in there and then I'm going to copy it down.
So, sure enough it works.
Now, the only difference in earlier versions is when you get to the INDEX, right here, row number you got to use this the actual small and if you download this workbook you can see that right there I hit pause for 2007 and earlier.
Otherwise, this great new AGGREGATE function, right here. All right.
Wanna thank you for stopping by.
We'll see you next time for another dueling Excel podcast from MrExcel and Excel Is Fun.
Episode 54, Approximate Multiple Lookup.
This is a crazy one he has some lookup values here Car, Truck, Jeep and he wants to look through column A and find all of the cells that contain the word car.
I have two solutions to this both of them are Macros, the Loop Macro have my self selected their press the Loop Macro and there is answer.
Cool and then the Filter Macro, I actually see the screen flash a little bit there, but you get the get the same answer.
Let's take a look at those two Macros, Alt+F11, already have those written, the first one the Loop Macro, fairly basic Macro if you take any course in Excel, VBA, Macros, you're going to get to these loops figure out how many rows we have today for each cell in the selection.
We look for that cell that's the variable look for and we're going to go from Row 2 down to the last row in this case 2 to 10, over in column A, we're going to use the in string function, now this is a VBA function.
it's not a Excel function, in string looks for a certain value in another string that's greater than 0 that means that it's found and once it's found we go to our current cell offset 0 rows, one column over and write the value and then this counter is a variable I increment that by one for anyone who is new to Macros, but I like this one do filter on selected let's run this one step at a time and you'll see how it's going.
So, for each cell over there in column C in our selection I build a variable called look for and that look for is what we're looking for Car, with an equal sign and asterisk before it and then another asterisk afterwards.
So, it builds actually a little criteria there and then I'm going to do this AutoFilter, the AutoFilter says hey, we're going to look through the first column that's column a the criteria is in the cases car.
So, I run that line of code let's go look at the Excel spreadsheet look at that it gave me all of the car entries.
All right, then we do a special cells, visible cells only copy those and look I was smart up here to start in A2, that way don't get the word list I also always get a blank beneath the AutoFilter that way even if nothing's found I still end up with one cell.
So, we've copied that and then I'm going to do a Paste Special, Transpose, press F8.
Let's see how that done.
All right! so now, does it matter which one you do well in this small case it really does not matter because there's hardly any items to look up, but it's going to start to matter when you have lots of items.
So, here, here I have a hundred thousand items over in column A that it has to look through.
All right! so, it's going to start to make a difference let's do a little time test here.
So, we're going to run the Loop Macro you can see the things actually slowly appear took about seven seconds to go through and do all that.
Now, let's compare it to the filter macro about four seconds to go through and do the filter.
So, you start to get a little bit of a difference they're between how many rows of data you have over there in column A.
All right! so, that's my solution that's VBA. Mike, let's see what you have.
Mike: Thanks MrExcel.
Wow! gotta love that VBA that loop is awesome and that filter you got I have a question for me MrExcel, was that a recorded Macro where you actually turned the Macro on did the filter and then turn the Macro off.
If that was the case, that'd be pretty cool.
I might even be able to do that then because I don't know how to write VBA.
That's why I'm stuck with formula.
Hey! So, this is an approximate match, approximate lookup, we're looking for Car that's not an exact match up it's got the word car there, but it doesn't exactly match that cell and we're returning multiple items.
So, I need these two items to be listed across the columns here.
So, first I'm going to Count, I'm going to use the SEARCH function, search is great we can say hey find car and usually you say find it there and it tells you the 8th position character is where car starts, but we're going to give it the whole range of values there and I'm going to hit the F4 key, and what it does now is it'll give us 8 here and six here because that starts at the six character.
All the other ones will give us an error.
So, if we highlight this, it's an array right this argument is expecting a single value, but we give it a range of values.
So, when we hit F9, it gives us an array of numbers when it finds cars and value error when it does not.
We can simply wrap ISNUMBER around that and that will give us a true whenever, it sees a number and to convert those numbers, trues and falses to numbers will use double negative because we need to count, right?
So, F9 gives us 1, 1, we'll put SUMPRODUCT around out and that will count those.
And SUMPRODUCT can handle this array, so we just hit Enter.
Now, I'm going to double click and send this down and we can see we get our count.
So, for example, there are no cows here.
Now, I'm actually going to copy this ISNUMBER search because we'll have to use that in our formula over here.
Now, we need to return multiple items and when we get to them here we only have 2, when we get over here we need a blank.
So, we're going to have to start this off with an IF and we're going to say we're in E2.
So, I'm going to say columns and columns could be used as a number incrementer.
So, I say E2 colon E2, right?
Now, we say from column E2 E how many columns are there? One, but one because this is locked and this is not we'll get 2, 3 etc as we copy over across the columns and we're going to say anytime that number is greater than this and F4 lock the column.
Anytime that's the case we need a value of true double quote for blank comma and the value of false will be our LOOKUP function, INDEX.
Now, INDEX we need to tell what we're looking up, we're looking up these whole values and then the F4 key to lock it and then comma and the only tricky part here is the row number because look at this we have Row 1 and Row 3 for Car, right?
So, we actually need to extract row numbers as we copy across the columns.
Now, I'm in 2010, so I'm going to show you a 2010 solution and then I'll show you the 2007, 2003 solution down here I'm going to use the AGGREGATE function, the AGGREGATE is great.
We ultimately need to use the small function to extract row numbers as we go to the side, but AGGREGATE can the first argument is function and it has the small function is one of its options.
So, I click that and it puts into 15 comma and with just a moment ago we saw that the array from search returns value errors and so, we don't want those to mess up our formula.
So, AGGREGATE has ignore errors as the options and there's lots of different ones but we want 6 comma and here's our array we need to create an array of all the row numbers and then extract just the one we want.
I'm going to in parentheses type ROW.
Now, if I highlight this, and hit the F4 key that will give me 2, 3, 4 etc as an array, but two is not really we want because Toyota car is the first item in the list.
So, from this ROW, I type minus ROW click on that first cell.
Now, right now to give us 2 minus 2 which is 0.
So, we add one back in that little array, close parentheses will give us all the row numbers, but we don't want all of them, we want just for color one and three.
So, I'm going to divide this by and Control+V that's, that ISNUMBER, SEARCH that's already got the trues and falses for where car is located in this column.
So, I'm going to hit the F4 key on this because we need to lock that when going to the side also remember car, but when we go down we need to move to truck.
Now, let's just see what this does right here or F9, look at that one and three those are the two row numbers for our car the divide by zeros will be eliminated by that six, Control+Z right array.
Finally we need our K which says, which item do we want do we want the first second, the second small, the second smallest, the third smallest.
So, we use our same number incrementer from over here in that argument right there I Control+V, I close parenthesis on AGGREGATE and the beauty of the AGGREGATE is it's not going to require Control+Shift+Enter because that argument can handle an array.
All right, INDEX we already have the row number the AGGREGATE is going to deliver it and if I highlight this and hit the F9, it better be Row 1 sure enough it is close parenthesis on the INDEX, close parentheses because the INDEX is the value of false and Enter, no Control+Shift+Enter, even though there's a bunch of arrays stuff in there and then I'm going to copy it down.
So, sure enough it works.
Now, the only difference in earlier versions is when you get to the INDEX, right here, row number you got to use this the actual small and if you download this workbook you can see that right there I hit pause for 2007 and earlier.
Otherwise, this great new AGGREGATE function, right here. All right.
Wanna thank you for stopping by.
We'll see you next time for another dueling Excel podcast from MrExcel and Excel Is Fun.
