In Part I of "Top 2 by Industry", Episode #1394, Mike "ExcelIsFun" Girvin and Bill "MrExcel" Jelen showed us how to get the result. Today, in Episode #1399, Mike and Bill revisit the Top 2 method to compare formula methods.
Transcript of the video:
Hey! Welcome back.
It's another dueling Excel podcast.
I'm Bill Jelen from MrExcel.
Be joined by Mike Girvin from Excel Is Fun.
This is episode 78, Top 2 Per Industry-Part 2.
Hey! Last week, Alex asks, "Is there a way to show the top two customers per industry?" and I used a pivot table.
Using the top 10 filter like, used sort and filter.
There is other ways of doing this...
Formula methods, and I'm going to use one of the brand new functions in excel 2010 called Aggregate.
Now, aggregate similar to subtotal that we've had since Excel 97.
In that we have these function numbers 1 through 11 same as in subtotal, but hey! Check this out.
They added function numbers 12 through 19 and a little known fact, 12 through 19 are allowed to have an array out here.
So, I'm going to use large.
What is large?
Large is just like max, which was there and subtotal.
The large allows us to say we want the largest value, or the second largest value, or the third largest value.
All right! second item what we're going to do.
We're gonna ignore the error values.
Error values, there are no error values over there.
Oh! That's there are.
At least there will be, by the time.
I build the range they gonna add up, okay!
So, the range of amount here starts in C2.
[ Ctrl + shift + down arrow ] C194.
I'll press [ F4 ] to lock that, all right!
That's one term.
Now, check this out right here on the fly, I'm going to create a Boolean array in the divisor.
I'm going to say, we want to look at all of the values from A 2, down to A 194.
See if they're equal to in quotes A.
All right!
So, what's that going to do?
That's going to take the numeric value, if this is true, true turns into a 1.
So, it's the numeric value divided by 1, beautiful.
If it's false numeric value divided by false turns into a 0.
That becomes a division by zero error and because back here in the 6. I said hey! Ignore the error values, bam!
It goes away.
And then we want the first largest value or the second largest, I'm going to put the two in there just assuming if a second largest value.
So, what's that.
What that is saying is for industry A the second largest value should be 2.
Let's do a quick little check here.
We'll sort by industry, there's the A's, bam!
It works, it's the second largest value.
I can't believe it, all right!
So, I took that formula and I changed it around a little bit to parameterize it and there we have for every industry the largest value and the second largest value.
All right! Mike, let's take a look at your Array formula.
Mike: Thanks, MrExcel.
Wow! That aggregate, that is beautiful.
I love the aggregate function in 2010, if you want to do the same thing in earlier versions and didn't have aggregate, you could simply do the same Boolean logic that MrExcel did it.
A say, is anything in that column equal to the A, with a column locked, then please give me everything in this column and then the large again, we're looking at 1, when I go over here, 2.
So, you could do it that way and then this is an array because there's a if function has more than one true and false.
So, if also you have to [ ctrl shift enter ], you can see the curly brackets up there and copy it down over.
You know, I totally misinterpreted Alex's question.
This is there a way to show the top two customers.
I thought he wanted these.
Now, that's hard.
Let's see if we can do it over here.
This may be too much for a video, but how about equals IF, I want this to turn over.
I want to be able to put it to one or two or three here and just have pop out to customers, the actual names of the customers.
So, I'm going to say columns and I'm sitting in F6, a dollar sign, F6 Colon F6.
Anytime, that's a number increment it goes one, two, three, one, two, three.
Any time that is greater than this with in F4.
What do I want, that means I'm past the 2, the second column.
I want blank.
All right! Comma and then the value of false, what's a gigantic look up form them and it is an array for me. The array...
What are we looking up the customer [ ctrl shift down ], F4 comma and this is going to get pretty wild here.
We're going to have to do a bunch of stuff, but we need a row number.
Now, here's the situation, we actually have two criteria but...
So, we need to find an A in this column and then B in this comment and see but over here.
We can't just say looking some value, we have to actually have to isolate some of the values in this column.
So, we're going to have actually two criteria, when we get to the second criteria which is the second biggest.
We're going to say whichever ones in this column are A.
Please give me the first and second biggest ones, all right!
So, this can get wild and the row I'm going to use small.
The small function will allow us to take the first smallest row number.
The second smallest row number and the third smallest row number because we're looking up names here.
And we need to know the actual row.
Eventually now, for this array, we're going to have a bunch of IF.
So, I'm going to say if anything in this column [ ctrL shift down ] [ F4 ], is equal to this with the column locked.
All right! Ready. That's the first condition.
Then comma, there's another condition and we said 12 and the second one is going to be pretty wild here.
If anything in this column [ control shift down arrow ] [ F4 ], is greater than or equal to and we're going to have to use the large function.
Large, remember we want the two biggest ones, So, that's all we have 2 IFs in front of inside this small.
To get the As or the Bs and then they get the second, the two biggest ones for A or B etc.
So, large.
We'll watch this inside the large.
I can't just say give me the two biggest.
I'm going to have to actually have to repeat the IF again and see is anything in this column equal to this one.
Alright! So, if that's the case.
What do I want to dump into the large function, these numbers So, all this is done so far, inside the large.
Here's the screen tip right here.
I just said if anything matches in A, then give me this range.
So, it's dumping in essence.
We can see over here, just these four values.
So, that's what the large function is receiving, all right!
And then that's the IF so, I want to close that off here.
The K I have to say comma and the K we want is 2, the second there.
Now notice, we're confer the small, we took this range, and we had to look through it again with the large.
But say give me the second biggest and by the way I have to hit [ F4 ].
But I'm asking anything in this range greater than equal to the second biggest largest value for the A's.
Now, I'm going to close this large off.
All right! So, this whole thing right here, this little large F9 is just giving me the second biggest value for the A's.
[ Ctrl Z ], all right!
Okay! That was pretty wild.
So, we have 2 IFs now.
Now, we can go comma and what's the ultimate thing, we're trying to put into the index row argument?
A row number.
Well, those are the two conditions.
Remember all the A's, the two biggest ones for all the A.
So, now I need to put some row numbers.
You can highlight whichever one columns.
I'm going to highlight these, [ control shift down arrow ] [ F4 ].
Now, that give me two, three, four.
That's not what I want.
So I have to subtract row from it that one.
Now, It's 2 minus 2, and I'm gonna hit [ F4 ].
So, I have to add one back in.
If you highlight this whole little bit, right here and hit [ F9 ], you can well, that's a lot of junk.
Maybe you can't see up here.
You can see one, two, three.
So it's all the row numbers for the sheets data set, [ control z ] but no problem, we're only picking potentially three of them, for each particular row, all right!
That's what we're putting into the small, so I'm going to close parenthesis on the first if I don't see a black.
So, I'm going to quote well, I don't see that brown one, right.
Now, we're back to small all of that just to get some row numbers comma and I'm going to use the same incrementer here, copy for the K because it's the small goes this way, I need the set first smallest, second smallest, third smallest row number that matches our two criteria.
All right! Ready, close that off and the screen tip goes to index, that row number.
That's, what's there.
Actually interesting thing here were oh yeah! We're getting raw numbers here.
So, that's exact what we want, closed parentheses on that, close parenthesis on the IF because the index is the false, closed parentheses.
[ Control shift enter ], and I'm praying this works.
Copy this over and down.
Now, I'm going to change this to A3 and there it is I'm going to change it to A1.
Change it to 2 and one other thing.
The reason, why we had to do this.
First little IF here because it seems like we don't have to do it, is just in case there's duplicates.
So, I'm going to copy this value right here.
So, there's two duplicates for and we should get 8375 and 11 when we do and if there's duplicates here, so the B now both has the same item.
So, this form is not getting confused for the fact that there's multiple duplicates here, is that right?
For B Yeah! it is working.
It's, these two right here, now.
So, that's pretty wild maybe I got totally confused on Alex's question, but I have been sick for a week.
So, all right! I'll throw back to MrExcel.
MrExcel: Well, that was crazy.
Hey! To learn how to do formulas like that, buy this book Slinging Excel Dragons.
That's a wild, to learn how to do what we did, last week.
Buy this book, pivot tables and all kinds of the good stuff.
I thank everyone for stopping by, we'll see you next time for another dueling Excel podcast from MrExcel, Excel Is Fun.
It's another dueling Excel podcast.
I'm Bill Jelen from MrExcel.
Be joined by Mike Girvin from Excel Is Fun.
This is episode 78, Top 2 Per Industry-Part 2.
Hey! Last week, Alex asks, "Is there a way to show the top two customers per industry?" and I used a pivot table.
Using the top 10 filter like, used sort and filter.
There is other ways of doing this...
Formula methods, and I'm going to use one of the brand new functions in excel 2010 called Aggregate.
Now, aggregate similar to subtotal that we've had since Excel 97.
In that we have these function numbers 1 through 11 same as in subtotal, but hey! Check this out.
They added function numbers 12 through 19 and a little known fact, 12 through 19 are allowed to have an array out here.
So, I'm going to use large.
What is large?
Large is just like max, which was there and subtotal.
The large allows us to say we want the largest value, or the second largest value, or the third largest value.
All right! second item what we're going to do.
We're gonna ignore the error values.
Error values, there are no error values over there.
Oh! That's there are.
At least there will be, by the time.
I build the range they gonna add up, okay!
So, the range of amount here starts in C2.
[ Ctrl + shift + down arrow ] C194.
I'll press [ F4 ] to lock that, all right!
That's one term.
Now, check this out right here on the fly, I'm going to create a Boolean array in the divisor.
I'm going to say, we want to look at all of the values from A 2, down to A 194.
See if they're equal to in quotes A.
All right!
So, what's that going to do?
That's going to take the numeric value, if this is true, true turns into a 1.
So, it's the numeric value divided by 1, beautiful.
If it's false numeric value divided by false turns into a 0.
That becomes a division by zero error and because back here in the 6. I said hey! Ignore the error values, bam!
It goes away.
And then we want the first largest value or the second largest, I'm going to put the two in there just assuming if a second largest value.
So, what's that.
What that is saying is for industry A the second largest value should be 2.
Let's do a quick little check here.
We'll sort by industry, there's the A's, bam!
It works, it's the second largest value.
I can't believe it, all right!
So, I took that formula and I changed it around a little bit to parameterize it and there we have for every industry the largest value and the second largest value.
All right! Mike, let's take a look at your Array formula.
Mike: Thanks, MrExcel.
Wow! That aggregate, that is beautiful.
I love the aggregate function in 2010, if you want to do the same thing in earlier versions and didn't have aggregate, you could simply do the same Boolean logic that MrExcel did it.
A say, is anything in that column equal to the A, with a column locked, then please give me everything in this column and then the large again, we're looking at 1, when I go over here, 2.
So, you could do it that way and then this is an array because there's a if function has more than one true and false.
So, if also you have to [ ctrl shift enter ], you can see the curly brackets up there and copy it down over.
You know, I totally misinterpreted Alex's question.
This is there a way to show the top two customers.
I thought he wanted these.
Now, that's hard.
Let's see if we can do it over here.
This may be too much for a video, but how about equals IF, I want this to turn over.
I want to be able to put it to one or two or three here and just have pop out to customers, the actual names of the customers.
So, I'm going to say columns and I'm sitting in F6, a dollar sign, F6 Colon F6.
Anytime, that's a number increment it goes one, two, three, one, two, three.
Any time that is greater than this with in F4.
What do I want, that means I'm past the 2, the second column.
I want blank.
All right! Comma and then the value of false, what's a gigantic look up form them and it is an array for me. The array...
What are we looking up the customer [ ctrl shift down ], F4 comma and this is going to get pretty wild here.
We're going to have to do a bunch of stuff, but we need a row number.
Now, here's the situation, we actually have two criteria but...
So, we need to find an A in this column and then B in this comment and see but over here.
We can't just say looking some value, we have to actually have to isolate some of the values in this column.
So, we're going to have actually two criteria, when we get to the second criteria which is the second biggest.
We're going to say whichever ones in this column are A.
Please give me the first and second biggest ones, all right!
So, this can get wild and the row I'm going to use small.
The small function will allow us to take the first smallest row number.
The second smallest row number and the third smallest row number because we're looking up names here.
And we need to know the actual row.
Eventually now, for this array, we're going to have a bunch of IF.
So, I'm going to say if anything in this column [ ctrL shift down ] [ F4 ], is equal to this with the column locked.
All right! Ready. That's the first condition.
Then comma, there's another condition and we said 12 and the second one is going to be pretty wild here.
If anything in this column [ control shift down arrow ] [ F4 ], is greater than or equal to and we're going to have to use the large function.
Large, remember we want the two biggest ones, So, that's all we have 2 IFs in front of inside this small.
To get the As or the Bs and then they get the second, the two biggest ones for A or B etc.
So, large.
We'll watch this inside the large.
I can't just say give me the two biggest.
I'm going to have to actually have to repeat the IF again and see is anything in this column equal to this one.
Alright! So, if that's the case.
What do I want to dump into the large function, these numbers So, all this is done so far, inside the large.
Here's the screen tip right here.
I just said if anything matches in A, then give me this range.
So, it's dumping in essence.
We can see over here, just these four values.
So, that's what the large function is receiving, all right!
And then that's the IF so, I want to close that off here.
The K I have to say comma and the K we want is 2, the second there.
Now notice, we're confer the small, we took this range, and we had to look through it again with the large.
But say give me the second biggest and by the way I have to hit [ F4 ].
But I'm asking anything in this range greater than equal to the second biggest largest value for the A's.
Now, I'm going to close this large off.
All right! So, this whole thing right here, this little large F9 is just giving me the second biggest value for the A's.
[ Ctrl Z ], all right!
Okay! That was pretty wild.
So, we have 2 IFs now.
Now, we can go comma and what's the ultimate thing, we're trying to put into the index row argument?
A row number.
Well, those are the two conditions.
Remember all the A's, the two biggest ones for all the A.
So, now I need to put some row numbers.
You can highlight whichever one columns.
I'm going to highlight these, [ control shift down arrow ] [ F4 ].
Now, that give me two, three, four.
That's not what I want.
So I have to subtract row from it that one.
Now, It's 2 minus 2, and I'm gonna hit [ F4 ].
So, I have to add one back in.
If you highlight this whole little bit, right here and hit [ F9 ], you can well, that's a lot of junk.
Maybe you can't see up here.
You can see one, two, three.
So it's all the row numbers for the sheets data set, [ control z ] but no problem, we're only picking potentially three of them, for each particular row, all right!
That's what we're putting into the small, so I'm going to close parenthesis on the first if I don't see a black.
So, I'm going to quote well, I don't see that brown one, right.
Now, we're back to small all of that just to get some row numbers comma and I'm going to use the same incrementer here, copy for the K because it's the small goes this way, I need the set first smallest, second smallest, third smallest row number that matches our two criteria.
All right! Ready, close that off and the screen tip goes to index, that row number.
That's, what's there.
Actually interesting thing here were oh yeah! We're getting raw numbers here.
So, that's exact what we want, closed parentheses on that, close parenthesis on the IF because the index is the false, closed parentheses.
[ Control shift enter ], and I'm praying this works.
Copy this over and down.
Now, I'm going to change this to A3 and there it is I'm going to change it to A1.
Change it to 2 and one other thing.
The reason, why we had to do this.
First little IF here because it seems like we don't have to do it, is just in case there's duplicates.
So, I'm going to copy this value right here.
So, there's two duplicates for and we should get 8375 and 11 when we do and if there's duplicates here, so the B now both has the same item.
So, this form is not getting confused for the fact that there's multiple duplicates here, is that right?
For B Yeah! it is working.
It's, these two right here, now.
So, that's pretty wild maybe I got totally confused on Alex's question, but I have been sick for a week.
So, all right! I'll throw back to MrExcel.
MrExcel: Well, that was crazy.
Hey! To learn how to do formulas like that, buy this book Slinging Excel Dragons.
That's a wild, to learn how to do what we did, last week.
Buy this book, pivot tables and all kinds of the good stuff.
I thank everyone for stopping by, we'll see you next time for another dueling Excel podcast from MrExcel, Excel Is Fun.
