Consecutive Values in a Column

  • I have a a 2016 spreadsheet that has seven columns of lottery numbers with 100 rows. I can't figure out how to generate a formula that will tell me the number of times a value repeats itself (or doesn't repeat itself) moving down from row to row, or drawing to drawing. As an example, suppose a number repeats itself in several consecutive games, or hasn't repeated itself for a certain number of games. How do I generate a formula that will tell me this?


    Thanks very much for your help in advance.

  • If you have a specific number you are searching each time, then this VBA solution should work for you

  • Alan,


    No, I'm not looking for a specific number. I am attempting to create an additional column that displays the number of games in which a number has repeated itself and an additional column that displays the number of games in which a specific number has been missing in action. An example of what I'm trying to accomplish appears below: (NOTE: The right most columns were manually populated for the number "9".)
    [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

    [tr]


    [TD="width: 21"]9[/TD]
    [TD="width: 83"]# of Consecutive Hits[/TD]
    [TD="width: 89"]# of Consecutive Misses[/TD]

    [/tr]


    [tr]


    [td]

    1

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    1

    [/td]


    [TD="align: right"]2[/TD]

    [td][/td]


    [/tr]


    [tr]


    [td]

    0

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    1

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    1

    [/td]


    [TD="align: right"]2[/TD]

    [td][/td]


    [/tr]


    [tr]


    [td]

    0

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    1

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    1

    [/td]


    [TD="align: right"]2[/TD]

    [td][/td]


    [/tr]


    [tr]


    [td]

    1

    [/td]


    [TD="align: right"]3[/TD]

    [td][/td]


    [/tr]


    [tr]


    [td]

    0

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    0

    [/td]


    [td][/td]


    [TD="align: right"]2[/TD]

    [/tr]


    [tr]


    [td]

    1

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    0

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    0

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    0

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    0

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    0

    [/td]


    [td][/td]


    [TD="align: right"]5[/TD]

    [/tr]


    [tr]


    [td]

    1

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    1

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    1

    [/td]


    [TD="align: right"]3[/TD]

    [td][/td]


    [/tr]


    [/TABLE]

    Thanks for your help earlier, Alan.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!