Ranking (where duplicates do not cause a skip) with CONDITIONS

[MikeB6453]

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.

The sheet contains different categories of competition (only Solo's is shown but Duo's, Trio's and Groups will also exist), different age ranges within these categories and finally different Genre's which also need a ranking

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="class: cms_table, width: 1345"]
[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]
[/TABLE]

Any help advise would be very much appreciated!!!!

 

[inactiveuserps07]

Hi, Mike. I'm going to offer a solution. However, it's important to know up front that, in order to work, you need to limit the range to include only as many rows as you have values (i.e., no blanks). For instance, in your sample data set, your formula ranges go to Row 1006; however, in order to work, my suggested formulas would need to only to to Row 15, which is the last row that contains non-blank data.

That said ...


In K2 (and drag-copied down to last row of data):

=ROUND(SUMPRODUCT(--(A$2:A$[COLOR=#ff0000]15[/COLOR]=$A2)*(($J2<J$2:J$[COLOR=#ff0000]15[/COLOR])/COUNTIF($J$2:$J$[COLOR=#ff0000]15[/COLOR],$J$2:$J$[COLOR=#ff0000]15[/COLOR])))+1,0)

In L2 (and drag-copied down to last row of data):

=ROUND(SUMPRODUCT(--(A$2:A$[COLOR=#ff0000]15[/COLOR]=A2)*(C$2:C$[COLOR=#FF0000]15[/COLOR]=C2)*((J2<J$2:J$[COLOR=#FF0000]15[/COLOR])/COUNTIF(J$2:J$[COLOR=#FF0000]15[/COLOR],J$2:J$[COLOR=#FF0000]15[/COLOR])))+1,0)

In M2 (and drag-copied down to last row of data):

=ROUND(SUMPRODUCT(--(A$2:A$[COLOR=#FF0000]15[/COLOR]=A2)*(C$2:C$[COLOR=#FF0000]15[/COLOR]=C2)*(F$2:F$[COLOR=#FF0000]15[/COLOR]=F3)*((J2<J$2:J$[COLOR=#FF0000]15[/COLOR])/COUNTIF(J$2:J$[COLOR=#FF0000]15[/COLOR],J$2:J$[COLOR=#FF0000]15[/COLOR])))+1,0)

(Again, those red range limits need to reflect YOUR actual last row. If it were me, I'd have structured the whole thing as a table and used structured referencing; but as it stands, this is the easiest solution.)

 

[MikeB6453]

Hi Erik,

Thank you very much for taking the time to work out a solution however... that doesn't really work for me - I need to create this sheet for use as an ongoing template, used at over 40 events each year. Quite often it's updated by people who have no knowledge of excel, and I'd like them to be able to enter the data on this sheet without messing with any formula. Sometimes there are 100 entries and on other occasions there are 300+

P.s it also feeds through to a pivot table.


(I did previous have a table version however they kept breaking it - I also tried locking cells but that coursed more trouble than it was worth)

Any ideas of how I could adapt my existing formula to work with the solution offered here:
https://www.mrexcel.com/forum/excel...uplicate-ranks-without-skipping-sequence.html

Kind Regards
Mike

 

[inactiveuserps07]

Mike, the link you reference only has to account for one variable (column) of data, not four.

Again, a solution is possible using a table and structured references. However, keep in mind that this site and others like it are here to offer advice, formula tweaking or small-scale solutions, since it's a volunteer site; it's not really designed to provide free in-depth solutions that would normally require extensive work for which someone would normally have to hire a pro.

Occasionally, someone here (myself included) will have the time and inclination to offer that level of work on a project; I myself currently don't, beyond encouraging you to look again at the table solution with structured references, since that would automatically adjust the formulas to only process through the last row of the table. Solutions incorporating INDIRECT / ADDRESS / COUNT (or COUNTA) could also conceivably work.

 

[inactiveuserps07]

Mike,

Just following up to let you know that, while a solution to your setup is definitely do-able, the formulas I suggested above will not actually work for your uses. I went back and looked, and your "rank" columns aren't entirely cumulative (i.e., you aren't doing "overall" then "category" then "category+age" then "category+age+genre"; you're doing "category-only" then an unrelated "age-only" and then "age+genre"). And the ROUND() portion will occasionally trip things up.

Again, I'd suggest reconsidering using a table with structured references.

And I would definitely go back to accounting for partial sheet protection, as well as including data validation by way of drop-down list options for consistency (and usability) and other failsafes (e.g., forcing TOTAL to remain null until all three judges' scores have been entered, etc.).