Welcome Guest
Log In | Register )
You last visited December 5, 2016, 7:43 pm
All times shown are
Eastern Time (GMT-5:00)

Hits against combo in Excel

Topic closed. 16 replies. Last post 9 years ago by budward.

Page 1 of 2
PrintE-mailLink
Avatar
New Member

Sweden
Member #53532
July 9, 2007
5 Posts
Offline
Posted: July 9, 2007, 10:48 pm - IP Logged

Hello everyone...

  I am playing a lotto 7/35 type of game and here is what I'm trying to do:

I have about 2000 of the previous draws on one sheet and about 400 of my own combinations on another sheet and I want to know how many hits every comb. have in the previous draws?

like:

                                                    4hits    5hits     6hits     7hits

Comb 1: 01 05 08 12 17 33 34     44         5           1          0

Comb 2: 02 05 11 14 22 34 35      22         0           1          1

 and so on...

 

Are there any way to do that?

 Thanks for your help

    RJOh's avatar - chipmunk
    mid-Ohio
    United States
    Member #9
    March 24, 2001
    19825 Posts
    Offline
    Posted: July 9, 2007, 11:20 pm - IP Logged

    Most good lottery software can compare two different files one number at time.  Even my home made DOS software can do that.

    Example:

                    SEARCH/COMPARE FILES
      SEARCHING FILE :CLOTO649 [73/73] WITH FILE :WVA625  [1569]
      FILE NAME  REC.NO.    DATE        NUMBERS          MATCH
      WVA625    11569    07/09/07  01 06 18 19 20 24    MIN. 2
      ______________________________________________________________
      CLOTO649  12  62  10 06/13/07  01 19 25 29 33 46      2
      REVERSE  14  60  13 06/09/07  01 14 19 31 33 44      2
              18  56  15 05/30/07  03 06 10 22 24 33      2
              25  49  18 05/14/07  01 17 20 21 31 46      2
              27  47  20 05/09/07  17 19 24 35 42 49      2
              30  44  23 05/02/07  01 03 10 13 18 46      2
              47  27  28 03/24/07  01 18 22 27 34 39      2
              54  20  32 03/07/07  16 19 20 28 30 48      2
              55  19  34 03/05/07  05 17 19 24 34 47      2
              60  14  39 02/21/07  05 06 08 24 27 33      2
      PAY RATE  62  12  42 02/17/07  03 06 12 24 26 40      2
          6      73  1

    ==================================================================

                    SEARCH/COMPARE FILES
      SEARCHING FILE :CLOTO649 [73/73] WITH FILE :WVA625  [1569]
      FILE NAME  REC.NO.    DATE        NUMBERS          MATCH
      WVA625    11569    07/09/07  01 06 18 19 20 24    MIN. 2
      ______________________________________________________________
      CLOTO649
      REVERSE              TOTAL HITS

                      MATCH  0 = 34    0
                      MATCH  1 = 28    0
                      MATCH  2 = 11    0
                      MATCH  3 = 0      0
                      MATCH  4 = 0      0
                      MATCH  5 = 0      0
                      MATCH  6 = 0      0
      PAY RATE
          6                PRIZE = $ 0

     * you don't need to buy more tickets, just buy a winning ticket * 
       
                 Evil Looking       

      RJOh's avatar - chipmunk
      mid-Ohio
      United States
      Member #9
      March 24, 2001
      19825 Posts
      Offline
      Posted: July 9, 2007, 11:25 pm - IP Logged

      I was comparing West Virgina Cash25 (6/25) with Ohio Classic Lotto (6/49) just for an example.  I usually compare my playing file with the file of the game I'm playing.

       * you don't need to buy more tickets, just buy a winning ticket * 
         
                   Evil Looking       

        time*treat's avatar - radar

        United States
        Member #13130
        March 30, 2005
        2171 Posts
        Offline
        Posted: July 9, 2007, 11:28 pm - IP Logged

        Doing this with a macro would be possible. You will need a couple of arrays (one for the current line, and one to hold your tallies) and a couple of loops (one for the drawn set, and one for the user set). You will also need to be a bit familiar with VBA, because any code you get here would need to be adjusted for your exact layout. 

        In neo-conned Amerika, bank robs you.
        Alcohol, Tobacco, and Firearms should be the name of a convenience store, not a govnoment agency.

          Avatar
          New Member

          Sweden
          Member #53532
          July 9, 2007
          5 Posts
          Offline
          Posted: July 10, 2007, 2:23 pm - IP Logged

          Aha.. I see what you mean, very good idea, thank you.

          Gotta start reading VBA for dummies then :)

           

          If any one else have an idea  please do share it, thank you 

            Avatar
            NASHVILLE, TENN
            United States
            Member #33372
            February 20, 2006
            1044 Posts
            Offline
            Posted: July 12, 2007, 6:33 pm - IP Logged

            Aha.. I see what you mean, very good idea, thank you.

            Gotta start reading VBA for dummies then :)

             

            If any one else have an idea  please do share it, thank you 

                We need a computer programming forum. 

              Avatar
              NASHVILLE, TENN
              United States
              Member #33372
              February 20, 2006
              1044 Posts
              Offline
              Posted: July 12, 2007, 6:33 pm - IP Logged

              Aha.. I see what you mean, very good idea, thank you.

              Gotta start reading VBA for dummies then :)

               

              If any one else have an idea  please do share it, thank you 

                  We need a computer programming forum. 

                Avatar

                United States
                Member #33295
                February 19, 2006
                699 Posts
                Offline
                Posted: July 12, 2007, 7:04 pm - IP Logged

                I Agree!

                There are lots of good programmers at the Lottery Post and there are lots of lottery related questions about programming and Excel. I know that I for one would be checking it constantly trying to learn new things.

                JMHO...Bud

                Isabel, you are going to feel very silly when this turns out to be make-believe.

                  Avatar
                  New Member

                  Sweden
                  Member #53532
                  July 9, 2007
                  5 Posts
                  Offline
                  Posted: July 12, 2007, 8:30 pm - IP Logged

                  Yes... I'm halfway through, It is not an easy task though :Puhh:

                   I agree with Gasmeterguy

                    time*treat's avatar - radar

                    United States
                    Member #13130
                    March 30, 2005
                    2171 Posts
                    Offline
                    Posted: July 12, 2007, 9:07 pm - IP Logged

                    Yes... I'm halfway through, It is not an easy task though :Puhh:

                     I agree with Gasmeterguy

                    Post 10 or so lines of the draw set and the test set.

                    Maybe we can give you a push in the right direction. Cool

                    In neo-conned Amerika, bank robs you.
                    Alcohol, Tobacco, and Firearms should be the name of a convenience store, not a govnoment agency.

                      Avatar
                      New Member

                      Sweden
                      Member #53532
                      July 9, 2007
                      5 Posts
                      Offline
                      Posted: July 12, 2007, 9:42 pm - IP Logged

                      Post 10 or so lines of the draw set and the test set.

                      Maybe we can give you a push in the right direction. Cool

                      Thanks m8... :-DHurray!

                      Draw set: 

                      05 12 15 16 22 23 32

                      01 04 13 14 17 27 29

                      01 14 18 19 20 21 30

                      02 08 13 17 21 27 29

                      04 05 13 16 17 19 34

                      03 03 04 11 24 27 35

                      09 11 17 27 28 30 32

                       

                      Test set

                      03 04 11 14 17 20 22

                      01 16 22 23 27 30 32

                      07 10 11 15 25 27 34

                      02 03 15 17 21 31 33

                      11 15 18 23 28 30 34

                      01 04 07 16 28 32 35

                      06 08 10 11 19 22 33

                       

                      All numbers in Draw set are in there own cells B through H, as column A contains the draw date.

                      All numbers in Test set are A through G.

                       

                      Thanks again 

                        time*treat's avatar - radar

                        United States
                        Member #13130
                        March 30, 2005
                        2171 Posts
                        Offline
                        Posted: July 13, 2007, 4:28 am - IP Logged

                        with 1 worksheet called drawset and one called testset (in the same workbook), draw data starting at row 3 col 2 (leaving space for a date), and test data starting at row 3 col 1, to row 9 for each...the screen may "wrap" some of these longer lines, I like to indentCool. Make sure they are 1 line before you run it.

                         

                        Sub Swed35c7()
                          'by time*treat'
                         
                          'compares test set of 35C7 numbers against'
                          'known 35C7 drawing and tallies subset counts'
                          'for the test__set matching the draw__set'
                          'no blanks between data, R1C1 cell format'
                         
                          Dim draw__row As Integer, test__row As Integer
                          Dim pool() As Integer, t As Integer
                          Dim match As Integer, totals() As Integer
                          Dim draw__col As Integer, test__col As Integer
                         
                          Const pool__size As Integer = 35
                          Const draw__size As Integer = 7
                          Const min__group As Integer = 4
                         
                          ReDim pool(pool__size)
                          ReDim totals(min__group To draw__size)
                             
                          For t = min__group To draw__size
                            Sheets("testset").Cells(2, 9 + t - min__group).Value = t & "hits"
                          Next t
                         
                          For test__row = 3 To 9
                            For t = 1 To pool__size: pool(t) = 0: Next t
                            For t = min__group To draw__size: totals(t) = 0: Next t

                            For test__col = 1 To draw__size
                              pool(Sheets("testset").Cells(test__row, test__col).Value) = 1
                            Next test__col

                            For draw__row = 3 To 9
                              match = 0
                              For draw__col = 2 To 8
                              If pool(Sheets("drawset").Cells(draw__row, draw__col).Value) = 1 Then match = match + 1
                              Next draw__col
                              If match >= min__group Then totals(match) = totals(match) + 1
                            Next draw__row
                           
                            For t = min__group To draw__size
                              Sheets("testset").Cells(test__row, 9 + t - min__group).Value = "*" & totals(t)
                            Next t
                          Next test__row
                        End Sub

                        In neo-conned Amerika, bank robs you.
                        Alcohol, Tobacco, and Firearms should be the name of a convenience store, not a govnoment agency.

                          Avatar

                          United States
                          Member #33295
                          February 19, 2006
                          699 Posts
                          Offline
                          Posted: July 14, 2007, 8:29 am - IP Logged

                          THANKS time*treat.

                          I took your code and adapted it to my 5/39 game. It's great!!

                          now for my question..

                          where it has these two lines

                          For test__row = 3 To 9

                          For draw__row = 3 To 9

                          Is it possible to make this a non static number so I don't have to change the code every time my number of test draws & draw numbers change 

                          Sometimes I might have 5 test rows & draw rows and sometime I might have 75.

                          Bud

                          Isabel, you are going to feel very silly when this turns out to be make-believe.

                            time*treat's avatar - radar

                            United States
                            Member #13130
                            March 30, 2005
                            2171 Posts
                            Offline
                            Posted: July 14, 2007, 10:07 am - IP Logged

                            @Budward

                            Glad you like the code Smile

                            To answer your question, yes.

                            Way #1: If you create new variables called (for example) last__test__row, or last__draw__row, your new code might say For test__row = 3 to last__test__row. Then what you would do is to set the value of the last__test__row (as a constant) like you did with the pool__size  

                            This is best, if you are just playing around with the concept. 

                             

                            Way #2: This is a little bit more complex to code, and is best for if you have 100's of lines, and are mostly done with this part. Instead of for--next loops, you change to while--wend. Make test__row = 3, then...

                             

                            While Sheets("testset").Cells(test__row, 3).Value <> ""

                                (everything else)

                                (then somewhere further along...) 

                                test__row = test__row + 1

                            Wend 

                            ~~~

                            You can add all sorts of things like range checking or checking for non-numbers, and so on. The original code is just for showing what you can get done for under 50 lines.Cool

                            In neo-conned Amerika, bank robs you.
                            Alcohol, Tobacco, and Firearms should be the name of a convenience store, not a govnoment agency.

                              Avatar
                              New Member

                              Sweden
                              Member #53532
                              July 9, 2007
                              5 Posts
                              Offline
                              Posted: July 14, 2007, 3:58 pm - IP Logged

                              Thanks alllot TimeTrea... you gave me a really good start :-)

                              I did the same above by adding a CountA-function on the sheet and then reffering the end value to the value of this cell.

                              Thanks again TimeTreat, you did a wonderfull job, you will get your share of the Jackpot if there will be any :-)