For today's podcast, Tyler asks the easiest way to produce a report that contains the top 3 values for the most popular names - per each letter of the alphabet in alpha order. In Episode #1376, Bill shows us one method for solving the problem.
Transcript of the video:
MrExccel podcast is sponsored by Easy-XL.
Learn Exccel from MrExcel Podcast. Episode 1376.
Top 3 Per Letter Alright well hey welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's question sent in by Tyler.
Tyler has some data here lots of different last names and a Rank and Tyler needs to get the top 3 A's top 3 B's the top 3 C's the top 3 Ds and all the way on down through you know what i thought I thought Pivot Table.
Here's what I came up with, I'm just going to insert a couple of columns the first one is called Letter.
=LEFT equal left of that column no 1.
Left is a great function because it's just the left most one character that kind of classifies everything.
the A's the B's the C's right and then I'm going to sort by letter.
Sort by Letter values A to Z. I'm going to add a level and sort by Let's go with Rank Smallest to Largest And click OK now what that does is that brings the three largest A's to the top if I scroll down then I'll have the three largest B's at the top of that and to solve this I'm going to say RankWithin RankWithin all right this is cool. This is really easy when you get right down to it =IF This letter is the same as the previous letter then I want the value before me +1 otherwise, I'm at a brand new letter. I want to start all over again at 1.
All right so that little formula when I shoot that down double click the fill handle Sweet. I get 1 2 3 and when I scroll down and find the Bs down here It starts with 1 2 3 as well.
So now what I want to do is I want to find all of the 1 2s and 3s out there in column F.
Really easy way to do that on the Data tab go to Filter.
And I'm going to do a Number Filter less than and say < 4 that will give you 1 2's and 3's all right.
And I now have the three largest A's the three largest B's and all the way on down through the list.
You'll see that all the other values are still kind of hidden there. I'm going to check all of that data.
So that's starting the top control+shift+ down arrow, control +shift+ right arrow.
Ctrl+C to copy. Ctrl+N for a new workbook.
Ctrl-V to paste and I end up with just the information I was looking for.
At this point I really don't need column A anymore really don't need the RankWithin anymore. There is my answer.
Alright so hey, I want to thank Tyler for sending that question in and what thank you stopping by. We'll see you next time for another netcast from MrExcel.
Learn Exccel from MrExcel Podcast. Episode 1376.
Top 3 Per Letter Alright well hey welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's question sent in by Tyler.
Tyler has some data here lots of different last names and a Rank and Tyler needs to get the top 3 A's top 3 B's the top 3 C's the top 3 Ds and all the way on down through you know what i thought I thought Pivot Table.
Here's what I came up with, I'm just going to insert a couple of columns the first one is called Letter.
=LEFT equal left of that column no 1.
Left is a great function because it's just the left most one character that kind of classifies everything.
the A's the B's the C's right and then I'm going to sort by letter.
Sort by Letter values A to Z. I'm going to add a level and sort by Let's go with Rank Smallest to Largest And click OK now what that does is that brings the three largest A's to the top if I scroll down then I'll have the three largest B's at the top of that and to solve this I'm going to say RankWithin RankWithin all right this is cool. This is really easy when you get right down to it =IF This letter is the same as the previous letter then I want the value before me +1 otherwise, I'm at a brand new letter. I want to start all over again at 1.
All right so that little formula when I shoot that down double click the fill handle Sweet. I get 1 2 3 and when I scroll down and find the Bs down here It starts with 1 2 3 as well.
So now what I want to do is I want to find all of the 1 2s and 3s out there in column F.
Really easy way to do that on the Data tab go to Filter.
And I'm going to do a Number Filter less than and say < 4 that will give you 1 2's and 3's all right.
And I now have the three largest A's the three largest B's and all the way on down through the list.
You'll see that all the other values are still kind of hidden there. I'm going to check all of that data.
So that's starting the top control+shift+ down arrow, control +shift+ right arrow.
Ctrl+C to copy. Ctrl+N for a new workbook.
Ctrl-V to paste and I end up with just the information I was looking for.
At this point I really don't need column A anymore really don't need the RankWithin anymore. There is my answer.
Alright so hey, I want to thank Tyler for sending that question in and what thank you stopping by. We'll see you next time for another netcast from MrExcel.
