I have a set of x amount of numbers.... sometimes 10 some times 8.. I would
like to take those numbers and generate every possible 5 number combination.
ie.
A B C D E F G H
--- --- --- --- --- --- --- ---
1 2 3 4 5 6 7 8
A B C D E
--- --- --- --- ---
1 2 3 4 5
1 2 3 4 6
1 2 3 4 7
1 2 3 4 8
1 2 3 5 6
1 2 3 5 7
1 2 3 5 8
It would be on the same sheet (lets call it Sheet3)
Thank you for helping me.
There are some generalized algorithms available, but for what you describe, a
simple set of loops will do. In the macro, set N = to the number of elements
in your set (e.g. 8 or 10).
You can "fancy this up" according to your own requirements.
If the numbers are not 1 through n, you may use a lookup table on the worksheet
to translate the output; or an array in the macro where each element in the
array is equivalent to an item in the list of possible items.
With this method, the output is limited to one column, which means a maximum of
65536 in the output list. This probably limits your total number to 25 if you
are choosing 5 items.
But you could certainly skip over to the next set of columns if you have more
items.
=================================
Option Explicit
Sub Combin5ofN()
'one counter for each item in the combination
Dim i As Long, j As Long, k As Long, l As Long, m As Long
'set this to the total number of possible items
Const N As Long = 25
Dim Output As Range
Dim LoopCount As Long
'set this to the top row left column of result output on the worksheet
Set Output = [A1]
'Useful if you are outputting large numbers of combinations
'Application.ScreenUpdating = False
For i = 1 To N - 4
For j = i + 1 To N - 3
For k = j + 1 To N - 2
For l = k + 1 To N - 1
For m = l + 1 To N
Output.Offset(LoopCount, 0).Value = i
Output.Offset(LoopCount, 1).Value = j
Output.Offset(LoopCount, 2).Value = k
Output.Offset(LoopCount, 3).Value = l
Output.Offset(LoopCount, 4).Value = m
LoopCount = LoopCount + 1
Next m
Next l
Next k
Next j
Next i
'Application.ScreenUpdating = True
End Sub
=========================================
--ron
