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:

Category	Performance Number	Age Section	Dance School	Performance Name	Genre	Judge 1	Judge 2	Judge 3	TOTAL	Category Ranking	Age Section Ranking	Age & Genre Ranking
Solo's	1	Babies	Dance School 1	Dancer 1	Classic	32	25	35	92	14	3	3
Solo's	2	Babies	Dance School 2	Dancer 2	Classic	36	29	39	104	6	1	1
Solo's	3	Babies	Dance School 3	Dancer 3	Classic	36	29	39	104	6	1	1
Solo's	4	Kids	Dance School 4	Dancer 4	Classic	34	27	37	98	10	3	3
Solo's	5	Kids	Dance School 5	Dancer 5	Classic	38	31	41	110	2	1	1
Solo's	6	Kids	Dance School 6	Dancer 6	Classic	34	27	37	98	10	3	3
Solo's	7	Kids	Dance School 7	Dancer 7	Classic	38	31	41	110	2	1	1
Solo's	8	Teens	Dance School 8	Dancer 8	Modern	35	28	38	101	8	2	2
Solo's	9	Teens	Dance School 9	Dancer 9	Modern	35	28	38	101	8	2	2
Solo's	10	Teens	Dance School 10	Dancer 10	Modern	39	32	42	113	1	1	1
Solo's	11	Toddlers	Dance School 11	Dancer 11	Classic	33	26	36	95	12	3	3
Solo's	12	Toddlers	Dance School 12	Dancer 12	Classic	37	30	40	107	4	1	1
Solo's	13	Toddlers	Dance School 13	Dancer 13	Classic	33	26	36	95	12	3	3
Solo's	14	Toddlers	Dance School 14	Dancer 14	Classic	37	30	40	107	4	1	1

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.).