+ Reply to Thread
Results 1 to 3 of 3

Rank range but use only one instance of duplicated ranking

  1. #1
    Forum Expert nigelog's Avatar
    Join Date
    12-14-2007
    Location
    Cork, Ireland
    MS-Off Ver
    Office 365 Windows 10
    Posts
    2,286

    Talking Rank range but use only one instance of duplicated ranking

    I have an assessment workbook for employee incentive rewards it lists the top ten point winners using regular ranking formula

    Place Points Name
    1 19 Tom
    2 16 Dan O
    3 15 Dan P
    3 15 John
    5 14 Greg
    5 14 Lucas
    7 13 Wawro
    7 13 Mario
    9 12 Mike
    10 11 Peter

    formula used( =RANK(M5,$M$4:$M$13)

    What I would prefer to see is

    Place Points Name
    1 19 Tom
    2 16 Dan O
    3 15 Dan P
    (no number) 15 John
    5 14 Greg
    (no number) 14 Lucas
    7 13 Wawro
    (no number) 13 Mario
    9 12 Mike
    10 11 Peter

    Can this be done??

    Any help appreciated

  2. #2
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: Rank range but use only one instance of duplicated ranking

    Pivot the data first to aggregate THEN rank.
    Please ensure you mark your thread as Solved once it is. Click here to see how.
    If a post helps, please don't forget to add to our reputation by clicking the star icon in the bottom left-hand corner of a post.

  3. #3
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,587

    Re: Rank range but use only one instance of duplicated ranking

    In N4, then drag down
    =IF(COUNTIF($N$3:$N3,RANK(M4,$M$4:$M$13))>0,"",RANK(M4,$M$4:$M$13))

+ 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. Replies: 3
    Last Post: 02-08-2016, 06:55 PM
  2. Ranking values and ranking duplicates the same rank
    By 302arpks in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 03-05-2015, 01:50 PM
  3. How to Rank duplicated categories in a list
    By adambriggs in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-08-2015, 02:31 PM
  4. find 2nd instance of a duplicated value in an index/match formula?
    By Bobbo Jones in forum Excel Formulas & Functions
    Replies: 30
    Last Post: 06-14-2013, 09:42 AM
  5. RANK, duplicate ranking but no gaps in rank
    By arron laing in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-06-2012, 07:15 AM
  6. Rank amounts and duplicated in descending order
    By jimstrongy in forum Excel General
    Replies: 3
    Last Post: 01-19-2012, 03:29 PM
  7. Replies: 6
    Last Post: 06-01-2010, 06:19 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