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.
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
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.
Paste your Excel data to the forum...
MrExcel HTML Maker or Excel Jeanie
How to post your vba code
[CODE]your VBA code here[/CODE]
The # button in the forum's editor will apply CODE tags around your selected text.
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 1 Date Time Number Worker Rank 2 1/1/2019 09:36 87 114 7 3 1/1/2019 09:36 27 114 7 4 1/1/2019 19:12 50 50 94 5 1/2/2019 14:24 29 58 82 6 1/2/2019 14:24 29 58 82 7 1/3/2019 00:00 87 87 27 8 1/3/2019 09:36 59 314 1 9 1/3/2019 09:36 63 314 1 10 1/3/2019 09:36 94 314 1 11 1/3/2019 09:36 98 314 1 12 1/5/2019 09:36 60 135 5 13 1/5/2019 09:36 75 135 5 Sheet3
Worksheet Formulas
Cell Formula 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)
Is that close?
Life advice found on a book of matches: "Keep cool. Keep away from children."
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
1 Date Time Number Rank 2 1/1/2019 09:36 5 3 3 1/1/2019 09:36 3 1 4 1/1/2019 09:36 8 6 5 1/1/2019 09:36 6 4 6 1/1/2019 09:36 7 5 7 1/1/2019 09:36 9 7 8 1/1/2019 09:36 4 2
So you can see dates and times are the same and the numbers have been ranked from smallest to highest.
cheers
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:-
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?Code:=RANK(F2,$F$2:$F$17,1)
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 by AlphaFrog; Sep 3rd, 2019 at 06:27 PM.
Paste your Excel data to the forum...
MrExcel HTML Maker or Excel Jeanie
How to post your vba code
[CODE]your VBA code here[/CODE]
The # button in the forum's editor will apply CODE tags around your selected text.
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?
Life advice found on a book of matches: "Keep cool. Keep away from children."
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
9G
80 3 9 8 14 12 0 3 7 5 6 4 16 13 0 3 9 8 22 15 11 10 14 12 22 15 10 9
This lot starts at a ranking of 2
5 2 5 2 7 4 8 5 6 3 10 6
This lot starts at 1 but misses 3. The 2 x 8 should both be 3 and not 4.
7 2 5 1 8 4 15 6 8 4 11 5 20 8 16 7
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
Last edited by honkin; Sep 4th, 2019 at 05:58 AM.
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
Life advice found on a book of matches: "Keep cool. Keep away from children."
Like this thread? Share it with others