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

Need some excel expertise

Topic closed. 11 replies. Last post 4 years ago by Ricklou.

Page 1 of 1
PrintE-mailLink
Ricklou's avatar - majestic lion.jpg

South Africa
Member #129822
June 28, 2012
361 Posts
Offline
Posted: September 15, 2012, 3:41 pm - IP Logged

Need a macro or a way to callcalate all the possible balls skips for 6 balls etc there is repeat nrs aswell that it will list all the nrs that could add up to the total skips for the draw. Column A (balls)1-49, Column B5 skips for ball1-49 Column C1 =Total that the 6 skips should be equal too. Column D1 =all the numbers that will equal to C1.

Balls           Game Out

Column A    Colum BSkips   Column C      Column D

1                       8                       36             

2                       3

3                      5 

4                     1

5                      5

6                     14

7                      11

8                        5

49                       3

    Ricklou's avatar - majestic lion.jpg

    South Africa
    Member #129822
    June 28, 2012
    361 Posts
    Offline
    Posted: September 15, 2012, 3:43 pm - IP Logged

    Example previous draws skips was 36

     

    So C1=36

    So need 6 balls values -repeats combinations aswell that can add to 36..Just clearing it up.Thanx for any help hope someone can assist

      SergeM's avatar - slow icon.png
      Economy class
      Belgium
      Member #123700
      February 27, 2012
      4035 Posts
      Offline
      Posted: September 15, 2012, 3:51 pm - IP Logged

      Someone posted a link for a free E-book VBA Excel. Good luck with it!

        Avatar

        United States
        Member #41846
        June 23, 2006
        458 Posts
        Offline
        Posted: September 16, 2012, 6:24 am - IP Logged

        Need a macro or a way to callcalate all the possible balls skips for 6 balls etc there is repeat nrs aswell that it will list all the nrs that could add up to the total skips for the draw. Column A (balls)1-49, Column B5 skips for ball1-49 Column C1 =Total that the 6 skips should be equal too. Column D1 =all the numbers that will equal to C1.

        Balls           Game Out

        Column A    Colum BSkips   Column C      Column D

        1                       8                       36             

        2                       3

        3                      5 

        4                     1

        5                      5

        6                     14

        7                      11

        8                        5

        49                       3

        Ricklou

        you mention a macro so you know your way around a spreadsheet.  can I assume you are taking care of columns A,B,C ?  you do realize that you could have many thousands  of matches?  you show col C to be on line 5, but your wording says col 1. I will assume everything starts on line 5.

        i let the code below run for 5 min using random data. 30000 matches later and not even a third of the way done. good luck

         

        Sub Button1_Click()
        Ln = 5 'line for display
        For r = 6 To 48
        For s = r + 1 To 49
        For t = s + 1 To 50
        For u = t + 1 To 51
        For v = u + 1 To 52
        For w = v + 1 To 53
        ts = Cells(5, 3) ' target skip
        st = Cells(r, 2) + Cells(s, 2) + Cells(t, 2) + Cells(u, 2) + Cells(v, 2) + Cells(w, 2) ' skip total
        If st = ts Then GoSub display
        Next w
        Next v
        Next u
        Next t
        Next s
        Next r




        Exit Sub

        display:
        Cells(Ln, 4) = Cells(r, 1)
        Cells(Ln, 5) = Cells(s, 1)
        Cells(Ln, 6) = Cells(t, 1)
        Cells(Ln, 7) = Cells(u, 1)
        Cells(Ln, 8) = Cells(v, 1)
        Cells(Ln, 9) = Cells(w, 1)
        Ln = Ln + 1
        Return



        End Sub

          Ricklou's avatar - majestic lion.jpg

          South Africa
          Member #129822
          June 28, 2012
          361 Posts
          Offline
          Posted: September 16, 2012, 10:53 am - IP Logged

          Phileight

           

          Yes I know and know the basics -I am no programmer thats why I need some expertise from an excel guru like yourself. Correct Column A = Numbers A2-1- 49

          Column B2 Skips for each ball 1-49 (note these skips can have repeat numbers up to 6 repeats) C2=Target. So A1 B1 C1 =Headings so all the data begin on A2 B2 C2. I know there is also solver dont know if that can do it. And yes there will be lots of options to get to the target -whats important is that the formulae need to be able to give the target value for instance C2= target value of 24. So the options from B2-B50 should list all the available option 6 in total to give you the target value of 24. It must be six values to be added to equal the target value very important. Thank you for your macro........Phileight can u confirm for me if this will be able to show all the available options to get to 36 -even repeat values should be listed as the repeat nrs should also be able to make up the target value. Thanks Phileight and rep for your help -much much appreciated and respect for your excel programming skills

           

          Ricklou

            Ricklou's avatar - majestic lion.jpg

            South Africa
            Member #129822
            June 28, 2012
            361 Posts
            Offline
            Posted: September 16, 2012, 12:23 pm - IP Logged

            All the results should be in Column D2-D49

              Avatar

              United States
              Member #41846
              June 23, 2006
              458 Posts
              Offline
              Posted: September 16, 2012, 12:24 pm - IP Logged

              Ricklou

              you mention a macro so you know your way around a spreadsheet.  can I assume you are taking care of columns A,B,C ?  you do realize that you could have many thousands  of matches?  you show col C to be on line 5, but your wording says col 1. I will assume everything starts on line 5.

              i let the code below run for 5 min using random data. 30000 matches later and not even a third of the way done. good luck

               

              Sub Button1_Click()
              Ln = 5 'line for display
              For r = 6 To 48
              For s = r + 1 To 49
              For t = s + 1 To 50
              For u = t + 1 To 51
              For v = u + 1 To 52
              For w = v + 1 To 53
              ts = Cells(5, 3) ' target skip
              st = Cells(r, 2) + Cells(s, 2) + Cells(t, 2) + Cells(u, 2) + Cells(v, 2) + Cells(w, 2) ' skip total
              If st = ts Then GoSub display
              Next w
              Next v
              Next u
              Next t
              Next s
              Next r




              Exit Sub

              display:
              Cells(Ln, 4) = Cells(r, 1)
              Cells(Ln, 5) = Cells(s, 1)
              Cells(Ln, 6) = Cells(t, 1)
              Cells(Ln, 7) = Cells(u, 1)
              Cells(Ln, 8) = Cells(v, 1)
              Cells(Ln, 9) = Cells(w, 1)
              Ln = Ln + 1
              Return



              End Sub

              Ricklou

              Correction to the code above.  for r=5 to 48   change the 6 to a 5

              you need to add data for col A,B, and C. I don't have your database so I can't calculate the skips

              this program simply loops thru all possible combinations of skip.  for each loop it adds the skips indicated in col B associated with the variables R,S,T,U,V,&W.  If the total matches the number in  c5 then it displays the numbers in col A indicated by variables R,S,T,U,V,W

              good luck

                Avatar

                United States
                Member #41846
                June 23, 2006
                458 Posts
                Offline
                Posted: September 16, 2012, 12:29 pm - IP Logged

                All the results should be in Column D2-D49

                the result is 6 numbers. the result is in col D,E,F,G,H,&I. starts on line 5 and moves down 1 line for each match

                  Ricklou's avatar - majestic lion.jpg

                  South Africa
                  Member #129822
                  June 28, 2012
                  361 Posts
                  Offline
                  Posted: September 16, 2012, 12:44 pm - IP Logged

                  Macro is still running.......Just pmed you Phileight...Thank you for your expert programming skills. Can see you are a FUNDI!!!!!

                   

                  regards

                   

                  Ricklou

                    Ricklou's avatar - majestic lion.jpg

                    South Africa
                    Member #129822
                    June 28, 2012
                    361 Posts
                    Offline
                    Posted: September 16, 2012, 12:49 pm - IP Logged

                    Macro is still running.......Just pmed you Phileight...Thank you for your expert programming skills. Can see you are a FUNDI!!!!!

                     

                    regards

                     

                    Ricklou

                    Very weird.......Macro run but I dont get the answers.Can I possible mail you the spreadsheet I have Phileight ? Tried to send u a pm but I dont think u got it ?

                      Avatar

                      United States
                      Member #41846
                      June 23, 2006
                      458 Posts
                      Offline
                      Posted: September 16, 2012, 12:55 pm - IP Logged

                      did not get PM. sent you one

                      p8

                        Ricklou's avatar - majestic lion.jpg

                        South Africa
                        Member #129822
                        June 28, 2012
                        361 Posts
                        Offline
                        Posted: September 16, 2012, 1:10 pm - IP Logged

                        Thanks PhileightSee Ya!