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

Maybe something like this in row 2...

=COUNTIFS(A:A,A2,B:B,B2,F:F,">="&F2)

Change the greater sign to a lesser sign if needed.

 

[Toadstool]

Re: Question On Conditional Ranking

I'd need to add a worker column to get all the same date/time numbers added together, then I rank those.

	A	B	C	D	E	F	G	H	I	J
Worker	Rank

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]Time[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]Number[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]1/1/2019[/TD]
[TD="align: center"]09:36[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]87[/TD]
[TD="align: right"][/TD]
[TD="align: right"]114[/TD]
[TD="align: right"][/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]1/1/2019[/TD]
[TD="align: center"]09:36[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]27[/TD]
[TD="align: right"][/TD]
[TD="align: right"]114[/TD]
[TD="align: right"][/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]1/1/2019[/TD]
[TD="align: center"]19:12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]50[/TD]
[TD="align: right"][/TD]
[TD="align: right"]50[/TD]
[TD="align: right"][/TD]
[TD="align: right"]94[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]1/2/2019[/TD]
[TD="align: center"]14:24[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]29[/TD]
[TD="align: right"][/TD]
[TD="align: right"]58[/TD]
[TD="align: right"][/TD]
[TD="align: right"]82[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]1/2/2019[/TD]
[TD="align: center"]14:24[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]29[/TD]
[TD="align: right"][/TD]
[TD="align: right"]58[/TD]
[TD="align: right"][/TD]
[TD="align: right"]82[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]1/3/2019[/TD]
[TD="align: center"]00:00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]87[/TD]
[TD="align: right"][/TD]
[TD="align: right"]87[/TD]
[TD="align: right"][/TD]
[TD="align: right"]27[/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]1/3/2019[/TD]
[TD="align: center"]09:36[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]59[/TD]
[TD="align: right"][/TD]
[TD="align: right"]314[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]1/3/2019[/TD]
[TD="align: center"]09:36[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]63[/TD]
[TD="align: right"][/TD]
[TD="align: right"]314[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]1/3/2019[/TD]
[TD="align: center"]09:36[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]94[/TD]
[TD="align: right"][/TD]
[TD="align: right"]314[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]1/3/2019[/TD]
[TD="align: center"]09:36[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]98[/TD]
[TD="align: right"][/TD]
[TD="align: right"]314[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]12[/TD]
[TD="align: center"]1/5/2019[/TD]
[TD="align: center"]09:36[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]60[/TD]
[TD="align: right"][/TD]
[TD="align: right"]135[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]13[/TD]
[TD="align: center"]1/5/2019[/TD]
[TD="align: center"]09:36[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]75[/TD]
[TD="align: right"][/TD]
[TD="align: right"]135[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H2
to
H181[/TH]
[TD="align: left"]=SUMIFS($F$2:$F$181,$A$2:$A$181,A2,$B$2:$B$181,B2)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J2
to J181[/TH]
[TD="align: left"]=RANK.EQ(H2,$H$2:$H$181,0)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Is that close?

 

[honkin]

Re: Question On Conditional Ranking

I'd need to add a worker column to get all the same date/time numbers added together, then I rank those.

	A	B	C	D	E	F	G	H	I	J
Worker	Rank

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]Time[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]Number[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]1/1/2019[/TD]
[TD="align: center"]09:36[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]87[/TD]
[TD="align: right"][/TD]
[TD="align: right"]114[/TD]
[TD="align: right"][/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]1/1/2019[/TD]
[TD="align: center"]09:36[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]27[/TD]
[TD="align: right"][/TD]
[TD="align: right"]114[/TD]
[TD="align: right"][/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]1/1/2019[/TD]
[TD="align: center"]19:12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]50[/TD]
[TD="align: right"][/TD]
[TD="align: right"]50[/TD]
[TD="align: right"][/TD]
[TD="align: right"]94[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]1/2/2019[/TD]
[TD="align: center"]14:24[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]29[/TD]
[TD="align: right"][/TD]
[TD="align: right"]58[/TD]
[TD="align: right"][/TD]
[TD="align: right"]82[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]1/2/2019[/TD]
[TD="align: center"]14:24[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]29[/TD]
[TD="align: right"][/TD]
[TD="align: right"]58[/TD]
[TD="align: right"][/TD]
[TD="align: right"]82[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]1/3/2019[/TD]
[TD="align: center"]00:00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]87[/TD]
[TD="align: right"][/TD]
[TD="align: right"]87[/TD]
[TD="align: right"][/TD]
[TD="align: right"]27[/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]1/3/2019[/TD]
[TD="align: center"]09:36[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]59[/TD]
[TD="align: right"][/TD]
[TD="align: right"]314[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]1/3/2019[/TD]
[TD="align: center"]09:36[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]63[/TD]
[TD="align: right"][/TD]
[TD="align: right"]314[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]1/3/2019[/TD]
[TD="align: center"]09:36[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]94[/TD]
[TD="align: right"][/TD]
[TD="align: right"]314[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]1/3/2019[/TD]
[TD="align: center"]09:36[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]98[/TD]
[TD="align: right"][/TD]
[TD="align: right"]314[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]12[/TD]
[TD="align: center"]1/5/2019[/TD]
[TD="align: center"]09:36[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]60[/TD]
[TD="align: right"][/TD]
[TD="align: right"]135[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]13[/TD]
[TD="align: center"]1/5/2019[/TD]
[TD="align: center"]09:36[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]75[/TD]
[TD="align: right"][/TD]
[TD="align: right"]135[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]H2
to
H181[/TH]
[TD="align: left"]=SUMIFS($F$2:$F$181,$A$2:$A$181,A2,$B$2:$B$181,B2)[/TD]
[/TR]
[TR]
[TH]J2
to J181[/TH]
[TD="align: left"]=RANK.EQ(H2,$H$2:$H$181,0)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Is that close?

cheers Toadstool, but adding the numbers in F together is not the option.

Here is what a correct one would look like. I am just unsure how to achieve it

[TABLE="class: cms_table"]
<tbody>[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]Time[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]Number[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD]Rank[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]1/1/2019[/TD]
[TD="align: center"]09:36[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]1/1/2019[/TD]
[TD="align: center"]09:36[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]1/1/2019[/TD]
[TD="align: center"]09:36[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]1/1/2019[/TD]
[TD="align: center"]09:36[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]1/1/2019[/TD]
[TD="align: center"]09:36[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]1/1/2019[/TD]
[TD="align: center"]09:36[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]1/1/2019[/TD]
[TD="align: center"]09:36[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]

So you can see dates and times are the same and the numbers have been ranked from smallest to highest.

cheers

 

[honkin]

Re: Question On Conditional Ranking

Cheers AlphaFrog, but no result there at all. Numbers appear in each cell, but it is not ranking them accurately. If I use -, then all results are 0

If I was just using RANK, the formula would be:-

=RANK(F2,$F$2:$F$17,1)

That would rank the cells in the range F2:F17 from smallest to largest. What I need it to do first is check if date and time are correct first, so F2:F17 would be ranked, but the next range is F18:F23 and so on. So how to get Excel to determine each range from the matching dates and times and then rank the numbers only in that range?

cheers

Maybe something like this in row 2...

=COUNTIFS(A:A,A2,B:B,B2,F:F,">="&F2)

Change the greater sign to a lesser sign if needed.

 

[AlphaFrog]

Re: Question On Conditional Ranking

Did you try this...

=COUNTIFS(A:A,A2,B:B,B2,F:F,"<="&F2)

For a given date, are all the Time values exactly the same including seconds as seen in the formula bar?

 

[Toadstool]

Re: Question On Conditional Ranking

That's done with =(RANK.EQ(F2,$F$2:$F$8,1)+COUNTIF($F$2:F2,F2)-1) but please can you give data and expected results when times and dates differ?

 

[honkin]

Re: Question On Conditional Ranking

Hi AlphaFrog

That's pretty close, but it starts the ranking from 3, which is odd. Here are the numbers on the left and the ranking on the right. The 3 cells in F which are 0 should all have the ranking 1 by rights, being the lowest. Instead they start at 3, but it then appears to rank them in order. Just needs it to start from 1


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

This lot starts at a ranking of 2

[TABLE="width: 168"]
<colgroup><col span="2"></colgroup><tbody>[TR]
[TD]5[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD="align: right"]6[/TD]
[/TR]
</tbody>[/TABLE]

This lot starts at 1 but misses 3. The 2 x 8 should both be 3 and not 4.

[TABLE="width: 168"]
<colgroup><col span="2"></colgroup><tbody>[TR]
[TD]7[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD="align: right"]7[/TD]
[/TR]
</tbody>[/TABLE]


To your question, yes, all the time values are exactly the same. So for 12:00 in the formula bar it shows 12:00:00 for all of the ones that are that time and date.

cheers

Did you try this...

=COUNTIFS(A:A,A2,B:B,B2,F:F,"<="&F2)

For a given date, are all the Time values exactly the same including seconds as seen in the formula bar?

 

[honkin]

Re: Question On Conditional Ranking

Hi Toadstool

I am not sure what you asking for. There are groups of items which share date and time; say 01/01/19 12:45...there might be 12 items. Then there might be 01/01/19 and 13:15 with 10 items and 01/01/10 and 13:30 with 7 then 02/01/19 and 11:15 with 20 items and so on through 140,000 rows going from 01/01/19 to 31/08/19. Each group that has matching date and time needs to be ranked on the numbers in column F.

Does that make it any clearer, as I am not 100% what you want me to supply

Thanks so much in advance

 

[Toadstool]

Re: Question On Conditional Ranking

Hi Toadstool

........ I am not 100% what you want me to supply....

You supplied an example where all the dates/times were 1/1/2019 09:36 but I'd like to see expected ranking when you've some duplicate date/time and some with different times and different dates.