Need some excel expertise

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

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

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

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

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

All the results should be in Column D2-D49

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

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

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 ?

did not get PM. sent you one

p8

Thanks Phileight