+ Reply to Thread
Results 1 to 6 of 6

Rank a range of data containing duplicates SEQUENTIALLY

  1. #1
    Registered User
    Join Date
    10-17-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    12

    Rank a range of data containing duplicates SEQUENTIALLY

    As the title indicates, I would like to rank data in a given range. However, the "Rank.EQ" function does not rank number sequentially if duplicates are present. The result ends up being 1,2,3,4,4,4,7,8,9,10... instead of 1,2,3,4,5,6,7,8,9,10... I have tried several work arounds, but I can't seem to wrap my head around how to get them to work properly.

    here are some examples of various codes I have tried...

    Example 1:

    =SUM(1*(A3<$A$3:$E$7))+1+IF(ROW(A3)-ROW($A$3)=0,0,SUM(1*(A3=OFFSET($A$3,0,0,INDEX(ROW(A3)-ROW($A$3)+1,1)-1,1))))

    Example 2:
    =RANK(A3,$A$3:$E$7,0)+COUNTIF($A$3:A3,A3)-1


    The code in the first example does not work properly when negatives are present in the range of data.

    The code in the second example does not work properly when the range of data is extended to multiple columns.

    I have attached the excel spreadsheet for your viewing. As you will see, My end goal is to conditionally format the TOP FIVE results (either top FIVE most negative or top FIVE most positive). The highlighted cells must also be greater than 500 or -500 depending on what report i run on the given data range. This concept will be applied to larger spreadsheets to highlight the top most "material" deviance's.

    This was a simple task using the AND function and RANK function, that is until i realized i could potentially run into issues with duplicates! I really could use some help on this!
    Attached Files Attached Files
    Last edited by banks334; 10-17-2013 at 10:10 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,917

    Re: Rank a range of data containing duplicates SEQUENTIALLY

    Hi and welcome to the forum

    You have so many different attempts in that file Im not sure what your expected outcome is. If you want to exclude duplicates in the ranking I would use a helper with a =if(countif($a$1:A1,A1)>1,"",A1) copied down, and then base the ranking on that helper
    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
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Rank a range of data containing duplicates SEQUENTIALLY

    Pl see the attached file with UDF.
    Attached Files Attached Files
    Last edited by kvsrinivasamurthy; 10-19-2013 at 12:36 AM.

  4. #4
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Rank a range of data containing duplicates SEQUENTIALLY

    find the attachment
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    10-17-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Rank a range of data containing duplicates SEQUENTIALLY

    Thank you for both reply's! Creating a UDF seems like the cleanest solution. Will a UDF be backwards compatible with older versions of excel?

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

    Re: Rank a range of data containing duplicates SEQUENTIALLY

    yes, it works with excel 2003.

+ 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: 7
    Last Post: 10-03-2012, 03:40 PM
  2. Weighted rank- if duplicates rank the average
    By vlady in forum Excel General
    Replies: 3
    Last Post: 02-28-2012, 09:17 PM
  3. Rank Data without duplicates
    By smh242 in forum Excel General
    Replies: 4
    Last Post: 02-26-2011, 07:32 AM
  4. Rank Duplicate Values Sequentially
    By kalyanverma in forum Excel General
    Replies: 3
    Last Post: 12-17-2009, 02:32 PM
  5. Excel Rank Duplicates then preferred rank
    By Economic in forum Excel General
    Replies: 2
    Last Post: 04-05-2009, 07:45 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