'combine' question

  • Thread starter Thread starter Christopher
  • Start date Start date
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
 
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
 
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
 
Back
Top