Bill 'MrExcel' Jelen: [Almost] Live From New York City to judge the ModelOff Worldwide Excel Championship! With 16 Excel Rock Stars, from around the Globe, in NYC - and Bill having access to them all - why not do some Podcasting On-Site!
Our Dilemma: When we try to Sort with a Formula, the VLOOKUP will fail when there is a tie for the Sales Amount... But today, in Episode #1615, Martijn Reekers comes aboard The Learn Excel Podcast with a trick for getting all of the tied Values.
ModelOff is a worldwide competition to find the best Excel modellers with a $25,000 cash prize. For information on next year's competition, visit http://www.modeloff.com/modeloff-2013/
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Our Dilemma: When we try to Sort with a Formula, the VLOOKUP will fail when there is a tie for the Sales Amount... But today, in Episode #1615, Martijn Reekers comes aboard The Learn Excel Podcast with a trick for getting all of the tied Values.
ModelOff is a worldwide competition to find the best Excel modellers with a $25,000 cash prize. For information on next year's competition, visit http://www.modeloff.com/modeloff-2013/
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Transcript of the video:
Bill Jelen: Hey, welcome back to another MrExcel podcast, I'm Bill Jelen.
I’m here in New York City at the World Championship ModelOff Competition.
We have Martijn again.
Hey, it's another MrExcel netcast, I'm Bill Jelen.
I want to set up today's problem before we jump over to the solution.
So we have a database here right?
And you see that there are some ties in the Sales column, some people that have the exact same revenue -- and our goal today is to sort this whole thing with a formula.
Well, the sales portion is easy, =LARGE of this array over here (B2:B9), I'll press F4, comma, the first will give us the largest value, and as we copy that down.
LARGE is really good at handling ties -- so it knows that there were three customers with a 100.000, two customers with 10.000, and it you know, it doesn't give us a hassle that hey these are tied at 6, you know it gives us one at 6 and one at 7.
But then the problem is when we do the VLOOKUP to go back and get the customers, it's not going to work because we're always going to get the first customer who had a 100.000.00, twice -- and the first customer had a 100.000 three times, and the first customer had 10.000 twice, so you never get the other customers.
All right, let me set the, I want to set the stage then we'll jump back to the ModelOff.
This is a cool trick, so let's switch over to Excel.
Martijn, tell us what's going on here.
Martijn Reekers: This is a trick I recently learned.
Somebody asked me how can you automatically rank contents?
Now the issue is if you've got duplicate numbers –so I've come with a very simple example here.
We have 434, 434 again, and there's some other numbers -- if you would use a normal LARGE function using this column, the number itself would be fine.
But if you want to match this number to something, imagine this is a market cap, and you want to match this with the company, it’s not going to know the difference between this 434 and this 434.
So it's going to pull the same company name BHP, and BHP.
Bill Jelen: So the second company never ever shows up in the results.
Martijn Reekers: Exactly.
I mean you find if you just need the number, but in the moment you want to use that number to do something else like matching the company name, you're in trouble.
So what you can do is a very simple trick -- you just add a little like a billion or a million to the number and what we use here is it's just a simple ROW function just to make sure that these numbers in this column here are unique numbers.
Just use the same format as LARGE, okay -- which as you can see here is slightly different, so if just format this proper you don't see the difference.
This is very important and it allows you to use the VLOOKUP format.
Bill Jelen: That's a hot trick, that’s great -- what a cool, cool way.
No way we even see the numbers that they're there, they're way too small.
We're using the ROW function to make it be, slightly different and the VLOOKUP s work -- this is great.
Everyone, you need to come to the next year's 2013 ModelOFF Final in New York City.
We got Excel rock stars here; I’m learning so much.
Hey, I’ll see you next time for another netcast from MrExcel.
I’m here in New York City at the World Championship ModelOff Competition.
We have Martijn again.
Hey, it's another MrExcel netcast, I'm Bill Jelen.
I want to set up today's problem before we jump over to the solution.
So we have a database here right?
And you see that there are some ties in the Sales column, some people that have the exact same revenue -- and our goal today is to sort this whole thing with a formula.
Well, the sales portion is easy, =LARGE of this array over here (B2:B9), I'll press F4, comma, the first will give us the largest value, and as we copy that down.
LARGE is really good at handling ties -- so it knows that there were three customers with a 100.000, two customers with 10.000, and it you know, it doesn't give us a hassle that hey these are tied at 6, you know it gives us one at 6 and one at 7.
But then the problem is when we do the VLOOKUP to go back and get the customers, it's not going to work because we're always going to get the first customer who had a 100.000.00, twice -- and the first customer had a 100.000 three times, and the first customer had 10.000 twice, so you never get the other customers.
All right, let me set the, I want to set the stage then we'll jump back to the ModelOff.
This is a cool trick, so let's switch over to Excel.
Martijn, tell us what's going on here.
Martijn Reekers: This is a trick I recently learned.
Somebody asked me how can you automatically rank contents?
Now the issue is if you've got duplicate numbers –so I've come with a very simple example here.
We have 434, 434 again, and there's some other numbers -- if you would use a normal LARGE function using this column, the number itself would be fine.
But if you want to match this number to something, imagine this is a market cap, and you want to match this with the company, it’s not going to know the difference between this 434 and this 434.
So it's going to pull the same company name BHP, and BHP.
Bill Jelen: So the second company never ever shows up in the results.
Martijn Reekers: Exactly.
I mean you find if you just need the number, but in the moment you want to use that number to do something else like matching the company name, you're in trouble.
So what you can do is a very simple trick -- you just add a little like a billion or a million to the number and what we use here is it's just a simple ROW function just to make sure that these numbers in this column here are unique numbers.
Just use the same format as LARGE, okay -- which as you can see here is slightly different, so if just format this proper you don't see the difference.
This is very important and it allows you to use the VLOOKUP format.
Bill Jelen: That's a hot trick, that’s great -- what a cool, cool way.
No way we even see the numbers that they're there, they're way too small.
We're using the ROW function to make it be, slightly different and the VLOOKUP s work -- this is great.
Everyone, you need to come to the next year's 2013 ModelOFF Final in New York City.
We got Excel rock stars here; I’m learning so much.
Hey, I’ll see you next time for another netcast from MrExcel.
