Today, with Learn Excel Episode #1420, Bill "MrExcel" Jelen and Mike "ExcelisFun" Girvin create methods to search for and match records in a Workbook.
Learn inherent functions of Excel as well as Custom Methods. Learn Excel 2010 from MrExcel!
Learn inherent functions of Excel as well as Custom Methods. Learn Excel 2010 from MrExcel!
Transcript of the video:
Hey! Welcome back it's another dueling Excel podcast.
I'm Bill Jelen from MrExcel.
We'd be joined by Mike Girvin from Excel Is Fun.
This is episode 81.
Get Matching Records.
Hey! Mike well welcome back.
Boy! This is an insane question that was sent in, today.
It's ended me if someone has a database here and they wanna be able to choose first name, last name and you see for each name there.
They're in there several times, one formulas to extract that information them hatching to him, and that's just, that's crazy.
We're try and write formulas for that.
I saw this I said well, that's obviously we want to go straight to VBA to do this and I rearrange things.
They had little drop-downs here, but I move them into this format up here in G1 through H2 and the reason I did that is this format is actually, a criteria range for the advanced filter command.
The advanced filter command and the way that advanced filter would normally work and I understand advanced filter is hard to do, we would come here choose a cell in original data set.
Data, go to advanced and say that our list is in A1 to E10, that criteria range is in G1 to H2.
And oh! By the way, copy the matching records to a new location and that location is going to have just the headings that we want.
So, I'll scroll over there to JK and out there are the headings So, again data, advanced, copy Okay! And you see that we get the results.
But I understand advanced filters really hard to use in the user interface.
No one likes to use advanced filter I get it, but I'm not suggest that we use advanced filter in that way.
What I'm suggesting is a little macro that will take the matching records and move them over there.
So, let's 11 and we wrote this macro, it's two lines, if you want to get right down to it because this whole line is one line, long line of code that I've broken into multiple lines, using the continuation characters that we can see it here on the screen.
First thing I do, is look for the final row that's a variable range A1, hit the [ end ] key, hit the [ down ] key and see what row I'm in.
That way if there's more records, that's going to expand and then from A1 will resize out to that final row, five columns, do an advanced filter.
We're doing the copy command, we're using the criteria G1 to H2, and we are copying to J , K and L.
Those three columns, unique equals false.
So, there's our macro, you know probably what we should do before, we do that is a range.
Just like J2 to L999 dot clear, that way it'll clear the previous results, just start of that.
Okay! So, we'll come back here, we want to make that Macro easy to run.
So, it's just a single click, I'm going to insert a shape with a nice little arrow shape.
Right-click, assign macro.
Okay, alright! Maybe adds a little text box there Drawing tools, text box, let's call it filter.
Home, center good enough, all right!
So, now all we should be able to do here is we should be able to choose a name, like filter and copy and all of valves records.
Now, go over there to new area.
So, VBA a lot faster than trying to figure out a formula for me anyway.
Mike let's see what you have.
Mike: Thanks, MrExcel.
Well, how it has been a long time, I broke my wrist six weeks ago.
I haven't done any Excel or videos, in six weeks and after doing Excel and videos for years and years every day.
Boy! That's strange.
So, it's good to be back.
I'll remember how to do this.
All right! So, here's our criteria.
I like the advanced filter in fact probably, a great way for a VBA, idiot like me is to simply turn on the macro recorder and do it that way.
All right! So, formulas.
Formulas the only advantage to formula, maybe is that it immediately changes as soon as you change the criteria.
You don't have to click that extra little button but still macros, VBA pretty nice.
All right! So, our problem is we have two criteria right and we have to extract multiple records.
So, there's a 4 Ken's right and then if we change it to Paul there's two records.
So, we came to a straight vlookup.
Well, there's lots of ways we could do this.
I'm going to add an extra column with a formula that will make our formula over here, quite a bit easier.
What you do is just use a straight vlookup, don't have to do an array formula.
First I want a column here, and I want to say have a true or false, anytime we have the right name.
So I'm gonna use the AND function.
I'm going to say, anytime one cell to my left is equal to this and hit the [ F4 ] key to lock it comma, now I can't quite get to my second logical test over there.
So I'm gonna use my [ arrow ] keys, ]arrow ], [ arrow ].
That's two cells to my left, any time that's equal to this and then [ F4 ], notice those are two logical tests.
They come out true or false when they both come out true and delivers a true.
Double click and send it down, now we have a bunch of trues and falses.
Now with this alone, we'd have to do still have to do an array formula over here, but watch this.
I want convert this basically to one, two, three, four here and then use a straight vlookup of here, looking up the number one, two, three, four.
All right! And I'm going to say this plus that, now this won't work because that's text, anytime you use the plus symbol to add a word and a number of what it's a two gives you a value error.
So, that's not gonna work, but no problem.
I'm going to use the SUM function.
Sum function is programmed to ignore text.
It's exactly like that plus symbol except for using a built-in function, which is programmed to ignore text.
So, when it adds.
It's just adding the true and the text.
Trues are always going to be ones, false is always going to be zero.
So, when I copy this down boom!
I get my one, two, three, four.
Now, the duplicates over here won't be a problem because here is the first number for all of these lists, four.
But they're not matches.
It's only the first four we're interested in.
No problem, vlookup.
We can say do an exact match and it will ignore.
It'll only take the first occurrence of the number four, all right!
Now, I need to figure out how many records are over there, notice I have that extra column.
So, I can just say, hey!
Give me the max.
Now, let's just do a straight vlookup and we're going to have to use, let's do vlookup.
Now our lookup value is going to be 1 for this row, 2 here, 3 here, 4 here, 5 etc.
So, we're going to use our number incrementer.
The formula element, rows up with an 'S'.
Now, I'm sitting in J2, so I say J, and I'm gonna lock the row because this is the ROWS function, so dollar.
Alright! So, that'll give me one, two, three, four is that copied down comma and then I simply highlight this whole table.
[ F4 ] to lock it, comma.
Now, column index, notice this is FN, LN, number item.
Second column, third column, four column, fifth column.
Well, if rows does one, two, three, four as you go down then columns does one, two, three, four as you go to the side, but notice we lock the row there.
Here we're going to lock the column.
So, that J will be locked, but this J will turn to K and then to L etc.
So, that gives us one, two, three, four as we copy to the site comma and then that zero in the fourth argument, for exact match.
That'll allow us to avoid those duplicates, [ ctrl enter ].
That's not going to work, notice we don't want 1, we want Ken.
No problem [ F2 ], to put in edit mode this columns has given us a 1, but really our first column, We're extracting is two.
So, I'm going to plus 1.
And then copy it over and to the side.
That, copy it down.
I know, that's a little bit off the screen there.
We got all these horrible N/A's, no problem, we're going to use this, notice 4 is the count, well the fourth row everything after the fourth row needs to show nothing.
So, I'm going to say equals IF.
Let me use my same number incrementer.
What are we going to say, when rows, which is one, two, three, four, five, [ Ctrl V ] is greater than our max?
And then [ F4 ], to lock it.
What's true comma, the value if true double quote, means show me nothing comma and then false is that big vlookup.
I come to the end, I close parentheses [ control enter ].
Copy it over and double click and send it down there we go.
Now, let's test it.
Let's change it to Paul.
Notice, this setup works fine because when we have two names that don't go together, it shows a zero and everything's black over here.
But as soon as we change it to Paul, boom!
There we go.
Wow, that was a wild one to come back on.
I'll throw it back over to MrExcel.
Mike, that is awesome.
I love that formula.
All right! I want to thank everyone for stopping by, we'll see you next time for another dueling Excel podcast from MrExcel and Excel Is Fun.
I'm Bill Jelen from MrExcel.
We'd be joined by Mike Girvin from Excel Is Fun.
This is episode 81.
Get Matching Records.
Hey! Mike well welcome back.
Boy! This is an insane question that was sent in, today.
It's ended me if someone has a database here and they wanna be able to choose first name, last name and you see for each name there.
They're in there several times, one formulas to extract that information them hatching to him, and that's just, that's crazy.
We're try and write formulas for that.
I saw this I said well, that's obviously we want to go straight to VBA to do this and I rearrange things.
They had little drop-downs here, but I move them into this format up here in G1 through H2 and the reason I did that is this format is actually, a criteria range for the advanced filter command.
The advanced filter command and the way that advanced filter would normally work and I understand advanced filter is hard to do, we would come here choose a cell in original data set.
Data, go to advanced and say that our list is in A1 to E10, that criteria range is in G1 to H2.
And oh! By the way, copy the matching records to a new location and that location is going to have just the headings that we want.
So, I'll scroll over there to JK and out there are the headings So, again data, advanced, copy Okay! And you see that we get the results.
But I understand advanced filters really hard to use in the user interface.
No one likes to use advanced filter I get it, but I'm not suggest that we use advanced filter in that way.
What I'm suggesting is a little macro that will take the matching records and move them over there.
So, let's 11 and we wrote this macro, it's two lines, if you want to get right down to it because this whole line is one line, long line of code that I've broken into multiple lines, using the continuation characters that we can see it here on the screen.
First thing I do, is look for the final row that's a variable range A1, hit the [ end ] key, hit the [ down ] key and see what row I'm in.
That way if there's more records, that's going to expand and then from A1 will resize out to that final row, five columns, do an advanced filter.
We're doing the copy command, we're using the criteria G1 to H2, and we are copying to J , K and L.
Those three columns, unique equals false.
So, there's our macro, you know probably what we should do before, we do that is a range.
Just like J2 to L999 dot clear, that way it'll clear the previous results, just start of that.
Okay! So, we'll come back here, we want to make that Macro easy to run.
So, it's just a single click, I'm going to insert a shape with a nice little arrow shape.
Right-click, assign macro.
Okay, alright! Maybe adds a little text box there Drawing tools, text box, let's call it filter.
Home, center good enough, all right!
So, now all we should be able to do here is we should be able to choose a name, like filter and copy and all of valves records.
Now, go over there to new area.
So, VBA a lot faster than trying to figure out a formula for me anyway.
Mike let's see what you have.
Mike: Thanks, MrExcel.
Well, how it has been a long time, I broke my wrist six weeks ago.
I haven't done any Excel or videos, in six weeks and after doing Excel and videos for years and years every day.
Boy! That's strange.
So, it's good to be back.
I'll remember how to do this.
All right! So, here's our criteria.
I like the advanced filter in fact probably, a great way for a VBA, idiot like me is to simply turn on the macro recorder and do it that way.
All right! So, formulas.
Formulas the only advantage to formula, maybe is that it immediately changes as soon as you change the criteria.
You don't have to click that extra little button but still macros, VBA pretty nice.
All right! So, our problem is we have two criteria right and we have to extract multiple records.
So, there's a 4 Ken's right and then if we change it to Paul there's two records.
So, we came to a straight vlookup.
Well, there's lots of ways we could do this.
I'm going to add an extra column with a formula that will make our formula over here, quite a bit easier.
What you do is just use a straight vlookup, don't have to do an array formula.
First I want a column here, and I want to say have a true or false, anytime we have the right name.
So I'm gonna use the AND function.
I'm going to say, anytime one cell to my left is equal to this and hit the [ F4 ] key to lock it comma, now I can't quite get to my second logical test over there.
So I'm gonna use my [ arrow ] keys, ]arrow ], [ arrow ].
That's two cells to my left, any time that's equal to this and then [ F4 ], notice those are two logical tests.
They come out true or false when they both come out true and delivers a true.
Double click and send it down, now we have a bunch of trues and falses.
Now with this alone, we'd have to do still have to do an array formula over here, but watch this.
I want convert this basically to one, two, three, four here and then use a straight vlookup of here, looking up the number one, two, three, four.
All right! And I'm going to say this plus that, now this won't work because that's text, anytime you use the plus symbol to add a word and a number of what it's a two gives you a value error.
So, that's not gonna work, but no problem.
I'm going to use the SUM function.
Sum function is programmed to ignore text.
It's exactly like that plus symbol except for using a built-in function, which is programmed to ignore text.
So, when it adds.
It's just adding the true and the text.
Trues are always going to be ones, false is always going to be zero.
So, when I copy this down boom!
I get my one, two, three, four.
Now, the duplicates over here won't be a problem because here is the first number for all of these lists, four.
But they're not matches.
It's only the first four we're interested in.
No problem, vlookup.
We can say do an exact match and it will ignore.
It'll only take the first occurrence of the number four, all right!
Now, I need to figure out how many records are over there, notice I have that extra column.
So, I can just say, hey!
Give me the max.
Now, let's just do a straight vlookup and we're going to have to use, let's do vlookup.
Now our lookup value is going to be 1 for this row, 2 here, 3 here, 4 here, 5 etc.
So, we're going to use our number incrementer.
The formula element, rows up with an 'S'.
Now, I'm sitting in J2, so I say J, and I'm gonna lock the row because this is the ROWS function, so dollar.
Alright! So, that'll give me one, two, three, four is that copied down comma and then I simply highlight this whole table.
[ F4 ] to lock it, comma.
Now, column index, notice this is FN, LN, number item.
Second column, third column, four column, fifth column.
Well, if rows does one, two, three, four as you go down then columns does one, two, three, four as you go to the side, but notice we lock the row there.
Here we're going to lock the column.
So, that J will be locked, but this J will turn to K and then to L etc.
So, that gives us one, two, three, four as we copy to the site comma and then that zero in the fourth argument, for exact match.
That'll allow us to avoid those duplicates, [ ctrl enter ].
That's not going to work, notice we don't want 1, we want Ken.
No problem [ F2 ], to put in edit mode this columns has given us a 1, but really our first column, We're extracting is two.
So, I'm going to plus 1.
And then copy it over and to the side.
That, copy it down.
I know, that's a little bit off the screen there.
We got all these horrible N/A's, no problem, we're going to use this, notice 4 is the count, well the fourth row everything after the fourth row needs to show nothing.
So, I'm going to say equals IF.
Let me use my same number incrementer.
What are we going to say, when rows, which is one, two, three, four, five, [ Ctrl V ] is greater than our max?
And then [ F4 ], to lock it.
What's true comma, the value if true double quote, means show me nothing comma and then false is that big vlookup.
I come to the end, I close parentheses [ control enter ].
Copy it over and double click and send it down there we go.
Now, let's test it.
Let's change it to Paul.
Notice, this setup works fine because when we have two names that don't go together, it shows a zero and everything's black over here.
But as soon as we change it to Paul, boom!
There we go.
Wow, that was a wild one to come back on.
I'll throw it back over to MrExcel.
Mike, that is awesome.
I love that formula.
All right! I want to thank everyone for stopping by, we'll see you next time for another dueling Excel podcast from MrExcel and Excel Is Fun.
