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

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

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

Any help?

Thanks!  Register To Reply

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

Try this formula in row 2 copied down  Register To Reply

3. ## 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  Register To Reply

4. ## 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:  `Please Login or Register  to view this content.`  Register To Reply

5. ## 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)  Register To Reply

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!  Register To Reply

7. ## 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?  Register To Reply

8. ## 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. Originally Posted by daddylonglegs =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)  Register To Reply

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

Awesome!

What a little thing can change.  Register To Reply