+ Reply to Thread
Results 1 to 6 of 6

Display 4 Highest Results with Same Values

  1. #1
    Registered User
    Join Date
    02-10-2010
    Location
    Toronto, Ontario
    MS-Off Ver
    Excel 2002
    Posts
    28

    Unhappy Display 4 Highest Results with Same Values

    I'm trying to compile a list of the top 4 names (Column A) based on the values of Column Q. The only problem is that there are same values. In Cell Z1, I want to display the top name (Name 1). In Cell Z3, I want to display the 2nd top name (Name 7). In Cell Z5, I want to display the 3rd top name (Name 10). In Cell Z7, I want to display the 4th top name (Name 2).

    I thought I figured it out with the MATCH() and LARGE() functions, but because of the same values, it's returning "Name 1" for all of them. I want it to basically move to the 2nd name if the value is the same as the previous one. Can anyone help please!!!??? I've attached a picture as an example below:

    worksheet.jpg

    A Q
    Name 1 99.00
    Name 2 96.60
    Name 3
    Name 4 86.10
    Name 5 95.80
    Name 6 96.30
    Name 7 99.00
    Name 8 93.40
    Name 9 89.20
    Name 10 99.00
    Name 11 96.50
    Name 12
    Name 13 95.50


    Thank you so much in advance!!
    Last edited by seaottr; 03-31-2013 at 08:01 PM.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,938

    Re: Display 4 Highest Results with Same Values

    Hi

    next time, please upload a sample workbook, no-one wants to re-create your data for you.

    having said that, assuming your data is in A1:B14 (including headings), I used this in a helper column C2, copied down. This will break the ties...

    =B2+(COUNTIF($B$2:B2,B2)*0.01)

    then to find the top 4 (or however many you want), use this, copied down...
    =INDEX($A$2:$A$14,MATCH(LARGE($C$2:$C$14,ROW(A1)),$C$2:$C$14,0),1)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Display 4 Highest Results with Same Values

    here is one way
    Attached Files Attached Files
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  4. #4
    Registered User
    Join Date
    02-10-2010
    Location
    Toronto, Ontario
    MS-Off Ver
    Excel 2002
    Posts
    28

    Unhappy Re: Display 4 Highest Results with Same Values

    Sorry, I forgot to mention that I can't use any of the columns beside the data being used. I've attached an example spreadsheet below.

    Book2.xlsx

  5. #5
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Display 4 Highest Results with Same Values

    why? no one cannot use another column. you mean you don't want to?

  6. #6
    Registered User
    Join Date
    02-10-2010
    Location
    Toronto, Ontario
    MS-Off Ver
    Excel 2002
    Posts
    28

    Re: Display 4 Highest Results with Same Values

    Thank you SO much!!! This is exactly what I needed!!! You rock!!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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