'combine' question

C

Christopher

Hello all,

Perhaps someone can help me with the following question.

The function 'combine' tells me that if 12 objects are divided in to groups
of 4 items there are a possible 495 combinations.
I want to visualize those 495 combinations.
I have not been able to do so after several atempts to write some code in
VBA. I'm always missing some.

Any help?

Thanks,
Christopher
 
J

Jim Cone

Enter 12 in the first Input box, enter 4 in the second Input box.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)

'--
'Code by John Warren March 21, 2001
'Modified by Jim Cone April 06, 2006
'Calls Comb2 sub.
'Creates the list in a single column.
'Select the top cell of the column then run code.
Sub Combinations()
Dim n As Variant
Dim m As Variant
ReStart:
n = InputBox("Number of items?", "Combinations")
If Len(n) = 0 Then Exit Sub
m = InputBox("Taken how many at a time?", "Combinations")
If Len(m) = 0 Then GoTo ReStart

Application.ScreenUpdating = False
Comb2 n, m, 1, vbNullString, ActiveCell
Application.ScreenUpdating = True
End Sub

'Generate combinations of integers k..n taken m at a time, recursively
'John Warren March 21, 2001
'Modified by Jim Cone April 06, 2006
Sub Comb2(ByVal n As Integer, ByVal m As Integer, ByVal k As Integer, _
ByVal s As String, ByRef rng As Excel.Range)
If m > n - k + 1 Then Exit Sub
If m = 0 Then
rng.Value = RTrim$(s)
Set rng = rng(2, 1)
Exit Sub
End If
Comb2 n, m - 1, k + 1, s & k & " ", rng
Comb2 n, m, k + 1, s, rng
End Sub
'----------



"Christopher"
wrote in message
Hello all,
Perhaps someone can help me with the following question.
The function 'combine' tells me that if 12 objects are divided in to groups
of 4 items there are a possible 495 combinations.
I want to visualize those 495 combinations.
I have not been able to do so after several atempts to write some code in
VBA. I'm always missing some.
Any help?
Thanks,
Christopher
 
M

Max

One play is to use Myrna Larson's power subroutine
to generate the combinations ..

Take away this implemented sample from my archives:
http://www.savefile.com/files/518493
MyrnaLarson_Combination_Permutation.xls
(full details inside, ready to run)

In the sample file,
In Sheet1,
1. Enter the letter C or P in A1
(C = combinations, P = permutations), ie enter: C
2. Enter the number of items involved per combo in A2, ie enter: 3
3. Enter/List the 6 items in A3 down, ie list in A3:A8 :1, 2, ... 6
4. Select A1 (this cell selection is required),
then click the button ListPermutations to run the sub ListPermutations
5. The results will be written to a new sheet (just to the left),
and appear like below, in a zig-zag manner* until all combos are exhausted:
*if it exceeds the rows limit of 65536 in xl97 to xl2003

1, 2, 3
1, 2, 4
1, 2, 5
1, 2, 6
....
4, 5, 6

Go easy when you *ramp* up the generation ...
eg a "Pick 6 out of 45" run works out to a staggering:
=COMBIN(45,6) = 8,145,060 combinations
so almost half** an entire sheet would be populated

**A single sheet in xl97 to xl2003 houses:
=65536 rows x 256 cols = 16,777,216 cells

The sub would certainly need time to complete generation
 
Top