# 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?

• 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!