Count Skips Between Last Occurrence

  • Sheet1


    IDDateN1N2N3N4N5


    8472/27/20211016172436
    8482/28/202169131422
    8493/1/202126182333
    8503/2/2021915202734
    8513/3/20211922232435
    8523/4/2021613203336
    8533/5/2021314152036
    8543/6/2021112152043
    8553/7/20211115161843
    8563/8/20211630313940
    8573/9/2021320253043
    8583/10/20211820252829
    8593/11/2021210132930
    8603/12/2021518193839
    8613/13/2021511202126
    8623/14/2021712252841
    8633/15/20211337383943
    8643/16/2021310112241
    8653/17/20211516171937
    8663/18/20211824283139
    8673/19/2021513222734
    8683/20/20211516212437
    8693/21/20211314182429
    8703/22/2021918323334
    8713/23/2021112183236
    8723/24/202168132936
    8733/25/2021912162035
    8743/26/2021628333639
    8753/27/202167222332
    8763/28/202126111331


    Sheet2

    #SBLO
    15
    20
    312
    447
    59
    60
    71
    84
    93
    1012
    110
    123
    130
    147
    158
    163
    1711
    185
    1911
    203
    218
    221
    231
    246
    2514
    2615
    279
    282
    294
    3017
    310
    321
    332
    346
    353
    362
    378
    3813
    392
    4020
    4112
    4237
    4313


    Sheet1 contains a list of drawings. The numbers are in Columns C through G. One row for each drawing.

    Sheet2 contains numbers 1-43 of which 5 will be selected for the next drawing.


    In Sheet2 Column B (SBLO) is where I want to place the number of drawings Skipped Between the Last Occurance for each number in Column A (#).


    The Sheet2 example has the results I'm seeking in Column B (SBLO) according to the latest drawings in Sheet1.

    [IMPORTANT] I didn't template the full history of drawings in my Sheet1 example. The #4 on Sheet2 shows 47 skips between last occurance but to save space I didn't list the drawings that far back.

    Ex. 2-6-11-13-31 was the last drawing on Sheet1 so they will have 0 skips between last occurance on Sheet2. The drawing before that on Sheet1 was 6-7-22-23-32. Numbers# 7, 22, 23, 32 on Sheet2 will have 1 skip between last occurance, 6 already has 0, etc. etc.

    Lame attempt below. This is stuff I mashed together from my little subroutine library but it definitely has big holes in it. I'm far from being VBA proficient. I only know enough to be dangerous and always frustrated.

    Thanks in advance to anyone willing to help.




    Edited once, last by Carim: Added Code Tags ().

  • Hello again,


    In order to deal with your objective of SBLO = Skipped Between the Last Occurrence ...


    ... and not reinvent the wheel ...


    Why not attaching a sample file ... to illustrate your point ... and to show your expected result ... ;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Thanks Carim! I'll attach a .xlsm that gives a sample of what I'm seeking Monday. Everything is on my work PC. Also, I have horrendous rural internet service (500kbps) at home.

  • No problem ... waiting for your sample file ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hello,


    Thanks a lot for your file.


    You are obviously very familiar with this issue ... but I am not :(


    Could you ... even manually ... show what should your expected result look like ... ;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • If you look on Sheet2, Column B, it shows the results I'm looking for. These were manually counted and entered.


    Moving forward, as I enter the latest drawing on Sheet1, the results will change on Sheet2.

  • Let me add this.


    Imagine on the next drawing, instead of selecting 5 numbers out of 43 numbers, they drew all 43 numbers. Just for giggles.


    I want to know how many skips between the last occurrence for each number (all 43 numbers).


    I'd like to see that result in Column B on Sheet2.


    Hope that helps make it more clear.

  • Well ... Column B is the final expected result which you do need ...


    Now ... with 3 concrete examples:


    1. Cell B7 : How do you calculate 5 ?

    2. Cell B8 : How do you calculate 0 ?

    3. Cell B9 : How do you calculate 12 ?

    :/

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Attached is a formula as a first attempt ...


    But my numbers do not match yours ...


    Thanks for your feedback :)

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • So ... if my understanding is correct ...


    You do need the Number of Skips between the last occurrence for each number (all 43 numbers) ... by Category N1 - N5 ...


    Attached is your Version 2 file


    Hope this is in line with your expectations

    :)

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • 1. B7 (5), 5 drawings since the #1 (A7) was hit/picked/selected. See Sheet1 C872.


    If you entered 1, in C878 of Sheet1 and counted the number of drawings between the last time 1 hit (Sheet1 C872), it would be 5 drawings.


    2. B8 (0), the number 2 (A8) was picked/hit/selected in the previous drawing on (Sheet1 C877), so, there would be no skips/drawings between it IF #2 was picked/hit/selected for the next drawing.


    3. B9 (12), 12 is the number of drawings between the next drawing that would be entered in one of the cells (C878:G878) IF 3 were picked/hit/selected in the next drawing.


    Five numbers out of 43 will be entered as the next drawing in Sheet1 C878:G878. I don't know which five out of the 43 it will be but I'd like to know how many skips it would be between drawings for each of the 43 numbers that could be drawn with the count placed in Column B.


    If I were to enter 1, 2, 3, 4, 5 in Sheet1(2) C878:G878 and click on the "Test SBLO" command button it would give me the result of 5, 0 , 12, 47, 9 in H878:L878.


    The result of 5 in H878 is number of drawings since the #1 occurred in a previous drawing. The result of 0 would be the number of skips for the #2 in the previous drawing. The #3 had 12 skips since it last occurred in a previous drawing. Etc. for the #4, 47 skips since it last occurred in a previous drawing. Nine skips for the #5 since it last occurred in a previous drawing.


    If I entered 6, 7, 8, 9, 10 in Sheet1(2) C878:G878 and click on the "Test SBLO" command button it would give me the result of 0, 1, 4, 3, 12 in H878:L878.


    Instead of entering 1, 2, 3, 4, 5 and clicking the command button, get the results, then entering 6, 7, 8, 9, 10, get the results, etc. etc. through 39, 40, 41, 42, 43, I'd like to modify the Sub attached to the "Test SBLO" command button to count the number of skips between the last occurrence for all 43 numbers of which 5 will be picked/hit/selected. Place the count of skips in Column B of Sheet2 for each number in Column A.

  • Re,


    Do you really need a macro ...???


    Have you tried the Test SBLO V2 file ...???


    Formulas have the advantage to update the results in Sheet 2 automatically ...


    The question which remains : why are numbers in Column B and Column C not totally identical ...???

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Sheet1(2) is just an example of code that counts the number of skips between hits for winning numbers. I only included it to show the subrountine used to find that.


    What I was hoping to find was a modification of that subroutine that counted the number of skips for ALL 43 numbers with the results displayed in Sheet2 Column B.

  • It is always possible to build a macro to generate your results ...


    BUT ...


    Could you answer the questions listed in message # 14 ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Let me review Test SBLO V2.

    Just looking at it, I don't need Columns C-G.


    I think Column A may be correct and what I need.


    The need for a subroutine was to get results without filling a sheet with formulas in each cell but I can always Copy/Past Value. This is just one part of many different calculations I'll be doing.


    I appreciate your efforts. Let me review.

  • 1. You can delete Columns D,E,F, G in Sheet 2 SBLO ...


    2. Question is : why your Manual Counts in Column C do not match exactly the Numbers in Column D resulting from the Formula ...???


    3. There is no need to copy anything anywhere ... since you are using 1 - 43 numbers ... everything is dynamic ...

    Just input more drawings in Sheet 1 Data --- and see updated results in Sheet 2 SBLO ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Sorry, I meant to say, Column B may be correct and what I need.


    After spot reviewing it. It is correct, although there are no formulas in any of the cells in Column B.

  • Let me clarify things ...


    Attached is a clearer set-up ...


    Column B is YOUR MANUAL INPUT


    Column C is THE FORMULA ...


    Can you explain the differences shown in RED ...???

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)