locked
Consecutive Finishes RRS feed

  • Question

  • In Excel 365 I have a table called Results which contains the following columns and sample data:

    Date --------- Player -- Result

    01/01/2019 - Danny -- 1st

    01/01/2019 - Craig -- 2nd

    01/01/2019 - Alex -- 3rd

    01/01/2019 - James -- LOW

    08/01/2019 - James -- 1st

    08/01/2019 - Alex --- 2nd

    08/01/2019 - Katie -- LOW

    08/01/2019 - Danny - LOW

    15/01/2019 - Craig -- 1st

    15/01/2019 - Katie -- 2nd

    15/01/2019 - Alex -- 3rd

    15/01/2019 - James -- LOW

    What I want is to work out somebody's consecutive finishes in a 4th column. I want to differentiate between a run of consecutive finishes in the top 7, or consecutive finishes outside the top 7. The player is in the top 7 if the left 3 characters of the 3rd column are "1st", "2nd" ... "7th". The player is not in the top 7 if the left 3 characters are "LOW" or "FIR". For each consecutive top 7 finish, their score must increment by 1, but for a non top 7 finish their score will go to -1 and decrement by one (until they finish in the top 7).

    Looking at the sample data above, the players would have the following consecutive finishes:

    In date order - i.e. 1st then 8th then 15th:

    Danny: 1, -1 (Danny was in the top 7, then outside the top 7, then he didn't play so his consecutive run is unaffected)

    Craig: 1, 2 (Craig didn't play on the 8th, but the 2 times he did play he finished in the top 7, so his top 7 run increments)

    James: -1, 1, -1 (James was not top 7, then top 7, then not top 7)

    Katie: -1, 1

    Alex: 1, 2, 3

    I've performed a similar formula but the data was set out differently - I need the data in a table format but whereas previously I was  comparing adjacent cells I no longer have this option. Any help much appreciated. Thanks.

    Wednesday, February 6, 2019 9:07 PM

Answers

All replies

  • Hi,

    I'm not very clearly about your requirement, could you provide a sample file and the result table you need to our email address?

    If possible, you could send this file to our email address:

    ibsofc@microsoft.com

    Note: Please add the URL of the case in the email subject or body, please make sure that you have hidden your private information.

    I'm glad to help you.

    Regards,

    Emi


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact: tnsf@microsoft.com.

    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Office 2019.

    Thursday, February 7, 2019 7:20 AM
  • Hi, 

    Just checking in to see if the information was helpful. Please let us know if you would like further assistance.

    Regards,

    Emi


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact: tnsf@microsoft.com.

    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Office 2019.

    Friday, February 8, 2019 7:37 AM
  • I sent an email including a sample file to the emil address provided on Monday 11/02/2019 at 14:36 GMT.
    Monday, February 18, 2019 9:51 AM
  • I haven't received a response to the email mentioned above, so I'll further explain my requirements below...

    There's two parts to the picture below, the data as it is input in columns A-D (although column D is what I want a formula for), then columns F-I is te same data sorted for demo purposes. So, I want to calculate a player's "consecutive runs" in what is a game of cards. The consecutive runs can be defined as follows; a top 7 finish is a "positive consecutive run", so every time the first 3 letters in column C contain the text "1st", "2nd" ... "7th" this represents a positive run. If the first 3 letters in column C are "LOW" or "FIR", this is a negative run. The real world event here is did the player finish in the top 7 or not and comparing this with previous weeks.

    The table in columns F-I demonstrates this better. We can see the player Danny has finished 1st, 4th & 7th and during these 3 weeks his consecutive finishes (as per the date column) have incremented from 1 - 2 - 3. On Danny's 4th week he finished LOW (outside the top 7), this therefore begins a negative run and so the Consecutive column records -1. A negative run is illustrated by the player Laura who has 4 finishes outside the top 7 and therefore goes from -1 to -2 to -3 to -4. The player Steve finishes in the top 7, the not in, then in, then not in. As such is consecutive runs is 1, then -1, then 1, then -1. Dave only has 3 entries and Lee has 1 entry to demonstrate that it's not the same players every week. Dave didn't play on the date 08/01/2019 but his consecutive run should continue despite him missing a week (it shouldn't reset itself).

    I hope this explains my requirement.

    Sunday, February 24, 2019 9:06 PM
  • Hi PetyBaelish,

    I'd like to confirm and ask you:
      (1) Can we use macro (VBA), instead of Formula in cells?
      (2) Could you provide general rules, make it abstract?
           I'm afraid that explaining by concrete values is hard to understand and may lack some rules. 
    Regards,

    Ashidacchi -- https://ssl01.rocketnet.jp/hokusosha.com/default.html

    Monday, February 25, 2019 3:55 AM
  • Excel 365 with Power Query (aka Get & Transform)
    No formulas, no VBA macro.
    With Table.Group(... , GroupType.Local)
    http://www.mediafire.com/file/xt2886obyzcp5s4/02_24_19a.xlsx/file
    http://www.mediafire.com/file/45iz7hvo5ipwa5c/02_24_19a.pdf/file

    • Proposed as answer by Ashidacchi Monday, February 25, 2019 8:11 AM
    • Marked as answer by PetyrBaelish Wednesday, March 6, 2019 10:39 AM
    Monday, February 25, 2019 4:03 AM
  • Herbert's solution has worked perfectly, thank you very much :)
    Wednesday, March 6, 2019 10:40 AM