Conditional Ranking

[honkin]

hi

I am having some issues trying to get quite a lot of data ranked correctly. I've done some reading and possibly I need to use COUNTIF rather than RANK.

Here is what I am trying to do.

I have around 140,000 rows of data with the first two columns being date and time. I have numbers in column F and I need to rank the items based on those numbers, but I want the ones with the same date & time to be ranked with the others of the same date and time, if that makes sense. So everything with A being 01/01/2019 and B being 14:45 would be ranked based on the numbers in column F. That would continue right through the sheet, ranking items where A & B match.

Is this possible at all?

Thanks so much in advance

 

[AlphaFrog]

Re: Question On Conditional Ranking

Try this starting in G2 on down to breakup ties (presumes any given date-time group is less than 1000 consecutive rows).

=COUNTIFS(A:A,A2,B:B,B2,F:F,"<="&F2)-COUNTIFS(A3:A1000,A2,B3:B1000,B2,F3:F1000,F2)

 

[honkin]

Re: Question On Conditional Ranking

OK, understood Toadstool

So here are some from the 2018 calendar year file. It's all the same stuff; groups of data where dates are the same and different times. I added the correct ranking manually and sorted them into rank order to make viewing easier.

There is nothing wrong with missing a ranking number, as if there are 3 that rank as 1, then the next number is 4 and not 2. That's a pretty standard ranking convention.

[TABLE="width: 368"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Time[/TD]
[TD]Number[/TD]
[TD]Rank[/TD]
[/TR]
[TR]
[TD]31/12/2018[/TD]
[TD]15:25[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]31/12/2018[/TD]
[TD]15:25[/TD]
[TD]6[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]31/12/2018[/TD]
[TD]15:25[/TD]
[TD]8[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]31/12/2018[/TD]
[TD]15:25[/TD]
[TD]13[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]31/12/2018[/TD]
[TD]15:25[/TD]
[TD]14[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]31/12/2018[/TD]
[TD]15:25[/TD]
[TD]20[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]31/12/2018[/TD]
[TD]15:25[/TD]
[TD]21[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]31/12/2018[/TD]
[TD]15:30[/TD]
[TD]3[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]31/12/2018[/TD]
[TD]15:30[/TD]
[TD]5[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]31/12/2018[/TD]
[TD]15:30[/TD]
[TD]7[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]31/12/2018[/TD]
[TD]15:30[/TD]
[TD]9[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]31/12/2018[/TD]
[TD]15:30[/TD]
[TD]9[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]31/12/2018[/TD]
[TD]15:30[/TD]
[TD]11[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]31/12/2018[/TD]
[TD]15:30[/TD]
[TD]11[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]31/12/2018[/TD]
[TD]15:30[/TD]
[TD]13[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]31/12/2018[/TD]
[TD]15:30[/TD]
[TD]14[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]31/12/2018[/TD]
[TD]15:30[/TD]
[TD]16[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]31/12/2018[/TD]
[TD]15:30[/TD]
[TD]16[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]31/12/2018[/TD]
[TD]15:30[/TD]
[TD]19[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]31/12/2018[/TD]
[TD]15:30[/TD]
[TD]19[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]31/12/2018[/TD]
[TD]15:30[/TD]
[TD]23[/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD]31/12/2018[/TD]
[TD]15:35[/TD]
[TD]4[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]31/12/2018[/TD]
[TD]15:35[/TD]
[TD]4[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]31/12/2018[/TD]
[TD]15:35[/TD]
[TD]4[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]31/12/2018[/TD]
[TD]15:35[/TD]
[TD]5[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]31/12/2018[/TD]
[TD]15:35[/TD]
[TD]8[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]31/12/2018[/TD]
[TD]15:35[/TD]
[TD]9[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]31/12/2018[/TD]
[TD]15:35[/TD]
[TD]9[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]31/12/2018[/TD]
[TD]15:35[/TD]
[TD]10[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]31/12/2018[/TD]
[TD]15:35[/TD]
[TD]13[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]31/12/2018[/TD]
[TD]15:45[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]31/12/2018[/TD]
[TD]15:45[/TD]
[TD]5[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]31/12/2018[/TD]
[TD]15:45[/TD]
[TD]6[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]31/12/2018[/TD]
[TD]15:45[/TD]
[TD]6[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]31/12/2018[/TD]
[TD]15:45[/TD]
[TD]7[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]31/12/2018[/TD]
[TD]15:45[/TD]
[TD]7[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]31/12/2018[/TD]
[TD]15:45[/TD]
[TD]9[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]31/12/2018[/TD]
[TD]15:45[/TD]
[TD]14[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]31/12/2018[/TD]
[TD]15:45[/TD]
[TD]15[/TD]
[TD]9[/TD]
[/TR]
</tbody>[/TABLE]

I hope that makes it clearer. The dates span all of 2018 and in the 2019 file, it spans all of 2019 to date. There are anywhere from 4 or 5 per group to 20 or so.

cheers

 

[honkin]

Re: Question On Conditional Ranking

Thanks so much for your reply, AlphaFrog. I actually don't want ties broken up, as that is not the standard ranking convention. Think of it like a horse race or any event where competitors race. If there is a dead head for 1st, the next placing is 3rd, not 2nd; a tie for 3rd and the next placing is 5th and so on. It certainly starts at the correct ranking number now, with the first ranking always being 1, but if we can eliminate the breakup of ties, it will be absolutely perfect.

To your question, yes, the most you'd find in any group is around 20 or so; so maybe I can cap it at 30 for safety. I can always adjust that number

Here is one group I ran it on and you can see it split the three entries which all had a zero where they should all rank as 1. I have sorted them so it is easier to view.

[TABLE="width: 168"]
<colgroup><col span="2"></colgroup><tbody>[TR]
[TD]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD="align: right"]15[/TD]
[/TR]
</tbody>[/TABLE]

Thanks so much for your help, AlphaFrog

Try this starting in G2 on down to breakup ties (presumes any given date-time group is less than 1000 consecutive rows).

=COUNTIFS(A:A,A2,B:B,B2,F:F,"<="&F2)-COUNTIFS(A3:A1000,A2,B3:B1000,B2,F3:F1000,F2)

 

[AlphaFrog]

Re: Question On Conditional Ranking

i actually don't want ties broken up, as that is not the standard ranking convention. Think of it like a horse race or any event where competitors race. If there is a dead head for 1st, the next placing is 3rd, not 2nd; a tie for 3rd and the next placing is 5th and so on. It certainly starts at the correct ranking number now, with the first ranking always being 1, but if we can eliminate the breakup of ties, it will be absolutely perfect.

=countifs(a:a,a2,b:b,b2,f:f,"<"&f2)+1

 

[honkin]

Re: Question On Conditional Ranking

Thanks so much AlphaFrog. That seems to have done the trick.

cheers

=countifs(a:a,a2,b:b,b2,f:f,"<"&f2)+1

 

[honkin]

Re: Question On Conditional Ranking

Hi again AlphaFrog

I do have one quick question re this code, if I may. Should it also work on decimal numbers, as I just tried it on a column of numbers with one and two decimal places and everything showed as ranking 1?

cheers

 

[AlphaFrog]

Re: Question On Conditional Ranking

Yes it should work with decimals.

 

[honkin]

Re: Question On Conditional Ranking

My bad AlphaFrog. I realised late that I had autocalculate turned off. It's a nightmare having it on with 100k+ rows, as once you do anything, it wants to recalc and you're waiting for ages. Once I did a recalculation on the sheet, it picked them up. Sorry for the bother.

cheers