Alex asks how to identify the top 2 amounts by industry. Today, in Episode #1394, Mike "ExcelIsFun" Girvin and Bill "MrExcel" Jelen show us how its done.
Dueling Excel Podcast #77...This episode is the video podcast companion to the book, "Slaying Excel Dragons: A Beginners Guide to Conquering Excel's Frustrations and Making Excel Fun", by Mike Girvin and Bill Jelen. [Currently available in eBook / Print Edition and as a DVD Bundle]
AND
"Excel 2010 In Depth", by Bill Jelen a.k.a. MrExcel.
For all of your Microsoft Excel needs visit MrExcel.com -- Your One Stop for Excel Tips and Solutions.
Dueling Excel Podcast #77...This episode is the video podcast companion to the book, "Slaying Excel Dragons: A Beginners Guide to Conquering Excel's Frustrations and Making Excel Fun", by Mike Girvin and Bill Jelen. [Currently available in eBook / Print Edition and as a DVD Bundle]
AND
"Excel 2010 In Depth", by Bill Jelen a.k.a. MrExcel.
For all of your Microsoft Excel needs visit MrExcel.com -- Your One Stop for Excel Tips and Solutions.
Transcript of the video:
Hey, welcome back it's another dueling Excel podcast.
I'm Bill Jelen from MrExcel we'll joined by Mike Girvin from Excel Is Fun.
This is episode 77.
Top 2 Per Industry.
All right! So, hey! I got a question from Alex.
Is there a way to show the top 2 customers per industry?
So, we have a whole bunch of industries over here in column A and for each one lots of different customers we wanna see the top two based on sales amount.
Now, I'm gonna do that with a Pivot Table boy that's got a little bit harder in Excel 2007, Excel 2010, Insert, Pivot Table, OK.
All right, let's bring the Pivot Table Field List, up here where you can see we're going to put industry down in row labels, yup I'm out and values and customer as a second field in row labels and here's the thing that I don't like.
You know, Microsoft is in love with this compact layout they loved it so much I think they want to marry it and that puts both of those fields in one column which means that we have only one heading up here instead of two headings like we really need and yes you could open the drop-down and select from there, but who's going to figure that out.
Let's just do what I always do I go to the Design tab, on the Design tab, Report Layout go back to tabular form.
Hey, if anyone at Microsoft is listening, there should be a way to make that be the default because I understand that was a lot of work for you we don't like it.
All right now, that we have industry and customer I want to filter the customers to show only the top 2.
So, I'm gonna go to the customer drop down Value Filters just right outside of the screen is called top 10 at the bottom, top 10 even though it's called top 10 it doesn't have to be the top 10.
I gonna cruise down here let's just put it in 2, 2, Items based on Sum of Amount, click OK.
There's we now have the top 2.
Probably, don't need grand totals here in field A.
So, back at Options, Field Settings say, None click OK.
And now, we just have values.
Oh, what's that you'd like to have those field in.
Well, that's a lot easier in Excel 2010 go to the Design tab, Report Layout, Repeat All Item Labels and that's there that's a beautiful thing.
So, I'm sorry to complain about the compact mode they do a lot of great things for us out there at Microsoft no one sound in-grateful or ungrateful.
It's easy enough to go back and choose our own tabular form sorry about that didn't mean to be so.
Sound so, unappreciative of the things they do especially in Pivot Tables they keep making Pivot Tables, better and better and better.
So, that's my solution using Pivot Tables. Mike, let's see what you have.
Mike: Thanks MrExcel.
Wow, Pivot Table that's, that is amazing.
That means since we have a filter here we could filter on customer based on these 2 and show the top 2, totally amazing Pivot Tables are amazing.
All right, I'm going to do this slightly different way I don't think it's better than that Pivot Table.
I kind of like the Pivot Table, I'm going to use the filter method.
Now, In order to use, we're actually, I'm an actually filter and sort and then out of extra column and then I'll just do a regular filter.
Now, based on this extra column first we have to sort and we're gonna sort 2 times because I want all of the A's, all the industries together and within that industry I want the biggest to smallest.
So, I'm gonna sort this column first, Data, Sort or this one Z to A, want the biggest one's on top.
You can also in 2007, 10 go to right click Sort largest to smallest.
So, I did that first, then I come over here and I right click, Sort smallest or A to Z.
So now, I have all the A's together and within the A's we have this sorted.
So, it's an important for this trick that you do those two sorts.
Now, I'm gonna add an extra column.
I'm going to call this number or something and I'm gonna say some borders here and this is a formula.
I like to color my formulas in a table like this to indicate that this is the raw down and this is a formula.
I'll use COUNTIF and one of mine account I'm going to count this you know why because I only need 1, 2, the 2 biggest, but when it comes down to B, I need a 1, 2, when it comes down to C, I need a 1, 2.
So, I'm gonna count the range, I'm gonna type a colon and then comma and get this series, very same range.
Now, that's a ridiculous formula there until we lock this.
I'm going to hit the F4 key and lock the row reference, this is an expandable range.
So, as we go down the blue box will expand control enter when I double click and send it down.
Of course right here it can only count 2 because the range is only that big when it's down here three, but when of course when it comes to here the relative cell reference here, is now counting something different and so, it starts over.
Now, I can simply filter, I could create a table, I could go to Data and Filter, I use control shift L.
Now, I'm gonna filter on this column un-select everything and just go 1, 2 and now, I have the top 2 for each industry.
So, the key was we sorted at an extra column and then filtered based on this column just on 1, 2.
I'm going to control asterisks to highlight the whole table, control+C that highlights and you could see the dancing answer going around just the visible cells.
I'm going to Insert a new sheet down here, shift+F11 and then call this a data dump, DD.
I click right there and control+V.
So, I've extracted.
Now, maybe I didn't really want this column here so, I'm going to right click delete.
All right! so, that's using Sort, Filter and an extra column.
Throwback to MrExcel.
Bill: Hey mike that was a cool, cool method.
Now, you know the one thing when you use that COUNTIF that was a fancy, fancy thing to do with the expanding range how about a thing that I do is I always say equal IF column A of this row is equal to column A of the previous row, then I want one plus the previous another number otherwise I want one and just shoot that down gives you the exact same result 1, 2, 3, 4, 1, 2, 3, 4, 5 and so on re starting at 1 every time makes your head hurt a little bit less because you don't have to think about that expanding range, expanding range definitely a very very cool way to go.
All right! So, there you have it.
A couple of different ways to solve Alex's problem.
Wanna 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'll joined by Mike Girvin from Excel Is Fun.
This is episode 77.
Top 2 Per Industry.
All right! So, hey! I got a question from Alex.
Is there a way to show the top 2 customers per industry?
So, we have a whole bunch of industries over here in column A and for each one lots of different customers we wanna see the top two based on sales amount.
Now, I'm gonna do that with a Pivot Table boy that's got a little bit harder in Excel 2007, Excel 2010, Insert, Pivot Table, OK.
All right, let's bring the Pivot Table Field List, up here where you can see we're going to put industry down in row labels, yup I'm out and values and customer as a second field in row labels and here's the thing that I don't like.
You know, Microsoft is in love with this compact layout they loved it so much I think they want to marry it and that puts both of those fields in one column which means that we have only one heading up here instead of two headings like we really need and yes you could open the drop-down and select from there, but who's going to figure that out.
Let's just do what I always do I go to the Design tab, on the Design tab, Report Layout go back to tabular form.
Hey, if anyone at Microsoft is listening, there should be a way to make that be the default because I understand that was a lot of work for you we don't like it.
All right now, that we have industry and customer I want to filter the customers to show only the top 2.
So, I'm gonna go to the customer drop down Value Filters just right outside of the screen is called top 10 at the bottom, top 10 even though it's called top 10 it doesn't have to be the top 10.
I gonna cruise down here let's just put it in 2, 2, Items based on Sum of Amount, click OK.
There's we now have the top 2.
Probably, don't need grand totals here in field A.
So, back at Options, Field Settings say, None click OK.
And now, we just have values.
Oh, what's that you'd like to have those field in.
Well, that's a lot easier in Excel 2010 go to the Design tab, Report Layout, Repeat All Item Labels and that's there that's a beautiful thing.
So, I'm sorry to complain about the compact mode they do a lot of great things for us out there at Microsoft no one sound in-grateful or ungrateful.
It's easy enough to go back and choose our own tabular form sorry about that didn't mean to be so.
Sound so, unappreciative of the things they do especially in Pivot Tables they keep making Pivot Tables, better and better and better.
So, that's my solution using Pivot Tables. Mike, let's see what you have.
Mike: Thanks MrExcel.
Wow, Pivot Table that's, that is amazing.
That means since we have a filter here we could filter on customer based on these 2 and show the top 2, totally amazing Pivot Tables are amazing.
All right, I'm going to do this slightly different way I don't think it's better than that Pivot Table.
I kind of like the Pivot Table, I'm going to use the filter method.
Now, In order to use, we're actually, I'm an actually filter and sort and then out of extra column and then I'll just do a regular filter.
Now, based on this extra column first we have to sort and we're gonna sort 2 times because I want all of the A's, all the industries together and within that industry I want the biggest to smallest.
So, I'm gonna sort this column first, Data, Sort or this one Z to A, want the biggest one's on top.
You can also in 2007, 10 go to right click Sort largest to smallest.
So, I did that first, then I come over here and I right click, Sort smallest or A to Z.
So now, I have all the A's together and within the A's we have this sorted.
So, it's an important for this trick that you do those two sorts.
Now, I'm gonna add an extra column.
I'm going to call this number or something and I'm gonna say some borders here and this is a formula.
I like to color my formulas in a table like this to indicate that this is the raw down and this is a formula.
I'll use COUNTIF and one of mine account I'm going to count this you know why because I only need 1, 2, the 2 biggest, but when it comes down to B, I need a 1, 2, when it comes down to C, I need a 1, 2.
So, I'm gonna count the range, I'm gonna type a colon and then comma and get this series, very same range.
Now, that's a ridiculous formula there until we lock this.
I'm going to hit the F4 key and lock the row reference, this is an expandable range.
So, as we go down the blue box will expand control enter when I double click and send it down.
Of course right here it can only count 2 because the range is only that big when it's down here three, but when of course when it comes to here the relative cell reference here, is now counting something different and so, it starts over.
Now, I can simply filter, I could create a table, I could go to Data and Filter, I use control shift L.
Now, I'm gonna filter on this column un-select everything and just go 1, 2 and now, I have the top 2 for each industry.
So, the key was we sorted at an extra column and then filtered based on this column just on 1, 2.
I'm going to control asterisks to highlight the whole table, control+C that highlights and you could see the dancing answer going around just the visible cells.
I'm going to Insert a new sheet down here, shift+F11 and then call this a data dump, DD.
I click right there and control+V.
So, I've extracted.
Now, maybe I didn't really want this column here so, I'm going to right click delete.
All right! so, that's using Sort, Filter and an extra column.
Throwback to MrExcel.
Bill: Hey mike that was a cool, cool method.
Now, you know the one thing when you use that COUNTIF that was a fancy, fancy thing to do with the expanding range how about a thing that I do is I always say equal IF column A of this row is equal to column A of the previous row, then I want one plus the previous another number otherwise I want one and just shoot that down gives you the exact same result 1, 2, 3, 4, 1, 2, 3, 4, 5 and so on re starting at 1 every time makes your head hurt a little bit less because you don't have to think about that expanding range, expanding range definitely a very very cool way to go.
All right! So, there you have it.
A couple of different ways to solve Alex's problem.
Wanna thank everyone for stopping by.
We'll see you next time for another dueling excel podcast from MrExcel and Excel Is Fun.
