Using Excel 2000</SPAN></SPAN>
Hi,</SPAN></SPAN>
The code below generate any combinations I use for example Const v& = 4 to 14 where Array is ("1", "X", "2") (3^4=81, and 3^14=4.782.969) so in the given example 3^4= 81 combinations are generated in the column A and column C & D there is a resume how many combinations can be found with example (4-1's=1) with (3-1's & 1-x's =4) </SPAN></SPAN>
My query is it possible to limit the numbers of X's & 2's to get only output for those combinations only, like for example 2-1's, 1-X's, 1-2's =12 combinations showed highlighted in blue that can be generated only. So the output only 12 not all the 81 </SPAN></SPAN>
Code....</SPAN></SPAN>
</SPAN></SPAN>
Example data... </SPAN></SPAN>
Thank you in advance</SPAN></SPAN>
Regards,</SPAN></SPAN>
Kishan</SPAN></SPAN>
Hi,</SPAN></SPAN>
The code below generate any combinations I use for example Const v& = 4 to 14 where Array is ("1", "X", "2") (3^4=81, and 3^14=4.782.969) so in the given example 3^4= 81 combinations are generated in the column A and column C & D there is a resume how many combinations can be found with example (4-1's=1) with (3-1's & 1-x's =4) </SPAN></SPAN>
My query is it possible to limit the numbers of X's & 2's to get only output for those combinations only, like for example 2-1's, 1-X's, 1-2's =12 combinations showed highlighted in blue that can be generated only. So the output only 12 not all the 81 </SPAN></SPAN>
Code....</SPAN></SPAN>
Code:
Sub GenerateCombi()
Const v& = 4
Dim z, y() As String, q(), u&, g&
Dim a&, b&, c&, d&, p&, MaxRow&
MaxRow = Rows.Count
z = Array("1", "X", "2")
u = UBound(z) + 1
ReDim y(1 To u ^ v, 1 To 1)
For a = 1 To v
For b = 1 To u ^ v Step u ^ a
For c = b To b + u ^ (a - 1) - 1
For d = 1 To u
y(c + u ^ (a - 1) * (d - 1), 1) = _
z(d - 1) & y(c + u ^ (a - 1) * (d - 1), 1)
Next d
Next c
Next b
Next a
For a = 1 To u ^ v Step MaxRow
ReDim q(1 To MaxRow, 1 To 1)
g = 0: p = p + 1
For b = 1 To MaxRow
If a + b > u ^ v + 1 Then Exit For
q(b, 1) = y(a + b - 1, 1)
g = g + 1
Next b
Cells(p).Resize(g) = q
Next a
End Sub
Example data... </SPAN></SPAN>
Book1 | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
1 | 1111 | ||||||
2 | 111X | ||||||
3 | 1112 | ||||||
4 | 11X1 | 1 | X | 2 | Total | ||||
5 | 11XX | 4 | 0 | 0 | 1 | ||||
6 | 11X2 | 3 | 1 | 0 | 4 | ||||
7 | 1121 | 3 | 0 | 1 | 4 | ||||
8 | 112X | 2 | 2 | 0 | 6 | ||||
9 | 1122 | 2 | 1 | 1 | 12 | ||||
10 | 1X11 | 2 | 0 | 2 | 6 | ||||
11 | 1X1X | 1 | 3 | 0 | 4 | ||||
12 | 1X12 | 1 | 2 | 1 | 12 | ||||
13 | 1XX1 | 1 | 1 | 2 | 12 | ||||
14 | 1XXX | 1 | 0 | 3 | 4 | ||||
15 | 1XX2 | 0 | 4 | 0 | 1 | ||||
16 | 1X21 | 0 | 3 | 1 | 4 | ||||
17 | 1X2X | 0 | 2 | 2 | 6 | ||||
18 | 1X22 | 0 | 1 | 3 | 4 | ||||
19 | 1211 | 0 | 0 | 4 | 1 | ||||
20 | 121X | ||||||
21 | 1212 | ||||||
22 | 12X1 | ||||||
23 | 12XX | ||||||
24 | 12X2 | ||||||
25 | 1221 | ||||||
26 | 122X | ||||||
27 | 1222 | ||||||
28 | X111 | ||||||
29 | X11X | ||||||
30 | X112 | ||||||
31 | X1X1 | ||||||
32 | X1XX | ||||||
33 | X1X2 | ||||||
34 | X121 | ||||||
35 | X12X | ||||||
36 | X122 | ||||||
37 | XX11 | ||||||
38 | XX1X | ||||||
39 | XX12 | ||||||
40 | XXX1 | ||||||
41 | XXXX | ||||||
42 | XXX2 | ||||||
43 | XX21 | ||||||
44 | XX2X | ||||||
45 | XX22 | ||||||
46 | X211 | ||||||
47 | X21X | ||||||
48 | X212 | ||||||
49 | X2X1 | ||||||
50 | X2XX | ||||||
51 | X2X2 | ||||||
52 | X221 | ||||||
53 | X22X | ||||||
54 | X222 | ||||||
55 | 2111 | ||||||
56 | 211X | ||||||
57 | 2112 | ||||||
58 | 21X1 | ||||||
59 | 21XX | ||||||
60 | 21X2 | ||||||
61 | 2121 | ||||||
62 | 212X | ||||||
63 | 2122 | ||||||
64 | 2X11 | ||||||
65 | 2X1X | ||||||
66 | 2X12 | ||||||
67 | 2XX1 | ||||||
68 | 2XXX | ||||||
69 | 2XX2 | ||||||
70 | 2X21 | ||||||
71 | 2X2X | ||||||
72 | 2X22 | ||||||
73 | 2211 | ||||||
74 | 221X | ||||||
75 | 2212 | ||||||
76 | 22X1 | ||||||
77 | 22XX | ||||||
78 | 22X2 | ||||||
79 | 2221 | ||||||
80 | 222X | ||||||
81 | 2222 | ||||||
82 | |||||||
83 | |||||||
84 | |||||||
85 | |||||||
Sheet1 |
Thank you in advance</SPAN></SPAN>
Regards,</SPAN></SPAN>
Kishan</SPAN></SPAN>
Last edited: