Get count on column

  • Thread starter Thread starter frogman7
  • Start date Start date
F

frogman7

I have a column of names, could be 5 different or 4000 different
names. I want to count the top 10 of this group. Is there a formula
that will help me do this or do I need to create a macro?
 
There may be some form of an elaborate array formula that would allow
you to do this, and yes it could be done through VBA. But, it can
also be done relatively simply without either of those.
3-Highlight the column containing the names
1-Data-->Filter-->Advanced Filter
2-Select "Copy To Another Location"
3-Check "Unique Records Only"
4-Click in the "Copy To" field and select a blank column
5-Double check to make sure that the "List Range" field contains the
address of you list of names
6-Click OK.

This will create a listing of all the unique names in your list. Then
you can do a simple CountIf formula to count how many times they occur
in that list. Sort the counts in descending order and there are your
top 10.
 
Try also a pivot table (PT). It's quite ideal for this. A couple of clicks,
drops n drags ... in a couple of seconds is all that it takes

Select only the col of names (select the entire col)
(just ensure the top cell carries a label, say: Name)

Click Data > Pivot table ..
Click Next > Next

In step 3 of the wiz, click Layout
Drag n drop Name into ROW area
Drag n drop Name into DATA area
(it'll appear as Count of Name)

Now double-click on Name in the ROW area,
click "Advanced" for the options available:
1. Check "Descending" under AutoSort options
using field: Count of Name
2. Check "On" for Top 10 AutoShow ...
Click to OK out all the way, then click Finish. That's it.
Hop over to the PT sheet for the results.
 
Back
Top