How to count and return the top ten values from the count

**clowesr** · 11-12-2013, 12:54 PM

Hi All

I want to perform what seemed to me an easy task, I have a list of names and I want to count each unique name and then return the top ten values in one column and then the bottom 10 in the column next. I'm using it to send to some managers so they can see who is the worse at doing a certain thing and who is the best at not doing.

I thought I could use a pivot and then use the =Large and =small function but then I don’t want to return the numbers i want to return the names relevant to the numbers

thanks

**JBeaucaire** · 11-12-2013, 01:14 PM

I think a Pivot Table is the best solution, no heavy plumbing and in one list you would see the COUNT of all the items. The top 10 can easily be extracted from the Pivot, and the bottom 10.

**clowesr** · 11-12-2013, 01:16 PM

So what would you do to extract the top/bottom 10 names out of the pivot based on the count?

**sktneer** · 11-12-2013, 01:30 PM

Suppose you have names in the range A2:A26 and their values in the range B2:B26, then

For Top 10 Names in col. C

Please Login or Register  to view this content.

For Bottom 10 Names in col. D

Please Login or Register  to view this content.

Then drag the formula down the cells until you get blank.

Is this what you want?

**clowesr** · 11-13-2013, 04:58 AM

Hi That seems to work relatively well it certainly returns the top value perfectly, i think the problem comes if someone has the same count as the person before?

I have attached a working example with both the formula results and the expected results if that helps?

thanks