This is challenging tricky combinations

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
2,422
Office Version
  1. 2010
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
ABCDEFGHIJKLMNOP
1
2
3
4Numbersn1n2n3n4n5n6n7n8n9n10n11n12n13n14
511
600
755
800
944
1000
1122
1211
1300
1411
1500
1600
1722
1800
1910
2015
2110
2214
2310
2412
2511
2610
2711
2810
2910
3012
3110
3205
3300
3404
3500
3602
3701
3800
3901
4000
4100
4202
4300
4450
4554
4650
4752
4851
4950
5051
5150
5250
5352
5450
5504
5600
5702
5801
5900
6001
6100
6200
6302
6400
6540
6642
6741
6840
6941
7040
7140
7242
7340
7402
7501
7600
7701
7800
7900
8002
8100
8221
8320
8421
8520
8620
8722
8820
8910
9011
9110
9210
9312
9410
Sheet1


Thank you all
</SPAN></SPAN>

Excel 2000
</SPAN></SPAN>
Regards,
</SPAN>
Moti
</SPAN>
 
Last edited:

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Maybe this:??
Code:
[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
 
Upvote 0
Maybe this:??
Code:
[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
ABCDEFGHIJKLMNOP
10920
110105
111100
112104
113100
114102
115101
116100
117101
118100
119100
120102
121100
122150
123154
124150
125152
126151
127150
128151
129150
130150
131152
132150
133104
134100
135102
136101
137100
138101
139100
140100
141102
142100
143140
144142
145Last Combi10504021010020
Sheet1


Kind Regards,</SPAN></SPAN>
Moti</SPAN></SPAN>
 
Last edited:
Upvote 0
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-ignore:padding; 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>[TABLE="width: 130"]
<!--StartFragment--> <colgroup><col width="65" span="2" style="width:65pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 65, align: right"]1[/TD]
[TD="class: xl63, width: 65, align: right"]14[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]91[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63, align: right"]364[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]4[/TD]
[TD="class: xl63, align: right"]1001[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl63, align: right"]2002[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]6[/TD]
[TD="class: xl63, align: right"]3003[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]7[/TD]
[TD="class: xl63, align: right"]3432[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]8[/TD]
[TD="class: xl63, align: right"]3003[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]9[/TD]
[TD="class: xl63, align: right"]2002[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]10[/TD]
[TD="class: xl63, align: right"]1001[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]11[/TD]
[TD="class: xl63, align: right"]364[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]12[/TD]
[TD="class: xl63, align: right"]91[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]13[/TD]
[TD="class: xl63, align: right"]14[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]14[/TD]
[TD="class: xl63, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[/TR]
[TR]
[TD="class: xl63"]TOTAL[/TD]
[TD="class: xl63, align: right"]16383[/TD]
[/TR]
<!--EndFragment--></tbody>[/TABLE]

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 :)
 
Upvote 0
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
ABCDEFGHIJKLMNOPQ
11P1P2P3P4P5P6P7P8P9P10P11P12P13P14Sum
2011
3500
4055
5400
6044
7200
8122
9011
10100
11011
12000
13200
14022
1500
16101
17516
18101
19415
20101
21123
2211
23101
2411
25101
26101
27123
28101
29505
3000
31404
3200
33202
34101
3500
36101
3700
3800
39202
4000
41505
42549
43505
44527
45516
46505
47516
48505
49505
50527
51505
52404
5300
54202
55101
5600
57101
5800
5900
60202
6100
62404
63426
64415
65404
66415
67404
68404
69426
70404
71202
72101
7300
74101
7500
7600
77202
7800
79123
80202
81123
82202
83202
8422
85202
86101
8711
88101
89101
90123
91101
92101
9300
9400
95202
9600
97101
98101
99123
100101
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>
 
Last edited:
Upvote 0
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
 
Upvote 0
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>
 
Upvote 0

Forum statistics

Threads
1,223,894
Messages
6,175,250
Members
452,623
Latest member
Techenthusiast

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top