Steve asks how to group records into age ranges. In Episode 949, I show how to use the range version of VLOOKUP to solve this problem.
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, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Basically we start out with massive amounts of data.
How we're gonna analyze this. Well, let's fire up a pivot table.
Let's see if you can solve this problem.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Question today from Steve.
Steve has some race results here and you see a column called Age and Steve wants to group those into categories like you know 0 to 9, 10 to 19 and so on.
And so, I'm gonna build a little table over here with the various categories and what I'm gonna do is.
I'm gonna label the table with the lowest value in the category and then have a text over here so, 10 to 19, 20 to 29, 30 to 39 and so on you get the idea and then to add the category in we're going to use a different version of VLOOKUP.
Most of the time we use comma false at the end of the VLOOKUP which needs an exact match, but we're going to use the other version of VLOOKUP the one that has comma true at the end.
Says hey, go find to this age within this table, press F4 and we want the second column, but I don't put comma false at the end.
We just leave it blank which is the same as doing true.
And now, it doesn't need an exact match what it does is it finds the value just lower than that.
So, as we fill this in here you see that it fills in the category very easily.
Now, Steve wanted then to sort by category and age.
So, that's now easy to do Data, Sort, Sort by Category and then we'll add a new level.
I said Category in age, category in time is what he wants to do and have the fastest times, click OK.
and now, we're good there for each category we get to see the times organized fastest to slowest.
So, a relatively good way to do this using the VLOOKUP version that we hardly ever use the the range VLOOKUP.
Where you don't have to put comma false at the end you don't need exact matches of course the one caveat here is that this table has to be sorted from lowest to highest in order to work with that range version of the VLOOKUP.
Thanks for stopping by.
We'll see you next time for another netcast for MrExcel.
Well, thanks for stopping by, We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Basically we start out with massive amounts of data.
How we're gonna analyze this. Well, let's fire up a pivot table.
Let's see if you can solve this problem.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Question today from Steve.
Steve has some race results here and you see a column called Age and Steve wants to group those into categories like you know 0 to 9, 10 to 19 and so on.
And so, I'm gonna build a little table over here with the various categories and what I'm gonna do is.
I'm gonna label the table with the lowest value in the category and then have a text over here so, 10 to 19, 20 to 29, 30 to 39 and so on you get the idea and then to add the category in we're going to use a different version of VLOOKUP.
Most of the time we use comma false at the end of the VLOOKUP which needs an exact match, but we're going to use the other version of VLOOKUP the one that has comma true at the end.
Says hey, go find to this age within this table, press F4 and we want the second column, but I don't put comma false at the end.
We just leave it blank which is the same as doing true.
And now, it doesn't need an exact match what it does is it finds the value just lower than that.
So, as we fill this in here you see that it fills in the category very easily.
Now, Steve wanted then to sort by category and age.
So, that's now easy to do Data, Sort, Sort by Category and then we'll add a new level.
I said Category in age, category in time is what he wants to do and have the fastest times, click OK.
and now, we're good there for each category we get to see the times organized fastest to slowest.
So, a relatively good way to do this using the VLOOKUP version that we hardly ever use the the range VLOOKUP.
Where you don't have to put comma false at the end you don't need exact matches of course the one caveat here is that this table has to be sorted from lowest to highest in order to work with that range version of the VLOOKUP.
Thanks for stopping by.
We'll see you next time for another netcast for MrExcel.
Well, thanks for stopping by, We'll see you next time for another netcast from MrExcel.
