+ Reply to Thread
Results 1 to 5 of 5

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

  1. #1
    Registered User
    Join Date
    09-30-2013
    Location
    Manchester
    MS-Off Ver
    Excel 2007
    Posts
    30

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

    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

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

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

    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.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    09-30-2013
    Location
    Manchester
    MS-Off Ver
    Excel 2007
    Posts
    30

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

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

  4. #4
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,639

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

    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?
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  5. #5
    Registered User
    Join Date
    09-30-2013
    Location
    Manchester
    MS-Off Ver
    Excel 2007
    Posts
    30

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

    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
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    09-30-2013
    Location
    Manchester
    MS-Off Ver
    Excel 2007
    Posts
    30

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

    Figured it out now

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Count Intervals of Filtered TEXT values in Column and Return Count across a Row
    By Domenic in forum Excel Formulas & Functions
    Replies: 46
    Last Post: 09-06-2005, 07:05 PM
  2. [SOLVED] Count Intervals of Filtered TEXT values in Column and Return Count across a Row
    By Sam via OfficeKB.com in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 PM
  3. Replies: 27
    Last Post: 09-06-2005, 04:05 AM
  4. [SOLVED] Count Intervals of Filtered TEXT values in Column and Return Count across a Row
    By Sam via OfficeKB.com in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 AM
  5. Count Intervals of Filtered TEXT values in Column and Return Count across a Row
    By Sam via OfficeKB.com in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 11:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1