6-number combinations in Excel
Not exactly what you wanted, but the following macro will generate all possible 6-number combinations using up to 30 numbers. I will try to work it out for less than 6-number combinations as you indicated.
Starting in cell A1 and going down, place the numbers you want to use to generate 6-number combinations. The macro will work with 6 up to 30 numbers. Preferably these numbers should be in ascending order.
Then run the VBA program listed below. Starting in Column C, you will get the combination number, then in adjacent columns, the values for N1, N2, …, N6 for each combination. After 60,000 combinations, the listing of combinations will continue but they will be shifted by 8 columns to the right. If you use 30 numbers, this will give you 594,775 combinations and obviously with only 6 numbers, you will get one combination.
Option Explicit
Option Base 1
Sub Combin_6N()
Dim A As Integer, B As Integer, C As Integer
Dim D As Integer, E As Integer, F As Integer
Dim I As Long, J As Integer, vN(30) As Integer
Application.ScreenUpdating = False
Range("A1").Select
J = 1
Do While ActiveCell > 0
vN(J) = ActiveCell.Value
J = J + 1
ActiveCell.Offset(1, 0).Select
Loop
J = J - 1
Range("C1").Select
I = 1
For A = 1 To J - 5
For B = A + 1 To J - 4
For C = B + 1 To J - 3
For D = C + 1 To J - 2
For E = D + 1 To J - 1
For F = E + 1 To J
ActiveCell.Offset(0, 0).Value = I
ActiveCell.Offset(0, 1).Value = vN(A)
ActiveCell.Offset(0, 2).Value = vN(B)
ActiveCell.Offset(0, 3).Value = vN(C)
ActiveCell.Offset(0, 4).Value = vN(D)
ActiveCell.Offset(0, 5).Value = vN(E)
ActiveCell.Offset(0, 6).Value = vN(F)
I = I + 1
ActiveCell.Offset(1, 0).Select
Select Case I
Case 60001, 120001, 180001, 240001, 300001, 360001, 420001, 480001, 540001
ActiveCell.Offset(-60000, 8).Select
End Select
Next F
Next E
Next D
Next C
Next B
Next A
Range("A1").Select
End Sub
