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.
Do you have a specific number that you would be looking for in this scenario?
If you have a specific number you are searching each time, then this VBA solution should work for you
Option Explicit
Sub CountDupe()
Dim c As Range
Dim rng As Range
Dim crit As Long
crit = InputBox("What number are you searching")
Set rng = Range("A1").CurrentRegion
Dim x As Long
x = 0
For Each c In rng
If c = crit Then
x = x + 1
End If
Next c
MsgBox "Your number appears " & x & "times"
End SubAlan,
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"]
[TD="width: 21"]9[/TD]
[TD="width: 83"]# of Consecutive Hits[/TD]
[TD="width: 89"]# of Consecutive Misses[/TD]
1
[/td]1
[/td]
[TD="align: right"]2[/TD]
0
[/td]1
[/td]1
[/td]
[TD="align: right"]2[/TD]
0
[/td]1
[/td]1
[/td]
[TD="align: right"]2[/TD]
1
[/td]
[TD="align: right"]3[/TD]
0
[/td]0
[/td]
[TD="align: right"]2[/TD]
1
[/td]0
[/td]0
[/td]0
[/td]0
[/td]0
[/td]
[TD="align: right"]5[/TD]
1
[/td]1
[/td]1
[/td]
[TD="align: right"]3[/TD]
[/TABLE]
Thanks for your help earlier, Alan.
Don’t have an account yet? Register yourself now and be a part of our community!