+ Reply to Thread
Results 1 to 6 of 6

Long list, show top 5

  1. #1
    Forum Contributor
    Join Date
    12-18-2011
    Location
    long island
    MS-Off Ver
    365
    Posts
    236

    Long list, show top 5

    I have a list of say 100 names in A1:A100 with duplicates
    I'd like to display the top 5 by occurrence in B1:B5
    Any thoughts? I know its possible through the auto filter function but i was hoping to do it with a formula or array.

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Long list, show top 5

    have you consider the function Rank
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Forum Contributor
    Join Date
    12-18-2011
    Location
    long island
    MS-Off Ver
    365
    Posts
    236

    Re: Long list, show top 5

    I'm not too familiar with rank but i understand its basic operation. The logic in my head would be a countif function to count the number of occurrences in the range, rank them from one to five, then display the corresponding names as 1st, 2nd, 3rd, 4th, 5th. Can all this be done with rank?

  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Long list, show top 5

    1. It would help to see the workbook.

    You get better help on your question if you add a small excel file, without confidential information.

    Please also add manualy the expected result in your file.

    To Attach a File:

    1. Scroll down to the window below your post Additional Options
    2. In the frame Attach Files you will see the button Manage Attachments
    3. Click the button.
    4. A new window will open titled Manage Attachments - Excel Forum.
    5. Click the Browse... button to locate your file for uploading.
    6. This will open a new window File Upload.
    7. Once you have located the file to upload click the Open button. This window will close.
    8. You are now back in the Manage Attachments - Excel Forum window.
    9. Click the Upload button and wait until the file has uploaded.
    10. Close the window and then click Submit.

  5. #5
    Forum Contributor
    Join Date
    12-18-2011
    Location
    long island
    MS-Off Ver
    365
    Posts
    236

    Re: Long list, show top 5

    I dont have a complete list yet so i put together a simple on with only ten names.
    My desired output is in column C. i just realized in making this that i would end up in cases where i have ties. I guess ties would look good displayed with a "/" between them?
    or maybe just display the name and a number rank in the column next to it titled "Place" and stop at 5th place
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Long list, show top 5

    See if the attached file, solves your question.

    The result is in the green cells.

    I used several helpcolumns to get the result.

+ 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. Use excel to show long term sales increase
    By ddbroadhead92 in forum Excel General
    Replies: 2
    Last Post: 06-18-2015, 03:24 PM
  2. [SOLVED] =IF and =SUMIF formulas creating long long long data processing times.
    By comp in forum Excel Programming / VBA / Macros
    Replies: 32
    Last Post: 03-26-2014, 02:59 PM
  3. Replies: 10
    Last Post: 01-05-2013, 12:31 PM
  4. Replies: 4
    Last Post: 09-22-2012, 04:21 PM
  5. Show me one of each value in a long list
    By dvent in forum Excel General
    Replies: 4
    Last Post: 01-09-2009, 12:01 AM
  6. [SOLVED] How do i break really long bars to show short ones in Excel?
    By nkchu83 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 05-26-2005, 05:15 PM
  7. [SOLVED] Long numbers show up as Scientific Notation
    By berryware421243 in forum Excel General
    Replies: 5
    Last Post: 02-08-2005, 12:06 AM

Tags for this Thread

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