Top 20 Values

[beeawwb]

Good morning All,

Having trouble searching the forums (can't search by 3 letter words
but I know I've seen this done somewhere, so I thought I might ask.

I've been asked to help with a problem, where, the user has a list i
Column "A". Column A only. It's about 30,000 rows long. It contains
list of user names, which occur many times.

Example:

AAA
AAA
AAA
BBB
BBB
CCC
CCC
CCC
CCC
CCC
DDD

And so on. What needs to happen is a way to show which usernames (To
20) occur the most. It could appear anywhere, for example, column B, o
anything like that. I think the solution might have something to d
with SumProduct, but I'm not really familiar with that function, so
don't know what would need to be done.

Thanks for your assistance,

-Bo

 

[Debra Dalgleish]

You could create a pivot table from the data, and display the top 20 names.

Add a heading to the list of names, e.g. "Names"
Select a cell in the list
Choose Data>Pivot Table and PivotChart Report
Click Next, click Next
Click the Layout button
Drag the Names button to the Row area
Drag another copy of the Names button to the Data area
Click OK, click Finish

Double-click the Names button in the Pivot Table
Click the Advanced button
Turn Top Ten AutoShow On
Set the options to show the Top 20
Click OK, click OK

 

[beeawwb]

Worked perfectly, thanks for that.

-