Learn Excel - Top 3 by Letter: Podcast #1376

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on Apr 5, 2011.
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.
maxresdefault.jpg


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.
 

Forum statistics

Threads
1,213,551
Messages
6,114,272
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top