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 SubExample 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:
