Another Dueling podcast: How to filter to the top 5 golf scores for one golfer? The AutoFilter won't let you do it. But Mike and Bill show you how in Episode 1005.
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey! I'm Bill Jelen from MrExcel.com.
I've got a cool excel tip for you, today.
Hey! This is Mike Gel Girvin from Excel Is Fun on YouTube.
I have a different way to do that.
Welcome back, it's another dueling Excel podcast.
Day that YouTube send in this question, and I completely feel his pain.
He's interested in doing a filter, a 'top-10' filter on two different columns, and you know, this just doesn't work if you would filter this down and say hey I want to see just the records for Tom.
Click [ ok ] and then come over to the score and say I want to see number filters, top-10.
I want to see the bottom that's the best five items.
Click [ ok ], then you want to give us three that's because Tom's fourth and fifth best scores are not in the top five overall or bottom five overall.
So, that is very frustrating.
Let's turn off the filter.
We're going to use an advanced filter to solve this least, that's how I'm going to go.
We'll see what Mike has to do.
I'm going to build a little helper cell over here.
This helper cell is going to be an array formula that says we want to go through and find Tom's fifth best score that way.
We know where we're going to go.
So, let's take a look at this formula.
I say, we're going to look through B6 to B27, see if it's equal to our selection in here, Tom.
If it is that, I'm with a corresponding cell from C6 to C27, otherwise.
I want a really huge number.
Something that will make it out of the realm of being in the top five.
So, I chose 999 That's my interior piece of the formula and then I use the SMALL function.
Small is like MIN, you know min gives you the smallest value but you can say I'm with a small of some range comma 2 to get the second smallest or comma 5 to get the fifth smallest.
So, [ ctrll shift enter ] that and it tells us that we're looking for everything less than or equal to 27.
Now, using advanced filter generally, we have to build a criteria range.
The criteria range usually looks like something like this a heading and then the value that we're interested in.
But there's another variant of the criteria for me, where you leave the heading blank and in that variant the second cell has to contain a formula.
The formula has to point to the very first row of data in your data set and Excel will take that formula, copy it down virtually, for all the things.
So my formula here says, we're going to look through C6, the very first score and see if it's less than or equal to our helper cell, over here.
Now, here I put dollar signs in to make sure that we're always pointing at the same helper cell.
Alright! So, finally we're going to go into the advanced filter.
Filter the list, in place.
I do want to use a criteria range.
My criteria range is E1 through F2, click [ ok ] and it filters the list down to just Tom.
Let's clear that filter and we'll try someone else let's choose Fred and again we will do the advanced filter, click [ ok ] and we see all of Fred scores.
Now, Fred didn't have five scores and you can see that's our helper cell, over here to shows up but we still get to see all of Fred scores.
Even though, the 74 clearly is not in the top five overall. Alright!
So, that's my method when Mike show me this problem is like, Oh! I want to use this cool formula version of the advanced filter.
I'll throw over to Mike and see what Mike has for us.
Mike: Thanks! Mr.Excel.
Hey! That advanced filter was great, what I like about it.
Is that it was self-contained.
You change the name here, run the advanced filter, boom!
It filters it, right in place.
Now, I'm going to use a formula here, and it's going to take up a little real estate.
You can see here, we have the same data set I'm going to do some formulas up here but this section here of the spreadsheet will be filled with formulas.
So, it will just automatically extract.
The disadvantage of course is that, you're using real estate and if you have a large data set, you're gonna have to have a lot of formulas here.
Advantages though, you won't have to run the advanced filter each time.
Now, I'm going to click in this cell here.
I'm actually going to add some data validation [ alt D L ], [ Tab L ], [ Tab ] and I'm going to get my source right here, click [ ok ].
So, now I can select Tom.
Now, I want to count Tom.
I need to go through this listing count because that'll help us down here with our formula.
I'm going to do a formula up here, equals count and that's in double quotes with a space at the end, ampersand (&) and then I'm going to click on Tom.
So, we have our label that will also change and now I need to count the occurrence of Tom.
How many scores there are?
Equals count if.
I'm going to click in the cell [ CTRL Shift down arrow ], [ F4 ] comma [ Left arrow ], close parenthesis.
I didn't need to lock that but it jumped the screen up.
So, that was convenient.
[ Tab ] and I want to get the bottom five.
So, I had to count Tom because I need to in this section here.
I'm going to show the formula.
I need to show just the top five but for instance Fred...
Ooh! Fred has three.
So, I need to then only show three records.
I'm going to do a label here too, equals in double quotes bottom.
[ Space ], double quote, ampersand, [ down arrow ].
So, that way I have a label there, too.
If I change this to four boom!
It said that if I change that there, this one will update.
Now, our formula...
We're going to have two formulas, one for extracting the name and one for extracting the five smallest.
This one will be equals if and we need to turn on and off this formula.
So, right now Fred...
We need to turn it on after we get to three.
So, we're going to use ROWS, which will allow us to increment a number inside a formula, and I'm sitting in F6.
So, I'm gonna do F, dollar sign, [ 6 ], colon, F6.
That way it'll count the rows, but as it goes down it'll increment higher, one row at a time.
If that's less than or equal to and what we need is the MIN of both of these because right now, we need to turn it off when it gets past three, but later when it gets to Tom we need it to be five, instead of the larger eight.
So, I'm going to say the MIN of this and I'm going to lock it with my [ F4 ] key going down.
So, if that's the case that rows are less than or equal to the min, then what do I want?
I want to get the name, [ F4 ] to lock it, going down otherwise I want blank double-quote, double-quote, close parenthesis [ ctrl enter ] and I'm going to drag it down.
So, now we got Fred, if we change it to Tom, boom!
We have Tom.
Now, our formula for extracting the five smalls.
We're going to use the small function.
But we need to look at this column get a criteria top and then go over here and get his values and from that, get the five smallest.
Equals if, this cell right here, equals blank then I want blank otherwise and here's what we'll do our small.
I'm going to have to do an if, just like you saw in MrExcel.
I'm going to get this range [ ctrl shift down ].
Oh no! [ F4 ] [ F4 ].
If that's equal to Tom and lock that one going down to then what do I want?
I want to look at this range here, [ F4 ], [ F4 ] right now.
That's the first part of the if, will give us a bunch of trues and falses.
This, then will find the trues and falses, will only extract Tom scores.
So, then we close parenthesis on our if, and notice we do not need the false.
So, I'm just going to close parenthesis.
The screen says hey, give me the K.
The K is the smallest well, we want one, two, three, four.
So, we're going to use our rows again, that increments number.
So, as we go down, we get the first small, second smallest, third smallest.
Now, I'm sitting in G6.
So, I'm going to put G, dollar sign, 6, colon, G6, close parenthesis.
Now, the small...
Screen tip says hey close parenthesis on that.
The if says closed parenthesis on that.
This is an array formula, so I hold [ ctrl + shift + enter ] and then I double click and send it down and just like that, I have my little system.
I change this to five, it shows Tom's sorted in order five smallest.
If I change it to Fred, it automatically only selects Fred's three smallest.
Alright!
There it is. We'll see you next trick.
MrExcel: Mike you did it again, congratulations!
Another point for Mike.
Want to thank you for stopping by, we'll see you next time for another podcast from MrExcel and Excel Is Fun.
I've got a cool excel tip for you, today.
Hey! This is Mike Gel Girvin from Excel Is Fun on YouTube.
I have a different way to do that.
Welcome back, it's another dueling Excel podcast.
Day that YouTube send in this question, and I completely feel his pain.
He's interested in doing a filter, a 'top-10' filter on two different columns, and you know, this just doesn't work if you would filter this down and say hey I want to see just the records for Tom.
Click [ ok ] and then come over to the score and say I want to see number filters, top-10.
I want to see the bottom that's the best five items.
Click [ ok ], then you want to give us three that's because Tom's fourth and fifth best scores are not in the top five overall or bottom five overall.
So, that is very frustrating.
Let's turn off the filter.
We're going to use an advanced filter to solve this least, that's how I'm going to go.
We'll see what Mike has to do.
I'm going to build a little helper cell over here.
This helper cell is going to be an array formula that says we want to go through and find Tom's fifth best score that way.
We know where we're going to go.
So, let's take a look at this formula.
I say, we're going to look through B6 to B27, see if it's equal to our selection in here, Tom.
If it is that, I'm with a corresponding cell from C6 to C27, otherwise.
I want a really huge number.
Something that will make it out of the realm of being in the top five.
So, I chose 999 That's my interior piece of the formula and then I use the SMALL function.
Small is like MIN, you know min gives you the smallest value but you can say I'm with a small of some range comma 2 to get the second smallest or comma 5 to get the fifth smallest.
So, [ ctrll shift enter ] that and it tells us that we're looking for everything less than or equal to 27.
Now, using advanced filter generally, we have to build a criteria range.
The criteria range usually looks like something like this a heading and then the value that we're interested in.
But there's another variant of the criteria for me, where you leave the heading blank and in that variant the second cell has to contain a formula.
The formula has to point to the very first row of data in your data set and Excel will take that formula, copy it down virtually, for all the things.
So my formula here says, we're going to look through C6, the very first score and see if it's less than or equal to our helper cell, over here.
Now, here I put dollar signs in to make sure that we're always pointing at the same helper cell.
Alright! So, finally we're going to go into the advanced filter.
Filter the list, in place.
I do want to use a criteria range.
My criteria range is E1 through F2, click [ ok ] and it filters the list down to just Tom.
Let's clear that filter and we'll try someone else let's choose Fred and again we will do the advanced filter, click [ ok ] and we see all of Fred scores.
Now, Fred didn't have five scores and you can see that's our helper cell, over here to shows up but we still get to see all of Fred scores.
Even though, the 74 clearly is not in the top five overall. Alright!
So, that's my method when Mike show me this problem is like, Oh! I want to use this cool formula version of the advanced filter.
I'll throw over to Mike and see what Mike has for us.
Mike: Thanks! Mr.Excel.
Hey! That advanced filter was great, what I like about it.
Is that it was self-contained.
You change the name here, run the advanced filter, boom!
It filters it, right in place.
Now, I'm going to use a formula here, and it's going to take up a little real estate.
You can see here, we have the same data set I'm going to do some formulas up here but this section here of the spreadsheet will be filled with formulas.
So, it will just automatically extract.
The disadvantage of course is that, you're using real estate and if you have a large data set, you're gonna have to have a lot of formulas here.
Advantages though, you won't have to run the advanced filter each time.
Now, I'm going to click in this cell here.
I'm actually going to add some data validation [ alt D L ], [ Tab L ], [ Tab ] and I'm going to get my source right here, click [ ok ].
So, now I can select Tom.
Now, I want to count Tom.
I need to go through this listing count because that'll help us down here with our formula.
I'm going to do a formula up here, equals count and that's in double quotes with a space at the end, ampersand (&) and then I'm going to click on Tom.
So, we have our label that will also change and now I need to count the occurrence of Tom.
How many scores there are?
Equals count if.
I'm going to click in the cell [ CTRL Shift down arrow ], [ F4 ] comma [ Left arrow ], close parenthesis.
I didn't need to lock that but it jumped the screen up.
So, that was convenient.
[ Tab ] and I want to get the bottom five.
So, I had to count Tom because I need to in this section here.
I'm going to show the formula.
I need to show just the top five but for instance Fred...
Ooh! Fred has three.
So, I need to then only show three records.
I'm going to do a label here too, equals in double quotes bottom.
[ Space ], double quote, ampersand, [ down arrow ].
So, that way I have a label there, too.
If I change this to four boom!
It said that if I change that there, this one will update.
Now, our formula...
We're going to have two formulas, one for extracting the name and one for extracting the five smallest.
This one will be equals if and we need to turn on and off this formula.
So, right now Fred...
We need to turn it on after we get to three.
So, we're going to use ROWS, which will allow us to increment a number inside a formula, and I'm sitting in F6.
So, I'm gonna do F, dollar sign, [ 6 ], colon, F6.
That way it'll count the rows, but as it goes down it'll increment higher, one row at a time.
If that's less than or equal to and what we need is the MIN of both of these because right now, we need to turn it off when it gets past three, but later when it gets to Tom we need it to be five, instead of the larger eight.
So, I'm going to say the MIN of this and I'm going to lock it with my [ F4 ] key going down.
So, if that's the case that rows are less than or equal to the min, then what do I want?
I want to get the name, [ F4 ] to lock it, going down otherwise I want blank double-quote, double-quote, close parenthesis [ ctrl enter ] and I'm going to drag it down.
So, now we got Fred, if we change it to Tom, boom!
We have Tom.
Now, our formula for extracting the five smalls.
We're going to use the small function.
But we need to look at this column get a criteria top and then go over here and get his values and from that, get the five smallest.
Equals if, this cell right here, equals blank then I want blank otherwise and here's what we'll do our small.
I'm going to have to do an if, just like you saw in MrExcel.
I'm going to get this range [ ctrl shift down ].
Oh no! [ F4 ] [ F4 ].
If that's equal to Tom and lock that one going down to then what do I want?
I want to look at this range here, [ F4 ], [ F4 ] right now.
That's the first part of the if, will give us a bunch of trues and falses.
This, then will find the trues and falses, will only extract Tom scores.
So, then we close parenthesis on our if, and notice we do not need the false.
So, I'm just going to close parenthesis.
The screen says hey, give me the K.
The K is the smallest well, we want one, two, three, four.
So, we're going to use our rows again, that increments number.
So, as we go down, we get the first small, second smallest, third smallest.
Now, I'm sitting in G6.
So, I'm going to put G, dollar sign, 6, colon, G6, close parenthesis.
Now, the small...
Screen tip says hey close parenthesis on that.
The if says closed parenthesis on that.
This is an array formula, so I hold [ ctrl + shift + enter ] and then I double click and send it down and just like that, I have my little system.
I change this to five, it shows Tom's sorted in order five smallest.
If I change it to Fred, it automatically only selects Fred's three smallest.
Alright!
There it is. We'll see you next trick.
MrExcel: Mike you did it again, congratulations!
Another point for Mike.
Want to thank you for stopping by, we'll see you next time for another podcast from MrExcel and Excel Is Fun.
