Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Thread: Conditional Ranking
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Mar 2012
    Posts
    46
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Conditional Ranking

    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

  2. #2
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    16,086
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    7 Thread(s)

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

  3. #3
    Board Regular Toadstool's Avatar
    Join Date
    Mar 2018
    Posts
    225
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    2 Thread(s)

    Default 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
    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."

  4. #4
    New Member
    Join Date
    Mar 2012
    Posts
    46
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Question On Conditional Ranking

    Quote Originally Posted by Toadstool View Post
    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?
    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

  5. #5
    New Member
    Join Date
    Mar 2012
    Posts
    46
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

    Quote Originally Posted by AlphaFrog View Post
    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.

  6. #6
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    16,086
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    7 Thread(s)

    Default 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 by AlphaFrog; Sep 3rd, 2019 at 05: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.

  7. #7
    Board Regular Toadstool's Avatar
    Join Date
    Mar 2018
    Posts
    225
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    2 Thread(s)

    Default 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?
    Life advice found on a book of matches: "Keep cool. Keep away from children."

  8. #8
    New Member
    Join Date
    Mar 2012
    Posts
    46
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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
    0 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
    Quote Originally Posted by AlphaFrog View Post
    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 honkin; Sep 4th, 2019 at 04:58 AM.

  9. #9
    New Member
    Join Date
    Mar 2012
    Posts
    46
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

  10. #10
    Board Regular Toadstool's Avatar
    Join Date
    Mar 2018
    Posts
    225
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Question On Conditional Ranking

    Quote Originally Posted by honkin View Post
    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.
    Life advice found on a book of matches: "Keep cool. Keep away from children."

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •