# Excel formula needed.

#### Frank

##### Member
Hi C Drake,

Ok I've got it now, so you do need to reset the counter for every new batch of repeats of a group. Using formulas I need 2 columns to do this, and the output would not be a contiguous list, but each total would align with the last group number in a set, see screenshot 1. Your data is highlighted red, I added more for test purposes.

That output is produced by putting a 1 in cell B1, then putting formula =IF(A2=A1,B1+1,1) in cell B2, and formula =IF(B3<B2,A2&B2,"") in cell C2. copy the formulas down cols B and C to get the results shown. Column B can be hidden, so the counts appear in the next column to A.

To give the results way you describe them (screenshot 2) requires a macro. In this layout the results bear no aligned relationship to the numbers on the left, they are a stand alone summary of the column on the left.

This macro only works for data in column A, starting in cell A1 and outputs to column B starting in cell B1.
It assumes that nothing else is in column A except that list. It would need to be modified if the columns were located elsewhere.

Sub GroupRepeatCounter()
Dim i, count, c As Integer
Dim lRow As Long

lRow = Cells(Rows.count, 1).End(xlUp).Row
'get length of column A data
ReDim List(lRow) As String 'store list of repeat counts
c = 0 'array counter

For i = 0 To lRow
'initialise as empty.
List(i) = ""
Next

Columns("B:B").Select
'clear old count data
Selection.ClearContents
Range("B1").Select

count = 1
'repeat counter
Range("A1").Select
For i = 0 To lRow

If Selection.Value = Selection.Offset(1, 0).Value Then
count = count + 1
If i = 1 Then
List(c) = ActiveCell.Value & count
c = c + 1
End If
Else
List(c) = ActiveCell.Value & count
c = c + 1
count = 1
End If

ActiveCell.Offset(1, 0).Select
Next

'output array to column B
Range("B1").Select
For i = 0 To c
ActiveCell.Offset(i, 0).Value = List(i)
Next
End Sub

#### Frank

##### Member
I hope this meets your requirements.
Frank

#### cdrake

##### Member
Let me know how to go about doing that bloubul. I recall somewhere that there was a v117 out there. Don't remember where and it was years ago.

#### cdrake

##### Member
Thanks Frank, just updating all my files for tomorrows draws. Once that is done I'll work on that program you posted and let you know how it works. Happy Lotto and thanks for taking the trouble in helping me out.

#### Frank

##### Member
Thanks Frank, just updating all my files for tomorrows draws. Once that is done I'll work on that program you posted and let you know how it works. Happy Lotto and thanks for taking the trouble in helping me out.
Well its not fully debugged and I suspect certain repeated sequences of 3 or more starting in cell A1 might throw a spanner in the works.

#### Frank

##### Member
Well its not fully debugged and I suspect certain repeated sequences of 3 or more starting in cell A1 might throw a spanner in the works.
Yes as I suspected. For the formula version you need =IF(B2<B1,A1&B1,IF(B2=1,A1&1,"")) in cell C1, and copied down the page. I think that cures that bug. The macro is a bit buggy for its first entry for repeated data more than 3 times.at the top

#### Frank

##### Member
to correct the macro, should you use it,

find this code :- ( just after count=count+1) and delete it.
If i = 1 Then
List(c) = ActiveCell.Value & count
c = c + 1

End If

then replace it with :-
If i = 1 And Selection.Offset(1, 0).Value = Selection.Offset(2, 0).Value Then
End If

fingers crossed, it seems to work Ok now.

#### cdrake

##### Member
I inputted that formula Frank and everything works as you described in excel. The blanked cells still contain the formula so if I want to collapse the column I can delete the contents of that cell, go to find, paste special, and blanks. Thanks for all of your hard work. Happy Lotto.

#### cdrake

##### Member
A question for both Frank and bloubul. Do both of you play the lotto 649 or any of the regional Canadian Lotteries. I've been playing the 649 seriously for approx 25 years along with the western 649 and lotto max. I've been thinking of dropping the lotto max and trying one of the pick 3s instead but haven't quite gotten the nerve to do that yet. Maybe an exchange of ideas and info on what works and doesn't seem to work could benefit all if we all collaborated as such. I'm somewhat OCD when it comes to numbers and would like to figure out this random number puzzle before I retire in the next year or so. For todays 649 draw I'm looking at the # 1,18,34,32,46,23,13,27,38,20,30. Could have 2 to 4 coming from that group. The 17,29,&39 could also produce 1 winning number. This group of #s should only produce 0-1 winning #(2459/101316/212427/303335/40. I won't filter them all out but look at the numbers that are close by to them on the charts that I draw up to make my combo selections. Happy lotto.

#### Frank

##### Member
Well I’m in the UK, and our 649 became a 659 about 4 years ago, so no I don’t play any version of 649 any more. I’ve already retired and given up using any of my past systems to win anything on 659, it’s time consuming and the odds are just too ridiculous. I wish we still had 649 though, maybe I’d still be plugging away with a system. I wish you lots of luck and success in your quest.

#### cdrake

##### Member
Well I’m in the UK, and our 649 became a 659 about 4 years ago, so no I don’t play any version of 649 any more. I’ve already retired and given up using any of my past systems to win anything on 659, it’s time consuming and the odds are just too ridiculous. I wish we still had 649 though, maybe I’d still be plugging away with a system. I wish you lots of luck and success in your quest.
I had 4 of the winning numbers in my group that I posted but still only managed a free play win. Are you playing some of the smaller pick lotteries though?

#### bloubul

##### Member
I'm in South Africa and we have 6 lotto's per week. The Daily from Monday to Monday 5/36, 3 Lotto's Wednesday & Saturday 6/52 ( it was 6/49), and 2 PowerBalls Tuesdays & Fridays 5/50 (it was 5/45), currently I'm concentrating on the Daily which I'm going to start playing seriously, I believe that which ever lotto you play the principals and mythologies are the same.

#### cdrake

##### Member
I agree, I'd like to link up with some other Can649 users that are committed to winning and are putting the needed time into making that happen. Thanks for your input bloubul.

#### Frank

##### Member
I had 4 of the winning numbers in my group that I posted but still only managed a free play win. Are you playing some of the smaller pick lotteries though?

In the U.K. we have hotpicks 1 to hotpicks 5 available, but it’s not one of the lotteries I ever got involved with, so I can’t really help with that.

#### cdrake

##### Member
Thanks bloubul. On my next set of days off I'll contact you on that link. Just updating my files for tomorrows draws as I start my night shifts tonight.

#### cdrake

##### Member
I have a Russian friend who plays Canadian Lotto's, contact me here and I'll give you his email address. you find me here: http://crazynuts.hollosite.com/lottery_spreadsheets/, at the bottom of the page under Lottery Draw History - Past Results
Hi boubul, I checked out that link you posted and scrolled to the bottom of the page and saw the contact field. I used that email and sent a note for a request a few days ago but haven't heard back from anyone yet.

#### cdrake

##### Member
I realized now bloubul that I used the wrong address. I've sent another one and hope to hear back from you soon. Happy lotto.

#### bloubul

##### Member
Got it. Mail to you