+ Reply to Thread
Results 1 to 9 of 9

RANK - Non unique values, contiguous rank required cannot be found

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

    RANK - Non unique values, contiguous rank required cannot be found

    Im 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 in thread http://www.excelforum.com/excel-gene...-required.html but could not make those work.

    Any help?

    Thanks!
    Attached Files Attached Files

  2. #2
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: RANK - Non unique values, contiguous rank required cannot be found

    Try this formula in row 2 copied down

    =IF(LEFT(A2,6)="Leader","",SUMPRODUCT(((LEFT(A$2:A$27,6)<>"leader")*(B$2:B$27<B2))/COUNTIF(B$2:B$27,B$2:B$27&""))+1)
    Audere est facere

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

    Re: RANK - Non unique values, contiguous rank required cannot be found

    Wow, it is pretty cool, not exaclty what i was looking for but solves the problem and stops skipping numbers. Thanks daddy.

    One issue is the "real" sheet doesnt have leader in that place but a name with a z at the beggining, for example:

    Name
    John
    Robert
    Peter
    Carl
    Karen
    Claire
    Johnson
    Joe
    zMark

    This to visualize who is the leader for that team.

    Looks like this cool work if used =IF(LEFT(A2,6)="z","",SUMPRODUCT((((LEFT(A$2:A$27,6)<>"z")*(B$2:B$27<B2))/COUNTIF(B$2:B$27,B$2:B$27&""))+1))

    What do you think? best way to attack that? What if the person has a "z" in the name? could it affect the results?


    Attached in sheet 2 you can see a more detailed version
    Attached Files Attached Files
    Last edited by ellogan; 02-20-2014 at 12:35 PM. Reason: added better version attached

  4. #4
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: RANK - Non unique values, contiguous rank required cannot be found

    Does this do what you want. It is daddylonglegs' formula with "Ω" instead of the "Z"? The Omega is just a character that is very unlikely to be found. The Omega can be found in the Symbols. Insert it into your worksheet then copy it and paste into your formula if this is something useful.

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  5. #5
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: RANK - Non unique values, contiguous rank required cannot be found

    because "Leader" has 6 characters I had to use LEFT(A2,6), for "z" that should be a 1, i.e.

    =IF(LEFT(A2,1)="z","",SUMPRODUCT(((LEFT(A$2:A$27,1)<>"z")*(B$2:B$27<B2))/COUNTIF(B$2:B$27,B$2:B$27&""))+1)

    That only discounts any name beginning with "z" (so you can't have "Zach"!)

    or this version will consider names beginning with upper case "Z" but not lower case "z"

    =IF(EXACT(LEFT(A2),"z"),"",SUMPRODUCT(((EXACT(LEFT(A$2:A$27),"z")+0=0)*(B$2:B$27<B2))/COUNTIF(B$2:B$27,B$2:B$27&""))+1)
    Last edited by daddylonglegs; 02-20-2014 at 02:51 PM.

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

    Re: RANK - Non unique values, contiguous rank required cannot be found

    Oh!!!! Ok, now I get it daddylonglegs. Then maybe using both your option with the Ω from newdoverman I cant get to use any name... Right?

    Thank you guys!

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

    Re: RANK - Non unique values, contiguous rank required cannot be found

    Ok, question.

    Why the highest value is 37 if there are only 27 "people"...

    Can the rank be the count of unqiue values?

  8. #8
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: RANK - Non unique values, contiguous rank required cannot be found

    I think the parentheses are wrong on the version you are using - you need to use the formula I suggested in my last post so that ranks begin at 1, i.e.

    Quote Originally Posted by daddylonglegs View Post
    =IF(LEFT(A2,1)="z","",SUMPRODUCT(((LEFT(A$2:A$27,1)<>"z")*(B$2:B$27<B2))/COUNTIF(B$2:B$27,B$2:B$27&""))+1)

  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 cannot be found

    Awesome!

    What a little thing can change.

+ 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. RANK - Non unique values, contiguous rank required
    By PaulBo in forum Excel General
    Replies: 9
    Last Post: 02-20-2014, 10:34 AM
  2. Replies: 6
    Last Post: 11-30-2013, 09:14 AM
  3. [SOLVED] RANK - Non unique values by subset.
    By brennanm in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-29-2013, 09:00 PM
  4. Formula to Rank and populate values base don Rank
    By Excel Dumbo in forum Excel General
    Replies: 3
    Last Post: 10-09-2012, 02:02 AM
  5. Replies: 1
    Last Post: 08-15-2005, 05:05 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