Create all possible combinations

[Kishan]

Using Excel 2000

Hi,

I want VBA the could create all possible combinations using 5 groups which Min Sum is = 0 And Max Sum is =14

My 1st group contain 3 numbers in cells B2:B4 0, 1 & 2. And the rest 4 contain 0, 1, 2 & 3 in cells C2:F5

Below is the example sheet I don't how much combination could be all together only I can say in row 8 1st is with sum = 0 like 0, 0, 0, 0, 0 And last could be the sum = 14 like 2, 3, 3, 3, 3

Example sheet...

Book1
	A	B	C	D	E	F	G	H
1		Group1	Group2	Group3	Group4	Group5
2		0	0	0	0	0
3		1	1	1	1	1
4		2	2	2	2	2
5			3	3	3	3
6
7	Generate	Set1	Set2	Set3	Set4	Set5	Sum
8	1	0	0	0	0	0	0
9	2	0	0	0	0	1	1
10	3	0	0	0	1	0	1
11	4	0	0	1	0	0	1
12	5	0	1	0	0	0	1
13	6	1	0	0	0	0	1
14	??						0
15	??						0
16	??						0
17	??						0
18	??						0
19	??						0
20	??						0
21	??						0
22	??	2	3	3	3	3	14
23
24
25
26
27
28
29
30
31
32
33
Create All Combination

Thank you in advance

Regards,
Kishan

 

[Kishan]

Hi, I am not sure may be it is permutation "not the combinations" what a am looking for....

Regards,
Kishan

 

[Kishan]

Hi, I am not sure may be it is permutation "not the combinations" what a am looking for....

Regards,
Kishan

Hi, searched in forum and goggle too. But can't find and VBA solution but find it could be "permutation with repetitions"

Regards,
Kishan

 

[shg]

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][td="bgcolor:#C0C0C0"]

F​

[/td][td="bgcolor:#C0C0C0"]

G​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td]B2: =MOD(INT(4*(ROWS($F$2:Me) - 1) / 4 ^ COLUMNS($F$2:Me)), 4)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

1​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

2​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

3​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

1​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

1​

[/td][td="bgcolor:#E5E5E5"]

1​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

1​

[/td][td="bgcolor:#E5E5E5"]

2​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

1​

[/td][td="bgcolor:#E5E5E5"]

3​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

2​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

2​

[/td][td="bgcolor:#E5E5E5"]

1​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

2​

[/td][td="bgcolor:#E5E5E5"]

2​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

13​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

2​

[/td][td="bgcolor:#E5E5E5"]

3​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

14​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

3​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

15​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

3​

[/td][td="bgcolor:#E5E5E5"]

1​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

16​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

3​

[/td][td="bgcolor:#E5E5E5"]

2​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

17​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

3​

[/td][td="bgcolor:#E5E5E5"]

3​

[/td][td][/td][/tr]
[/table]

Copy down through row 1024.

 

[Kishan]

Copy down through row 1024.

Hi shg, thank you for the formula but I can't figure out "Me" in the formula

Please could you check?

Regards,
Kishan

 

[shg]

In B2, replace Me with B2.

 

[lrobbo314]

I am trying to figure out how you would do this in VBA using recursion, and I can't figure it out. Does anyone know how you would do that?

 

[Kishan]

In B2, replace Me with B2.

Hi shg, now it is giving results as request

Thank you very much for solving my query.

Have a nice day ahead

Regards,
Kishan