Today's Dueling Podcast [ Episode #1308 ] looks at sorting data with a Formula. Mike "Excel Is Fun" Girvin and Bill "MrExcel" Jelen look at the uses of the RANK, VLOOKUP, COUNTIF, LARGE, ROWS, INDEX, MATCH and SMALL Functions - plus a few shortcuts you may find useful!
Transcript of the video:
Hey, welcome back to another dueling Excel podcast.
I'm Bill Jelen from MrExcel will be joined by Mike Girvin from Excel Is Fun.
This is episode number 58, Sort with a Formula.
All right! So, here's the question. Mike sent this over.
OhmPaola from YouTube asked how can we have a formula that will sort, but keep the original data of order intact, I can already tell that Mike's going to win this one.
and the thing that got me, as I saw that there were ties here.
Ties make this very, very difficult.
First of all, we're going to start off the one that doesn't have ties.
My solution to this, is I come along and I insert a new column.
That column is going to use the RANK function.
I want the RANK of this value, within this range of values.
I'll press F4 there.
This is, that is, that is rank number 4.
I'm going to copy that down, so we get all the numbers from 1 through 5 and then what I do is, I come over here and put in the numbers 1, 2, 3, 4, 5 and you can see that, I already used a cool little trick there. I am changing the font color from black to white.
Now, noone even sees that those numbers are there.
And then =VLOOKUP, VLOOKUP.
I'm always going to go back to column D.
So, I'll press F4. 1, 2, 3, times there.
My range that I'm going to look up from, is this range. F4,2,FALSE.
And we see that Sioux has the largest sales, copy that down.
All right! now, I'm going to copy that over, and here I'm going to edit it to say, that I want column 3, instead of column 2. Copy that down.
All right and so, we see the largest sale, and the next the next the next.
Now, let's do a little test here.
Let's say that Lee comes along, it's a big sale, and he now have 33 for the day. Baam!
It resorts automatically using that formula, but it starts to fail, when we have a tie, right.
So, 2 people 21.
2 people 21.
We're getting N/A's and the reason we're getting N/A's is the way that Excel does the RANK formula.
Now, I've complained about this in the past.
We have 1, 2, 3, 3, 5.
In Excel 2010, they change the way that you can do RANK, but what they do is, they give me 1, 2, 3.5, 3.5, 5.
Not at all what I wanted.
So, if you have the possibility of ties, now you hopefully have some big huge number and no one's ever in a tie.
But if you have the possibility of ties, then you have to say.
All right, we're going to use the RANK plus the COUNTIF how many numbers above me.
So, we're going to go from C$4:C4 is equal to C5.
And what that will do, should put the plus sign there.
What we get, really silly format there.
We get the numbers 1, 2, 3, 4, 5.
So, even if there's a tie, the first person shows up with the 3 the next person shows up with a 4, and that allows that to work.
Definitely not a fun solution, there has to be something better.
Mike, let's see what you have.
Mike: Thanks MrExcel.
Not a fun solution, I love the solution. It's going to use the RANK, the COUNTIF and the VLOOKUP.
Everyone knows those functions, and you don't have to do an array formula.
All right, I'm going to do an array formula and trying to do two different formulas here.
All right, the first thing is I'm going to try and sort with a formula of the numbers, and then do some sort of look up on the sorted numbers that can deal with duplicates.
So, I'm going to use the LARGE function.
The LARGE function. Just we can tell it give me the second largest, third largest, and it will give us just those values.
So, I'm going to highlight that and then hit the F4 key to lock it , and as I copy it down I need a number incrementer.
So, I need 1, 2, 3, as it goes down through the row.
So, I'll just use the ROWS with an S, ROWS function, D4.
So, I'm going to say, hey D$4 that means that cell reference is locked.
D4, that one is not. That means an expandable range.
So, as we go down, it'll go from 4 to 4 which is 1 row, 4 to 5 which is 2, that is our number incrementer.
Copy it down, I'm going to do the control, the keyboard shortcut, Control+Shift+F4, that gives me a currency format.
All right! Now, I'm gonna try the INDEX, INDEX function.
INDEX is a lookup function, I give it the array. Well, the things I want to return are the name.
So, I can already see, I did this backwards, I'm going to highlight this.
Hold Shift and point to the edge. When I see that move cursor, I'm going to drag.
I want to see that gray bar I'm going to drop it.
I am going to let go of the mouse, but not the Shift.
All right! Now, I have the sales in the right column.
All right! Actually maybe I should just go like this.
You can see it's hard to drop that.
that's really hard, this, oh there it is, right there, there it is right.
Well, I guess it's better to...
Let's do it this way.
Almost, there we go.
That took a long time.
=INDEX the lookup range I want to look up these names, F4 , and what if I use the MATCH, the MATCH function.
I say. Hey, MATCH function, lookup that comma within this range right here, F4 comma 0.
That will return the ordinal position, the problem is when I copy this down, it gives me Lee because MATCH when you put a 0 these are not sorted numbers.
So, when you put a 0, it says find exact match. It's always going to find the first one.
So now, I need to do something slightly different here.
I'm going to get rid of that MATCH, want to have to use the SMALL function.
And ultimately what the INDEX function needs right here, is a row number.
So, for these duplicates, I need row 3, because 1, 2, 3 and row 5, 3, 4.
So, 3 & 5. Here I need 3, here I need 5.
So, I'm going to use the SMALL function.
The SMALL function, I'm going to create an array of the row numbers and since I have duplicates, I can, as I copy down, say. Hey, SMALL get the first smallest row number, second smallest, but I need to just pick out the rows with 20.
So, I'm going to say IF, if what, if anything in this range, right here F4 is equal to this.
That is going to give me a bunch of TRUEs and FALSEs, F9 to evaluate.
TRUE TRUE There's a TRUE, TRUE. Ctrl+Z.
If that's TRUE, then what I want, I want the ROW.
The problem with ROW right here, F4, is that'll give me Row 4, 5.
So, I'm going to, that one works on the subtract from it the ROW.
Now, notice what this will do, this will give me 4 - 4 which is 0.
So, that won't work and I want 1 here, so I'll add 1 back in.
All right that's the end of the IF and if you highlight the IF and hit the F9, you can see it gives me FALSE, FALSE, FALSE.
Oh, there's our 3 and 5.
So, that's the array that the SMALL is looking at, right.
So, all I need is a K here, give me the first 1 and then the second 1, Cntrl+Z, the k, and I'm going to use the MrExcel trick, he just did the COUNTIF that is just beautiful, COUNTIFs. What am I going to count, I am going to count this, Shift+: comma this, close parenthesis.
Now, I need to lock this. This needs to be an expandable range.
Right now will give me a count of 1.
Because there's one of them, if I hit the F4 key, when I copy it down, then that criteria moved to here and I'll be the second one.
All right, and then that's the K close off, close off on the INDEX and that's it.
Now, I have to, this is an array formula and the reason why it's an array formula is this IF function, logical test.
We gave it more than one TRUE and FALSE. So, boom! You have to use Control+Shift+Enter.
I'm holding Control+Shift and Enter.
All right and then Lee, Sioux, Mary, Lee, Sioux, Mary.
All right, so that looks like it worked.
If you're in 2010, this SMALL can be replaced with the AGGREGATE function, and you don't have to do Control+Shift+Enter.
So, if you download this workbook, you can see how to do that AGGREGATE.
All right, I'll throw it back over to MrExcel.
Bill: Mike. I'm sure that formula is incredibly cool, I'm still stuck back here without it.
Hold down the Shift key, go to the right edge, drag right, let go of the mouse, but not the Shift key.
What, what was that, what were you trying to do. I understand the headings were wrong.
Why not just come here to Sales, Control+X to cut and then Alt+I+E to Insert cells and that would work that way.
Oh! Your way was far cooler.
That's the beautiful thing about these dueling excel podcasts.
You learn all kinds of amazing, amazing things in the course of solving one problem.
Hey, I want to thank everyone for stopping by.
Will see you next time for another dueling Excel podcast from MrExcel and Excel Is Fun.
I'm Bill Jelen from MrExcel will be joined by Mike Girvin from Excel Is Fun.
This is episode number 58, Sort with a Formula.
All right! So, here's the question. Mike sent this over.
OhmPaola from YouTube asked how can we have a formula that will sort, but keep the original data of order intact, I can already tell that Mike's going to win this one.
and the thing that got me, as I saw that there were ties here.
Ties make this very, very difficult.
First of all, we're going to start off the one that doesn't have ties.
My solution to this, is I come along and I insert a new column.
That column is going to use the RANK function.
I want the RANK of this value, within this range of values.
I'll press F4 there.
This is, that is, that is rank number 4.
I'm going to copy that down, so we get all the numbers from 1 through 5 and then what I do is, I come over here and put in the numbers 1, 2, 3, 4, 5 and you can see that, I already used a cool little trick there. I am changing the font color from black to white.
Now, noone even sees that those numbers are there.
And then =VLOOKUP, VLOOKUP.
I'm always going to go back to column D.
So, I'll press F4. 1, 2, 3, times there.
My range that I'm going to look up from, is this range. F4,2,FALSE.
And we see that Sioux has the largest sales, copy that down.
All right! now, I'm going to copy that over, and here I'm going to edit it to say, that I want column 3, instead of column 2. Copy that down.
All right and so, we see the largest sale, and the next the next the next.
Now, let's do a little test here.
Let's say that Lee comes along, it's a big sale, and he now have 33 for the day. Baam!
It resorts automatically using that formula, but it starts to fail, when we have a tie, right.
So, 2 people 21.
2 people 21.
We're getting N/A's and the reason we're getting N/A's is the way that Excel does the RANK formula.
Now, I've complained about this in the past.
We have 1, 2, 3, 3, 5.
In Excel 2010, they change the way that you can do RANK, but what they do is, they give me 1, 2, 3.5, 3.5, 5.
Not at all what I wanted.
So, if you have the possibility of ties, now you hopefully have some big huge number and no one's ever in a tie.
But if you have the possibility of ties, then you have to say.
All right, we're going to use the RANK plus the COUNTIF how many numbers above me.
So, we're going to go from C$4:C4 is equal to C5.
And what that will do, should put the plus sign there.
What we get, really silly format there.
We get the numbers 1, 2, 3, 4, 5.
So, even if there's a tie, the first person shows up with the 3 the next person shows up with a 4, and that allows that to work.
Definitely not a fun solution, there has to be something better.
Mike, let's see what you have.
Mike: Thanks MrExcel.
Not a fun solution, I love the solution. It's going to use the RANK, the COUNTIF and the VLOOKUP.
Everyone knows those functions, and you don't have to do an array formula.
All right, I'm going to do an array formula and trying to do two different formulas here.
All right, the first thing is I'm going to try and sort with a formula of the numbers, and then do some sort of look up on the sorted numbers that can deal with duplicates.
So, I'm going to use the LARGE function.
The LARGE function. Just we can tell it give me the second largest, third largest, and it will give us just those values.
So, I'm going to highlight that and then hit the F4 key to lock it , and as I copy it down I need a number incrementer.
So, I need 1, 2, 3, as it goes down through the row.
So, I'll just use the ROWS with an S, ROWS function, D4.
So, I'm going to say, hey D$4 that means that cell reference is locked.
D4, that one is not. That means an expandable range.
So, as we go down, it'll go from 4 to 4 which is 1 row, 4 to 5 which is 2, that is our number incrementer.
Copy it down, I'm going to do the control, the keyboard shortcut, Control+Shift+F4, that gives me a currency format.
All right! Now, I'm gonna try the INDEX, INDEX function.
INDEX is a lookup function, I give it the array. Well, the things I want to return are the name.
So, I can already see, I did this backwards, I'm going to highlight this.
Hold Shift and point to the edge. When I see that move cursor, I'm going to drag.
I want to see that gray bar I'm going to drop it.
I am going to let go of the mouse, but not the Shift.
All right! Now, I have the sales in the right column.
All right! Actually maybe I should just go like this.
You can see it's hard to drop that.
that's really hard, this, oh there it is, right there, there it is right.
Well, I guess it's better to...
Let's do it this way.
Almost, there we go.
That took a long time.
=INDEX the lookup range I want to look up these names, F4 , and what if I use the MATCH, the MATCH function.
I say. Hey, MATCH function, lookup that comma within this range right here, F4 comma 0.
That will return the ordinal position, the problem is when I copy this down, it gives me Lee because MATCH when you put a 0 these are not sorted numbers.
So, when you put a 0, it says find exact match. It's always going to find the first one.
So now, I need to do something slightly different here.
I'm going to get rid of that MATCH, want to have to use the SMALL function.
And ultimately what the INDEX function needs right here, is a row number.
So, for these duplicates, I need row 3, because 1, 2, 3 and row 5, 3, 4.
So, 3 & 5. Here I need 3, here I need 5.
So, I'm going to use the SMALL function.
The SMALL function, I'm going to create an array of the row numbers and since I have duplicates, I can, as I copy down, say. Hey, SMALL get the first smallest row number, second smallest, but I need to just pick out the rows with 20.
So, I'm going to say IF, if what, if anything in this range, right here F4 is equal to this.
That is going to give me a bunch of TRUEs and FALSEs, F9 to evaluate.
TRUE TRUE There's a TRUE, TRUE. Ctrl+Z.
If that's TRUE, then what I want, I want the ROW.
The problem with ROW right here, F4, is that'll give me Row 4, 5.
So, I'm going to, that one works on the subtract from it the ROW.
Now, notice what this will do, this will give me 4 - 4 which is 0.
So, that won't work and I want 1 here, so I'll add 1 back in.
All right that's the end of the IF and if you highlight the IF and hit the F9, you can see it gives me FALSE, FALSE, FALSE.
Oh, there's our 3 and 5.
So, that's the array that the SMALL is looking at, right.
So, all I need is a K here, give me the first 1 and then the second 1, Cntrl+Z, the k, and I'm going to use the MrExcel trick, he just did the COUNTIF that is just beautiful, COUNTIFs. What am I going to count, I am going to count this, Shift+: comma this, close parenthesis.
Now, I need to lock this. This needs to be an expandable range.
Right now will give me a count of 1.
Because there's one of them, if I hit the F4 key, when I copy it down, then that criteria moved to here and I'll be the second one.
All right, and then that's the K close off, close off on the INDEX and that's it.
Now, I have to, this is an array formula and the reason why it's an array formula is this IF function, logical test.
We gave it more than one TRUE and FALSE. So, boom! You have to use Control+Shift+Enter.
I'm holding Control+Shift and Enter.
All right and then Lee, Sioux, Mary, Lee, Sioux, Mary.
All right, so that looks like it worked.
If you're in 2010, this SMALL can be replaced with the AGGREGATE function, and you don't have to do Control+Shift+Enter.
So, if you download this workbook, you can see how to do that AGGREGATE.
All right, I'll throw it back over to MrExcel.
Bill: Mike. I'm sure that formula is incredibly cool, I'm still stuck back here without it.
Hold down the Shift key, go to the right edge, drag right, let go of the mouse, but not the Shift key.
What, what was that, what were you trying to do. I understand the headings were wrong.
Why not just come here to Sales, Control+X to cut and then Alt+I+E to Insert cells and that would work that way.
Oh! Your way was far cooler.
That's the beautiful thing about these dueling excel podcasts.
You learn all kinds of amazing, amazing things in the course of solving one problem.
Hey, I want to thank everyone for stopping by.
Will see you next time for another dueling Excel podcast from MrExcel and Excel Is Fun.
