Question For GillesD Re: Formulas

Sandman

Member
Thanx GillesD for the quick learn how on Excell with regards to combinations. I would also like to know if there are any possible formulas that I could use to eliminate runs of 3 or more in all sum range combinations. Example all 165,772 combo's within the sum range of 150 less the combinations with runs of 3 consecutive numbers or more. I would like to eliminate combo's of 6 with numbers in them like 23,24,25 ect....... or combo's with all even or odd numbers. Could Excell create such beauty????? This would reduce the total of combo's considerably in each sum range I'm sure. I do not want to modify the formulas you have already posted, I will ruin them for sure. I hope my questions are clear enough. You Probably undersatnd my questions better then I do!!! I'm so AMAZED with what you've already opened my eyes to with Excell that I'm completely hooked. I think I've already asked enough that you should be charging me a fee already!!!!
 

GillesD

Member
Eliminating some combinations

Eliminating some combinations can be done but the VBA program given may not be the best choice since combinations are listed one combination per cell.

The VBA program listed at the end of this post (List_CombA) might be more appropriate. It does the following:
1 – it provides some headings for columns (columns named N1 to N6, then a column named Test, then a blank column, before repeating)
2 – the combinations are listed one per row but each number of a combination (N1 to N6) is in a different cell over 6 columns

You can then use the column Test to enter a specific condition to be used for eliminating some combinations. To identify combinations with 3 consecutive numbers (whether 1-2-3, 23-24-25 or 47-48-49), in cell G2, you could enter the formula: =OR(A2=C2-2,B2=D2-2,C2=D2-2,D2=F2-2) and then copy it down and also in columns O and W. Cells will take the value TRUE if there are three consecutive numbers in the combination and FALSE otherwise. The filtering options of Excel can then be used to eliminate those you do not want to keep.

The VBA program is:

Option Explicit

Dim A As Integer, B As Integer, C As Integer
Dim D As Integer, E As Integer, F As Integer
Dim N As Long

Sub List_CombA()
Range("A1").Select
Application.ScreenUpdating = False
N = 0
ActiveCell.Offset(0, 0).Value = "N1"
ActiveCell.Offset(0, 1).Value = "N2"
ActiveCell.Offset(0, 2).Value = "N3"
ActiveCell.Offset(0, 3).Value = "N4"
ActiveCell.Offset(0, 4).Value = "N5"
ActiveCell.Offset(0, 5).Value = "N6"
ActiveCell.Offset(0, 6).Value = "Test"
ActiveCell.Offset(1, 0).Select
For A = 1 To 44
For B = A + 1 To 45
For C = B + 1 To 46
For D = C + 1 To 47
For E = D + 1 To 48
For F = E + 1 To 49
If A + B + C + D + E + F = 150 Then
If N = 65001 Then
N = 0
ActiveCell.Offset(-65002, 8).Select
Application.ScreenUpdating = True
Application.ScreenUpdating = False
ActiveCell.Offset(0, 0).Value = "N1"
ActiveCell.Offset(0, 1).Value = "N2"
ActiveCell.Offset(0, 2).Value = "N3"
ActiveCell.Offset(0, 3).Value = "N4"
ActiveCell.Offset(0, 4).Value = "N5"
ActiveCell.Offset(0, 5).Value = "N6"
ActiveCell.Offset(0, 6).Value = "Test"
ActiveCell.Offset(1, 0).Select
End If
ActiveCell.Offset(0, 0).Value = A
ActiveCell.Offset(0, 1).Value = B
ActiveCell.Offset(0, 2).Value = C
ActiveCell.Offset(0, 3).Value = D
ActiveCell.Offset(0, 4).Value = E
ActiveCell.Offset(0, 5).Value = F
ActiveCell.Offset(1, 0).Select
N = N + 1
End If
Next F
Next E
Next D
Next C
Next B
Next A
Application.ScreenUpdating = True
End Sub


Notes:
- this program is not optimized in any way but it is just a quick and dirty way of obtaining some results
- this program should not be used if more than 2,000,000 combinations are expected to be listed; you will run out of columns in the worksheet
- it might be better if you set column width to 5 for columns A to Z
 

Sandman

Member
Gilles Is Amazing!!!

Once again Gilles you amaze me with your absolute knowledge of the Excell Program, WOW!!! I truly appreciate the time taken to write out the information you have in the last few postings. It may not seem like a lot to you or hard for you to write out, but you get a BIG definate 2 thumbs up in my books.
Has this Excell program at all helped you with narrowing down any 4 or 5 number wins? Because you definately deserve the Jackpot if at all possible. Good luck to you & thanx once again.
 

Sidebar

Top