Page 2 of 2 FirstFirst 12
Results 11 to 18 of 18

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

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

    Default 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)
    Last edited by AlphaFrog; Sep 4th, 2019 at 03:11 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.

  2. #12
    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

    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.

    Date Time Number Rank
    31/12/2018 15:25 0 1
    31/12/2018 15:25 6 2
    31/12/2018 15:25 8 3
    31/12/2018 15:25 13 4
    31/12/2018 15:25 14 5
    31/12/2018 15:25 20 6
    31/12/2018 15:25 21 7
    31/12/2018 15:30 3 1
    31/12/2018 15:30 5 2
    31/12/2018 15:30 7 3
    31/12/2018 15:30 9 4
    31/12/2018 15:30 9 4
    31/12/2018 15:30 11 6
    31/12/2018 15:30 11 6
    31/12/2018 15:30 13 8
    31/12/2018 15:30 14 9
    31/12/2018 15:30 16 10
    31/12/2018 15:30 16 10
    31/12/2018 15:30 19 12
    31/12/2018 15:30 19 12
    31/12/2018 15:30 23 14
    31/12/2018 15:35 4 1
    31/12/2018 15:35 4 1
    31/12/2018 15:35 4 1
    31/12/2018 15:35 5 4
    31/12/2018 15:35 8 5
    31/12/2018 15:35 9 6
    31/12/2018 15:35 9 6
    31/12/2018 15:35 10 8
    31/12/2018 15:35 13 9
    31/12/2018 15:45 2 1
    31/12/2018 15:45 5 2
    31/12/2018 15:45 6 3
    31/12/2018 15:45 6 3
    31/12/2018 15:45 7 5
    31/12/2018 15:45 7 5
    31/12/2018 15:45 9 7
    31/12/2018 15:45 14 8
    31/12/2018 15:45 15 9

    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

  3. #13
    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

    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.

    0 1
    0 2
    0 3
    6 4
    7 5
    9 6
    9 7
    9 8
    10 9
    11 10
    14 11
    14 12
    16 13
    22 14
    22 15

    Thanks so much for your help, AlphaFrog

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

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

    Default Re: Question On Conditional Ranking

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

  5. #15
    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

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

    cheers

    Quote Originally Posted by AlphaFrog View Post
    =countifs(a:a,a2,b:b,b2,f:f,"<"&f2)+1

  6. #16
    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 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

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

    Default Re: Question On Conditional Ranking

    Yes it should work with decimals.
    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.

  8. #18
    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

    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

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
  •