Rank Duplicate Values With Duplicate Ranks Without Skipping Sequence

[PATSYS]

Hi all,

I have the following data in A1:A7

100
40
80
80
85
60
60

In the corresponding column B, I want to return the rank of each (descending) i.e.

1
5
3
3
2
4
4

If I use the function rank, what I get is:

1
7
3
3
2
5
5

How can this be done?

Thanks

 

[whada]

@ T. Valko

Hi there - I used the formula in my spreadsheet but there seems to be some issue with the calculation. My column has 11000 records only 6 of them unique. I wanted to get the ascending ranging using the formula above however the calculation on 11k rows takes forever - it only moves 1% every 5 minutes. The machine is not an issue, have 8GB ram, intel i7, 64bit excel... any ideas why this may be happening? Also is there any alternative solution using different formula? Appreciate any feedback..

 

[billszysz]

Hi whada,

Use helper column with formula like this below (assume that your data is in range is A2:A10000)
B2 and copy down
=A2-(ROW()-ROW($A$2)+1)/100000
C2 and copy down
=RANK(B2,$B$2:$B$10000)

 

[usmanali81]

thanks a lot .. God bless u .. this formula worked the way i wanted the results

 

[MikeB6453]

Ok, I've read through the thread and it appears to resolve the problem that I'm struggling with (duplicate skipping ranks) however I need to add in additional conditions. The scenario competition results where there can be a joint first place and a second place still exists, it doesn't skip to third place.

So to clarify I'm interested in the formula that goes into the last three columns,

Column K is the easiest, which just needs needs a non-duplicate skipping ranking for everything in the category of column A (category)
*current formula is : =SUMPRODUCT(--(A$2:A$1006=$A2)*($J2< J$2:J$1006))+1

Column L adds an additional condition, as above but also for everything that matches column C (Age section)
*current formula is : =SUMPRODUCT(--(A$2:A$1006=$A2)*($J2< J$2:J$1006),(C$2:C$1006=$C2)*($J2< J$2:J$1006))+1

Finally column M, again as above with the first two conditions and additionally where everything matches in column F (Genre)
*current formula is : =SUMPRODUCT(--(A$2:A$1006=$A2)*($J2< J$2:J$1006),(C$2:C$1006=$C2)*($J2< J$2:J$1006),(F$2:F$1006=F2)*($J2< J$2:J$1006))+1

Can you have a look at my formula and let me know where I'm going wrong:

[TABLE="width: 1345"]
<tbody>[TR]
[TD]Category[/TD]
[TD]Performance Number[/TD]
[TD]Age Section[/TD]
[TD]Dance School[/TD]
[TD]Performance Name[/TD]
[TD]Genre[/TD]
[TD]Judge 1[/TD]
[TD]Judge 2[/TD]
[TD]Judge 3[/TD]
[TD]TOTAL[/TD]
[TD]Category Ranking[/TD]
[TD]Age Section Ranking[/TD]
[TD]Age & Genre Ranking[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Solo's[/TD]
[TD]1[/TD]
[TD]Babies[/TD]
[TD]Dance School 1[/TD]
[TD]Dancer 1[/TD]
[TD]Classic[/TD]
[TD]32[/TD]
[TD]25[/TD]
[TD]35[/TD]
[TD]92[/TD]
[TD]14[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Solo's[/TD]
[TD]2[/TD]
[TD]Babies[/TD]
[TD]Dance School 2[/TD]
[TD]Dancer 2[/TD]
[TD]Classic[/TD]
[TD]36[/TD]
[TD]29[/TD]
[TD]39[/TD]
[TD]104[/TD]
[TD]6[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Solo's[/TD]
[TD]3[/TD]
[TD]Babies[/TD]
[TD]Dance School 3[/TD]
[TD]Dancer 3[/TD]
[TD]Classic[/TD]
[TD]36[/TD]
[TD]29[/TD]
[TD]39[/TD]
[TD]104[/TD]
[TD]6[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Solo's[/TD]
[TD]4[/TD]
[TD]Kids[/TD]
[TD]Dance School 4[/TD]
[TD]Dancer 4[/TD]
[TD]Classic[/TD]
[TD]34[/TD]
[TD]27[/TD]
[TD]37[/TD]
[TD]98[/TD]
[TD]10[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Solo's[/TD]
[TD]5[/TD]
[TD]Kids[/TD]
[TD]Dance School 5[/TD]
[TD]Dancer 5[/TD]
[TD]Classic[/TD]
[TD]38[/TD]
[TD]31[/TD]
[TD]41[/TD]
[TD]110[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Solo's[/TD]
[TD]6[/TD]
[TD]Kids[/TD]
[TD]Dance School 6[/TD]
[TD]Dancer 6[/TD]
[TD]Classic[/TD]
[TD]34[/TD]
[TD]27[/TD]
[TD]37[/TD]
[TD]98[/TD]
[TD]10[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Solo's[/TD]
[TD]7[/TD]
[TD]Kids[/TD]
[TD]Dance School 7[/TD]
[TD]Dancer 7[/TD]
[TD]Classic[/TD]
[TD]38[/TD]
[TD]31[/TD]
[TD]41[/TD]
[TD]110[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Solo's[/TD]
[TD]8[/TD]
[TD]Teens[/TD]
[TD]Dance School 8[/TD]
[TD]Dancer 8[/TD]
[TD]Modern[/TD]
[TD]35[/TD]
[TD]28[/TD]
[TD]38[/TD]
[TD]101[/TD]
[TD]8[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Solo's[/TD]
[TD]9[/TD]
[TD]Teens[/TD]
[TD]Dance School 9[/TD]
[TD]Dancer 9[/TD]
[TD]Modern[/TD]
[TD]35[/TD]
[TD]28[/TD]
[TD]38[/TD]
[TD]101[/TD]
[TD]8[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Solo's[/TD]
[TD]10[/TD]
[TD]Teens[/TD]
[TD]Dance School 10[/TD]
[TD]Dancer 10[/TD]
[TD]Modern[/TD]
[TD]39[/TD]
[TD]32[/TD]
[TD]42[/TD]
[TD]113[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Solo's[/TD]
[TD]11[/TD]
[TD]Toddlers[/TD]
[TD]Dance School 11[/TD]
[TD]Dancer 11[/TD]
[TD]Classic[/TD]
[TD]33[/TD]
[TD]26[/TD]
[TD]36[/TD]
[TD]95[/TD]
[TD]12[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Solo's[/TD]
[TD]12[/TD]
[TD]Toddlers[/TD]
[TD]Dance School 12[/TD]
[TD]Dancer 12[/TD]
[TD]Classic[/TD]
[TD]37[/TD]
[TD]30[/TD]
[TD]40[/TD]
[TD]107[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Solo's[/TD]
[TD]13[/TD]
[TD]Toddlers[/TD]
[TD]Dance School 13[/TD]
[TD]Dancer 13[/TD]
[TD]Classic[/TD]
[TD]33[/TD]
[TD]26[/TD]
[TD]36[/TD]
[TD]95[/TD]
[TD]12[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Solo's[/TD]
[TD]14[/TD]
[TD]Toddlers[/TD]
[TD]Dance School 14[/TD]
[TD]Dancer 14[/TD]
[TD]Classic[/TD]
[TD]37[/TD]
[TD]30[/TD]
[TD]40[/TD]
[TD]107[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Any help advise would be very much appreciated!!!!

 

[Joe4]

MikeB6453 re-posted his question here: https://www.mrexcel.com/forum/excel...-duplicates-do-not-cause-skip-conditions.html
Please post all replies there.

Mike,
Please do not post the same question multiple times. Per forum rules, posts of a duplicate nature will be locked or deleted (rule #12 here: Forum Rules). It is generally better to post your question to a new thread as opposed to posting it to an existing old thread, unless you have a specific question regarding the original question asked in the thread.

 

[MikeB6453]

MikeB6453 re-posted his question here: https://www.mrexcel.com/forum/excel...-duplicates-do-not-cause-skip-conditions.html
Please post all replies there.

Mike,
Please do not post the same question multiple times. Per forum rules, posts of a duplicate nature will be locked or deleted (rule #12 here: Forum Rules). It is generally better to post your question to a new thread as opposed to posting it to an existing old thread, unless you have a specific question regarding the original question asked in the thread.

Hi Joe,

Apologies, it was my first time posting.
To make matters worse I think my duplicate post is also in the wrong section but I didn't know how to move it.

I reposted because although this thread answers part of the question, it my question is slightly more complex.

Kind Regards
Mike

 

[Joe4]

To make matters worse I think my duplicate post is also in the wrong section but I didn't know how to move it.

Yes, I moved it for you.

I reposted because although this thread answers part of the question, it my question is slightly more complex.

It is usually best to post it to a new thread. You can include links to other threads which you may think are helpful. Many times, people who were active on old threads aren't around much anymore. Also, there is a "Zero Reply Posts" listing that many people use to look for new unanswered questions. Posting to existing threads will not show up on that list. So you are more likely to get "looks" (and hopefully help) by posting to your own new thread.

 

[candycanepanda]

[FONT=&quot]This formula will allow ties and not skip in ranks when scoring see cell F35 for the example [/FONT]
https://docs.google.com/spreadsheets/d/1Y1AHRn-bRnVex-tFwbPV9bN4t46usrFAvRPsUPUn6i4/edit?usp=sharing


[FONT=&quot]=SUMPRODUCT((DD34<$F34:$DT34)/COUNTIF($F34:$DT34,$F34:$DT34))+1[/FONT]

 

[candycanepanda]

*********** sorry cell F40 in my google sheet *************