Conditional Ranking

honkin

Board Regular
Joined
Mar 20, 2012
Messages
66
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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,135
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

Active Member
Joined
Mar 5, 2018
Messages
296
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.

ABCDEFGHIJ
1DateTimeNumberWorkerRank
21/1/201909:36871147
31/1/201909:36271147
41/1/201919:12505094
51/2/201914:24295882
61/2/201914:24295882
71/3/201900:00878727
81/3/201909:36593141
91/3/201909:36633141
101/3/201909:36943141
111/3/201909:36983141
121/5/201909:36601355
131/5/201909:36751355

<tbody>
</tbody>
Sheet3

Worksheet Formulas
CellFormula
H2
to
H181
=SUMIFS($F$2:$F$181,$A$2:$A$181,A2,$B$2:$B$181,B2)
J2
to J181
=RANK.EQ(H2,$H$2:$H$181,0)

<tbody>
</tbody>

<tbody>
</tbody>

Is that close?
 

honkin

Board Regular
Joined
Mar 20, 2012
Messages
66
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.

ABCDEFGHIJ
1DateTimeNumberWorkerRank
21/1/201909:36871147
31/1/201909:36271147
41/1/201919:12505094
51/2/201914:24295882
61/2/201914:24295882
71/3/201900:00878727
81/3/201909:36593141
91/3/201909:36633141
101/3/201909:36943141
111/3/201909:36983141
121/5/201909:36601355
131/5/201909:36751355

<tbody>
</tbody>
Sheet3

Worksheet Formulas
CellFormula
H2
to
H181
=SUMIFS($F$2:$F$181,$A$2:$A$181,A2,$B$2:$B$181,B2)
J2
to J181
=RANK.EQ(H2,$H$2:$H$181,0)

<tbody>
</tbody>

<tbody>
</tbody>

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

1DateTimeNumberRank
21/1/201909:3653
31/1/201909:3631
41/1/201909:3686
51/1/201909:3664
61/1/201909:3675
71/1/201909:3697
81/1/201909:3642

<tbody>
</tbody>

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

cheers
 

honkin

Board Regular
Joined
Mar 20, 2012
Messages
66
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:-

Code:
=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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,135
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?
 
Last edited:

Toadstool

Active Member
Joined
Mar 5, 2018
Messages
296
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

Board Regular
Joined
Mar 20, 2012
Messages
66
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


F
9
G
8
03
98
1412
03
75
64
1613
03
98
2215
1110
1412
2215
109

<tbody>
</tbody>

This lot starts at a ranking of 2

52
52
74
85
63
106

<colgroup><col span="2"></colgroup><tbody>
</tbody>

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

72
51
84
156
84
115
208
167

<colgroup><col span="2"></colgroup><tbody>
</tbody>


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?
 
Last edited:

honkin

Board Regular
Joined
Mar 20, 2012
Messages
66
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

Active Member
Joined
Mar 5, 2018
Messages
296
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.
 

Forum statistics

Threads
1,077,851
Messages
5,336,759
Members
399,101
Latest member
BharathSanthanam

Some videos you may like

This Week's Hot Topics

Top