This is challenging tricky combinations

[motilulla]

Hello,</SPAN></SPAN>

I have set of 14 numbers in cells A5:A18 I want to have combinations as shown below this probably will make 16384 rows</SPAN></SPAN>

Here is example </SPAN></SPAN>

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2
3
4	Numbers		n1	n2	n3	n4	n5	n6	n7	n8	n9	n10	n11	n12	n13	n14
5	1		1
6	0			0
7	5				5
8	0					0
9	4						4
10	0							0
11	2								2
12	1									1
13	0										0
14	1											1
15	0												0
16	0													0
17	2														2
18	0															0
19			1	0
20			1		5
21			1			0
22			1				4
23			1					0
24			1						2
25			1							1
26			1								0
27			1									1
28			1										0
29			1											0
30			1												2
31			1													0
32				0	5
33				0		0
34				0			4
35				0				0
36				0					2
37				0						1
38				0							0
39				0								1
40				0									0
41				0										0
42				0											2
43				0												0
44					5	0
45					5		4
46					5			0
47					5				2
48					5					1
49					5						0
50					5							1
51					5								0
52					5									0
53					5										2
54					5											0
55						0	4
56						0		0
57						0			2
58						0				1
59						0					0
60						0						1
61						0							0
62						0								0
63						0									2
64						0										0
65							4	0
66							4		2
67							4			1
68							4				0
69							4					1
70							4						0
71							4							0
72							4								2
73							4									0
74								0	2
75								0		1
76								0			0
77								0				1
78								0					0
79								0						0
80								0							2
81								0								0
82									2	1
83									2		0
84									2			1
85									2				0
86									2					0
87									2						2
88									2							0
89										1	0
90										1		1
91										1			0
92										1				0
93										1					2
94										1						0
Sheet1

Thank you all</SPAN></SPAN>

Excel 2000</SPAN></SPAN>
Regards,</SPAN>
Moti</SPAN>

 

[MickG]

Maybe this:??

[COLOR="Navy"]Sub[/COLOR] MG12Jan02
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] col [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
c = 4: col = 1
[COLOR="Navy"]For[/COLOR] n = 1 To 14
    [COLOR="Navy"]Set[/COLOR] Rng = Range("A" & (n + 4), Range("A" & Rows.Count).End(xlUp))
    col = col + 1: ac = col
    [COLOR="Navy"]If[/COLOR] c > 4 [COLOR="Navy"]Then[/COLOR] Cells(c + 1, col).Resize(Rng.Count).Value = Rng(1).Offset(-1).Value
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
            c = c + 1: ac = ac + 1
            Cells(c, ac) = Dn
        [COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[motilulla]

Maybe this:??

[COLOR=navy]Sub[/COLOR] MG12Jan02
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, n [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] c [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] ac [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Dim[/COLOR] col [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
c = 4: col = 1
[COLOR=navy]For[/COLOR] n = 1 To 14
    [COLOR=navy]Set[/COLOR] Rng = Range("A" & (n + 4), Range("A" & Rows.Count).End(xlUp))
    col = col + 1: ac = col
    [COLOR=navy]If[/COLOR] c > 4 [COLOR=navy]Then[/COLOR] Cells(c + 1, col).Resize(Rng.Count).Value = Rng(1).Offset(-1).Value
        [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
            c = c + 1: ac = ac + 1
            Cells(c, ac) = Dn
        [COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]Next[/COLOR] n
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

Thank you Mick, but I think this will end up making 16384 rows, this code is creating only 105 rows, I have made some more lines manually after the your code finishes may this help </SPAN></SPAN>

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
109															2	0
110			1	0	5
111			1	0		0
112			1	0			4
113			1	0				0
114			1	0					2
115			1	0						1
116			1	0							0
117			1	0								1
118			1	0									0
119			1	0										0
120			1	0											2
121			1	0												0
122			1		5	0
123			1		5		4
124			1		5			0
125			1		5				2
126			1		5					1
127			1		5						0
128			1		5							1
129			1		5								0
130			1		5									0
131			1		5										2
132			1		5											0
133			1			0	4
134			1			0		0
135			1			0			2
136			1			0				1
137			1			0					0
138			1			0						1
139			1			0							0
140			1			0								0
141			1			0									2
142			1			0										0
143			1				4	0
144			1				4		2
145		Last Combi	1	0	5	0	4	0	2	1	0	1	0	0	2	0
Sheet1

Kind Regards,</SPAN></SPAN>
Moti</SPAN></SPAN>

 

[motilulla]

MickG, here is a bit more of information. Infact, there will be 16,383 combinations. An example table has been attached below:

<style><!--table {mso-displayed-decimal-separator:"\,"; mso-displayed-thousand-separator:"\.";}@page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;}td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}.xl63 {border:.5pt solid windowtext;}--></style>

1	14
2	91
3	364
4	1001
5	2002
6	3003
7	3432
8	3003
9	2002
10	1001
11	364
12	91
13	14
14	1
TOTAL	16383

<!--StartFragment--> <colgroup><col width="65" span="2" style="width:65pt"> </colgroup><tbody>
<!--EndFragment--></tbody>

Also, i remember there was a similar query and now i searched and found the link below in which you helped me.
https://www.mrexcel.com/forum/excel-questions/900170-power-set-set.html#post4335937

But, this works perfectly if i put 1 to 14 starting from cells A1 downwards. But if i use the numbers that i have listed in post#1, column A, then it doesn't give the same results. Now, can you please help me to make a new code which can work accordingly to the post#1 layout.

Have a nice day and a good start to the week.

As I am on vacation till the 18th of January, i won't be able to reply you till i get access to my desktop computer.

Good luck.

Kind Regards,
Moti

 

[motilulla]

Hello Mick, as I tried with my friend's computer he had latest version of Excel, but just now I tried with my version 2000 using the same layout of the post#1 under this link </SPAN></SPAN>

https://www.mrexcel.com/forum/excel-questions/900170-power-set-set.html#post4335215</SPAN></SPAN>

Using the current data and it is giving the same result.</SPAN></SPAN>

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	1		P1	P2	P3	P4	P5	P6	P7	P8	P9	P10	P11	P12	P13	P14	Sum
2	0											1					1
3	5															0	0
4	0				5												5
5	4															0	0
6	0						4										4
7	2															0	0
8	1														2		2
9	0											1					1
10	1															0	0
11	0											1					1
12	0															0	0
13	2															0	0
14	0														2		2
15																0	0
16												1				0	1
17					5							1					6
18												1				0	1
19							4					1					5
20												1				0	1
21												1			2		3
22												1					1
23												1				0	1
24												1					1
25												1				0	1
26												1				0	1
27												1			2		3
28												1				0	1
29					5											0	5
30																0	0
31							4									0	4
32																0	0
33															2	0	2
34												1				0	1
35																0	0
36												1				0	1
37																0	0
38																0	0
39															2	0	2
40																0	0
41					5											0	5
42					5		4										9
43					5											0	5
44					5										2		7
45					5							1					6
46					5											0	5
47					5							1					6
48					5											0	5
49					5											0	5
50					5										2		7
51					5											0	5
52							4									0	4
53																0	0
54															2	0	2
55												1				0	1
56																0	0
57												1				0	1
58																0	0
59																0	0
60															2	0	2
61																0	0
62							4									0	4
63							4								2		6
64							4					1					5
65							4									0	4
66							4					1					5
67							4									0	4
68							4									0	4
69							4								2		6
70							4									0	4
71															2	0	2
72												1				0	1
73																0	0
74												1				0	1
75																0	0
76																0	0
77															2	0	2
78																0	0
79												1			2		3
80															2	0	2
81												1			2		3
82															2	0	2
83															2	0	2
84															2		2
85															2	0	2
86												1				0	1
87												1					1
88												1				0	1
89												1				0	1
90												1			2		3
91												1				0	1
92												1				0	1
93																0	0
94																0	0
95															2	0	2
96																0	0
97												1				0	1
98												1				0	1
99												1			2		3
100												1				0	1
Sheet3

Please can you help to make it modified to work, as I want as per layout of this thread post #1? </SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Moti</SPAN></SPAN>

 

[MickG]

I'm sorry but I don't really have the commitment and understanding required at the moment, hopefully someone else can help you.
Regrds Mick

 

[motilulla]

I'm sorry but I don't really have the commitment and understanding required at the moment, hopefully someone else can help you.
Regrds Mick

Thank you Mick, I appreciate for your reply.</SPAN></SPAN>

Have a nice weekend. Good Luck</SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Moti </SPAN></SPAN>