Closed Thread
Results 1 to 14 of 14

Rank list sequentially without skipping rank value

  1. #1
    Registered User
    Join Date
    05-31-2017
    Location
    Brunswick, ME
    MS-Off Ver
    2010 (work) and 2016 (home)
    Posts
    68

    Rank list sequentially without skipping rank value

    I need a formula to rank my values so that duplicate values receive the same rank and subsequent values are ranked sequentially without skipping rank.

    RANK.EQ skips rank value, and the temporary fix that I have used (below) is not sortable.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Could this be solved with some use of FREQUENCY?

    Please see attached sheet.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2010
    Posts
    4,747

    Re: Rank list sequentially without skipping rank value

    Try this ...

    =SUMPRODUCT(($A$2:$A$45<=A2)/COUNTIF($A$2:$A$45,$A$2:$A$45))

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: Rank list sequentially without skipping rank value

    Try this:

    =SUMPRODUCT((A2 > A$2:A$45)/COUNTIF(A$2:A$45,A$2:A$45))+1
    Glenn



  4. #4
    Registered User
    Join Date
    05-31-2017
    Location
    Brunswick, ME
    MS-Off Ver
    2010 (work) and 2016 (home)
    Posts
    68

    Re: Rank list sequentially without skipping rank value

    Great! That does what I need. Thank you both.
    Last edited by theTaoJones; 05-31-2017 at 11:28 AM.

  5. #5
    Registered User
    Join Date
    05-31-2017
    Location
    Brunswick, ME
    MS-Off Ver
    2010 (work) and 2016 (home)
    Posts
    68

    Re: Rank list sequentially without skipping rank value

    One more question:
    I'm going to set this up in a template to sort material lists. Some of the lists have thousands of entries, whereas others only have a hundred or so. Is there a way to adjust the endpoint of the array so that it automatically detects where the list ends instead of me having to manually manipulate it?

    For example:
    For the first list I am sorting to A45, but the second list only goes to A20. If I import the 19 item list into the table I get a #VALUE error unless I adjust the formula to read
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Is there a way to set it up for 4,000 entries ($A$4001) but have it autodetect if the list does not extend that far?

  6. #6
    Registered User
    Join Date
    05-31-2017
    Location
    Brunswick, ME
    MS-Off Ver
    2010 (work) and 2016 (home)
    Posts
    68

    Re: Rank list sequentially without skipping rank value

    Quote Originally Posted by Phuocam View Post
    Try this ...

    =SUMPRODUCT(($A$2:$A$45<=A2)/COUNTIF($A$2:$A$45,$A$2:$A$45))
    Phuocam,
    I also had to make one more adjustment to the formula so that VLOOKUP could accurately read the numbers. Many of the entries round to #.9999... within the SUMPRODUCT calculation, so I added ROUNDUP.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: Rank list sequentially without skipping rank value

    In the sheet you posted, there was a formula in A47. I assume that it is not present in your real data. here is a way, using a Named Range - CTRL-F3 to view - to make the column range fully dynamic. It's called data, and the formula becomes:

    =SUMPRODUCT((A2 >data)/COUNTIF(data,data))+1

    There is something strange about your last comment, concerning ROUNDUP. The formula returns INTEGERS. Can you explain where the VLOOKUP and problems arise? The problem lies elsewhere....




    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    05-31-2017
    Location
    Brunswick, ME
    MS-Off Ver
    2010 (work) and 2016 (home)
    Posts
    68

    Re: Rank list sequentially without skipping rank value

    Glenn,

    The named range solution works great. Thank you!

    For the VLOOKUP issue, I've started a new thread here.

  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: Rank list sequentially without skipping rank value

    You're welcome.



    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

  10. #10
    Registered User
    Join Date
    12-17-2019
    Location
    amsterdam
    MS-Off Ver
    Office 2016
    Posts
    5

    Re: Rank list sequentially without skipping rank value

    Quote Originally Posted by Glenn Kennedy View Post
    Try this:

    =SUMPRODUCT((A2 > A$2:A$45)/COUNTIF(A$2:A$45,A$2:A$45))+1
    Thanks, this works for me in some cases. However, if I want to do this with an if condition, i.e. for different categories (like I before used to do with countifs statements) it doesn't work. Could anyone help me with this?
    Capture.PNG

    Cheers,

    Jelco

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,174

    Re: Rank list sequentially without skipping rank value

    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  12. #12
    Registered User
    Join Date
    04-11-2020
    Location
    Montreal
    MS-Off Ver
    10
    Posts
    4

    Re: Rank list sequentially without skipping rank value

    Hi, could you help to explain how this formula works?
    Thanks so much for your help!

  13. #13
    Registered User
    Join Date
    04-11-2020
    Location
    Montreal
    MS-Off Ver
    10
    Posts
    4

    Re: Rank list sequentially without skipping rank value

    Hello Glenn, could you help to explain the logic of this formula?
    Thank you so much,

  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,174

    Re: Rank list sequentially without skipping rank value

    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

Closed Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Rank Formula skipping a number
    By dianaschar in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-26-2019, 02:15 PM
  2. Ranking by Category w/o Skipping Rank
    By aoballer008 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-25-2016, 07:02 AM
  3. Rank a range of data containing duplicates SEQUENTIALLY
    By banks334 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-20-2013, 01:06 AM
  4. Team Rank without skipping numbers
    By mattmcg in forum Excel General
    Replies: 7
    Last Post: 05-05-2011, 03:29 PM
  5. Rank Duplicate Values Sequentially
    By kalyanverma in forum Excel General
    Replies: 3
    Last Post: 12-17-2009, 02:32 PM
  6. Un-Skipping the Rank after a duplicate value to get a regular Series!
    By e4excel in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-14-2009, 08:12 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