Bill Jelen: Hey! I'm Bill Jelen from mr.excel.com. I’ve got a cool excel tip for you today.
Male: Hey, this is Mike Gel Gervin in Excel is Fun You Tube and I have a different way to do that.
Alright, welcome back it's another dueling excel pod cast David and you have sent in it 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 okay 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 okay. They only give us three, that’s because Tom’s fourth and fifth best scores are not in the top five overall or bottom five or else so that is very frustrating, let’s turn off the filter.
We’re going to use an advanced filter to solve this at least—we’ll see what Mike has to do. I’m going to go to a little helper cell over here, this a helper cell is going to be an array formula that says we want to go through and find Tom’s fifth best score. Then we know where we’re going to go so let’s take a look at this formula as we’re going to look through B6 to B27, see if it’s equal to our selection for Tom. If it is then I want the corresponding cell from C6 to C27. Otherwise, I want a really huge number. Something that will make it out of the round of being in the top five so I chose 999 that’s why interior piece of the formula and then I used the small function. Smallest like mean, you know mean gives the smallest value but you can say, “I want the small of some range come a two to get the second smallest or come a five to get the fifth smallest so control 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 our criteria range. The criteria range usually looks like something like this, a heading and then the value that we’re interested in. But there is another variant of the criteria formula where you leave the heading blank and in that variant the second cell have to contain a formula. The formula has to point to the very first row of data in your data in your data set and excel will take that formula, copy it down virtually for all things so my formula here says “Hey, 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, I have a dollar sign to make sure they were 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 the criteria range. My criteria range is E1 through F2, click okay. 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 okay and we see all of Fred’s score. Now, Fred didn’t have five scores and you can see that’s our helper cell over here that shows up. But we still get to see all of Fred’s score even though the 74 clearly is not in the top five overall.
Alright, so that’s my method, when Mike showed me this problem I was like, ‘Oh, I want to use this cool formula version of the advanced filter.” Let’s throw it 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 it was self-contained. You can 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 mistake. You can see here we have the same dataset. I’m going to do some formulas up here but this section here of the spreadsheet will be filled with formula so will just automatically extract. The disadvantage of course is that you use in real estate and if you have a large dataset, you may 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 going to actually going to add some data validation, alt, DL, tab, L, tab and I’m going to get my source right here, click okay. So now, I can select Tom. Now, I want to count Tom, I need to go to this listing count because that will help us down here with our formula. I’m going to do a formula up here, equals, count and that’s a double quote of a space at the end, ampersand, and then I'm going to click on Tom. So, we have our label that we’ll 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 on the cell control shift, down, arrow F4, comma, left arrow, close parentheses. I don’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, Fred has three so I need to then only show three records. I'm going to do label here too, equals in double quotes bottom, space, double quote, ampersand, down arrow, so that way I have a label there to. If I change this to four, boom! It set that. If I change that there, this will update. Now our formula, we’re going to have 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 the formula and I’m sitting in F6 so I’m going to do F$6, colon, F6. That way it will count the rows but as it goes down a little increment higher one row at a time. If that’s less than or equal to and what we need is the mean of both of this 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 save the mean 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 mean then what do I want, I want to get the name. F4 then lock it going down other wise I want blank, double quote, double quote, close parenthesis, control 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 smallest, we’re going to use the small function but we need to look at this column get a criteria Tom 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 do on our small, small and we’re we going to do an if just like you saw in Mr. Excel. I’m going to get this range, control, shift, down arrow, 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, the first part of the “if” will give us a bunch of trues and falses. This then will—the trues and falses will only extract Tom’s 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 tip says “Hey, give me the K.” The K is the smallest but we want 1, 2, 3, 4 and so we’re going to use our rows again that increments number so as we go down we get the first smallest, second smallest, third smallest.
Now, I’m setting 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 close parenthesis on that, this is an array formula so I hold control and shift and 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 will automatically only selects Fred’s three smallest. Alright here it is, we’ll see you next trick.
Bill Jelen: Oh Mike, you did it again. I had the so cool solution to the whole thing with the advanced formula filter and all this cool stuff you just come up with, apply the auto filter and sort. Alright hey much better, congratulations another point from Mike. Alright, well I thank you for stopping by, we’ll see next time for another pod cast from Mr. Excel and Excel is Fun.
Transcription by:
Scribe4you Transcription Services