+ Reply to Thread
Results 1 to 10 of 10

RANK - Non unique values, contiguous rank required

  1. #1
    Registered User
    Join Date
    05-24-2007
    Posts
    4

    RANK - Non unique values, contiguous rank required

    Hi,

    I'm trying to rank some values where more than one cell may contain the same value. Normally Ranking these would give these values the same rank which is fine, but the next value would not follow, but skip a few numbers:

    Example:

    Data Rank
    30 2
    10 6
    20 4
    20 4
    30 2
    50 1

    What I would like it to do is:

    Data Rank
    30 2
    10 4
    20 3
    20 3
    30 2
    50 1

    Any suggestions would be very much appreciated.

    Kind regards
    Paul

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    Would this be an acceptable option?

    This will rank each number sequentially and if a duplicate entry occurs it will give sequential numbers instead of repeating.

    =RANK($A1,$A$1:$A$6)+COUNTIF($A$1:A1,A1)-1

    Where A1:A6 houses your numbers to rank. copied down
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    05-24-2007
    Posts
    4
    Thanks for that, though I do want it to give the same number rank for the same value (as rank would normally do), but after that I want it to follow on at the next number, and not jump x places. So if the data values are non unique, it means there will be less unique rank numbers than there are data values (as per example).

    Cheers
    Paul

  4. #4
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    Try in B1 copied down

    =SUM(--ISNUMBER(MATCH(ROW(INDIRECT("1:"&RANK(A1,A$1:A$6))),RANK(A$1:A$6,A$1:A$6),0)))

    confirmed with CTRL+SHIFT+ENTER

  5. #5
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    Here's another way I found here (by Harlon Grove):

    http://groups.google.ca/group/micros...4e965ca42ae32f

    Please Login or Register  to view this content.
    Formula must be confirmed with CTRL+SHIFT+ENTER not just ENTER.

  6. #6
    Registered User
    Join Date
    05-24-2007
    Posts
    4
    Thankyou both for taking time out to look at this. I've tried both solutions, and both seem to be giving me the same value for every cell. I'm concerned that it may be my inability to paste arrays properly that may be the cause of the problem (highlight a number of rows, hit F2, Ctrl+Shift+Enter?), so I've uploaded a small sample of actual data so you can see what I'm trying to do (there are thousands of groups to be ranked, so I'm hoping a single array per sheet can handle that rather than having to paste them individually!)

    Thankyou for you patience in this. I've always managed to find some way of getting excel to do what I want, and this is the first one that's beaten me.

    Sincerely
    Paul
    Attached Files Attached Files

  7. #7
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    Hi, you need to confirm the formula with CTRL+SHIFT+ENTER not just ENTER.You will note the {} brackets.


    I've attached the sheet here and entered the formula (daddylongleg's version) in BR so you can compare. you can move it to BS at your convenience.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    05-24-2007
    Posts
    4
    That does exactly what I was looking for. Thankyou so much!

    Kind regards
    Paul

  9. #9
    Registered User
    Join Date
    02-20-2014
    Location
    Monterrey
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: RANK - Non unique values, contiguous rank required

    If I´m understanding what is going on that is exactly what I[m trying to get but I just can´t get to it.

    I´m trying to find how to upload in here but I can´t find the option so... https://www.dropbox.com/s/mi5sv53fbn...%20Needed.xlsx

    I´m trying to get column C (Rank) to rank column B (duh), but I cant have the skipping between numbers. Column C shows what i would need int his case since these are 12 different values.

    I tried the options presented but could not make those work.

    Any help?

  10. #10
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: RANK - Non unique values, contiguous rank required

    Hi, ellogan,

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

    ===


    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic

    Ciao,
    Holger
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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