Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Ranking Formula

  1. #1
    Board Regular
    Join Date
    Apr 2017
    Posts
    108
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Ranking Formula

    Hello,

    I have a list of Data with the following headers: Count, Year, ID.

    What I am trying to do is come up with a formula that will give me the top 25 IDs for each year that have the highest counts. Basically the table I want to fill out looks like what you see below. I need a formula that will find and return the ID of each rank for each year. Any ideas?


    Year
    2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017
    Rank 1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    16,181
    Post Thanks / Like
    Mentioned
    280 Post(s)
    Tagged
    22 Thread(s)

    Default Re: Ranking Formula

    Maybe something like

    Excel 2013/2016
    ABCDEFGHIJKLMNOP
    1CountyearIDRank20072008200920102011201220132014201520162017
    2222007abc11abc4abc4abc4abc3abc3abc4abc3abc3abc4abc3abc3
    392007abc22abc3abc3abc3abc4abc3abc4abc2abc2abc3abc2abc2
    4312007abc33abc1abc1abc1abc1abc1abc2abc1abc4abc4abc4abc4
    51392007abc44abc2abc2abc2abc2abc2abc1abc1abc1abc2abc2abc1
    6142008abc1
    7102008abc2
    8442008abc3
    9652008abc4
    10162009abc1
    1172009abc2
    12472009abc3
    13672009abc4

    Sheet1



    Array Formulas
    CellFormula
    F2{=INDEX($C$2:$C$45,MATCH(LARGE(IF($B$2:$B$45=F$1,$A$2:$A$45),$E2),$A$2:$A$45,0))}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself



    Fill formula down & across
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  3. #3
    Board Regular
    Join Date
    Apr 2017
    Posts
    108
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Ranking Formula

    BEAUTIFUL.

    Thanks a bunch!

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    16,181
    Post Thanks / Like
    Mentioned
    280 Post(s)
    Tagged
    22 Thread(s)

    Default Re: Ranking Formula

    You're welcome
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  5. #5
    Board Regular
    Join Date
    Apr 2017
    Posts
    108
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Ranking Formula

    Sorry, I may have spoken too soon. It appeared to be workingat first, but some of the rankings arenít correct. For example, in 2014 I havean ID that should be in the rank 20 spot, but it isnít showing up at first.However, when I sort the data I have be year largest to smallest, it correctlyplaces the ID at rank 20 but messes up some others that were correct before.Any idea why it would be doing that?


  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    16,181
    Post Thanks / Like
    Mentioned
    280 Post(s)
    Tagged
    22 Thread(s)

    Default Re: Ranking Formula

    Apologies, my mistake.
    If you have the same count in different rows, that formula will pull out the first one, which may not be correct.
    I'll have another look tomorrow if nobody else steps in.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  7. #7
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    7,166
    Post Thanks / Like
    Mentioned
    30 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Ranking Formula

    I believe the above formula can create invalid results in the case of duplicate amounts. For example, if A7 is 139 in the example above, the 2008 list will be off.

    Duplicates in different years can be solved with this variation of the formula:

    =INDEX($C$2:$C$45,MATCH(LARGE(IF($B$2:$B$45=F$1,$A$2:$A$45),$E2)&"|"&F$1,$A$2:$A$45&"|"&$B$2:$B$45,0))
    with Control+Shift+Enter.


    It's even trickier if there are duplicates within a year, for example if you put 31 in A3 and A4. This formula should work for that, but it does assume that the values in column A are integers, and that the number of rows is less than 1000:

    =INDEX($C$2:$C$45,MATCH(LARGE(IF($B$2:$B$45=F$1,$A$2:$A$45+ROW($A$2:$A$45)/1000),$E2),$A$2:$A$45+ROW($A$2:$A$45)/1000,0))

    with Control+Shift+Enter.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  8. #8
    Board Regular
    Join Date
    Apr 2017
    Posts
    108
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Ranking Formula

    The number of rows in this unfortunately is 12,377. There could be the case where there are duplicates within a year, but it should be a very rare occurrence. Is there a reason it has to be less than 1000, or was that just the number you went with for the formula because I hadnít given you specifics?
    Last edited by SanFelippo; Dec 7th, 2018 at 11:16 AM.

  9. #9
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    7,166
    Post Thanks / Like
    Mentioned
    30 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Ranking Formula

    Just I number I picked since I didn't have specifics. You should be able to change the 1000's I marked in red to 100000's (a number bigger than 12,377) and it should work.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  10. #10
    Board Regular
    Join Date
    Apr 2017
    Posts
    108
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Ranking Formula

    Perfect, you guys are fantastic. Thanks!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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