Excel VBA combinations for 2,3,4,5 or 6 numbers

Eager_For_Knowledge

New Member
Joined
Sep 2, 2014
Messages
5
I'm trying to create the combination list of 2,3,4,5 or 6 numbers depending on what is required. For instance in cell(1,1) i put 6, in cell(1,2) i put 3, in cell(1,3) i put 4, then macro would produce the combination list of the 3 numbers listed and so on. i want the combination results to be listed in ColumnA, delimited like 1-12-24-5. if or when the combination results reach row 1048576, i want it to move to ColumnB when rows reach 1048576 move to ColumnC and so on, until the combination is complete. I'm new to VBA and need help.

Example:

ColumnA ColumnB ColumnC ColumnD ColumnE ColumnF ColumnG
2 Enter number here: 5 2 10 8 1
3 Results:
4 5-2-10-8-1
5 2-5-10-8-1
6 10-8-5-2-1
7
8



1048576 8-10-5-1-2
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
There's a workbook at https://app.box.com/shared/f3puhccnqa that will do this:

[Table="width:, class:grid"][tr][td] [/td][td]
B​
[/td][td]
C​
[/td][/tr]
[tr][td]
7​
[/td][td]
m​
[/td][td]1 2 5 8 10[/td][/tr]

[tr][td]
8​
[/td][td]
5​
[/td][td]10 8 5 2 1[/td][/tr]

[tr][td]
9​
[/td][td]
5​
[/td][td]10 8 5 1 2[/td][/tr]

[tr][td]
10​
[/td][td]
5​
[/td][td]10 8 2 5 1[/td][/tr]

[tr][td]
11​
[/td][td]
5​
[/td][td]10 8 2 1 5[/td][/tr]

[tr][td]
12​
[/td][td]
5​
[/td][td]10 8 1 5 2[/td][/tr]

[tr][td]
13​
[/td][td]
5​
[/td][td]10 8 1 2 5[/td][/tr]

[tr][td]
14​
[/td][td]
5​
[/td][td]10 5 8 2 1[/td][/tr]

[tr][td]
15​
[/td][td]
5​
[/td][td]10 5 8 1 2[/td][/tr]

[tr][td]
16​
[/td][td]
5​
[/td][td]10 5 2 8 1[/td][/tr]

[tr][td]
17​
[/td][td]
5​
[/td][td]10 5 2 1 8[/td][/tr]

[tr][td]
18​
[/td][td]
5​
[/td][td]10 5 1 8 2[/td][/tr]

[tr][td]
19​
[/td][td]
5​
[/td][td]10 5 1 2 8[/td][/tr]

[tr][td]
20​
[/td][td]
5​
[/td][td]10 2 8 5 1[/td][/tr]

[tr][td]
21​
[/td][td]
5​
[/td][td]10 2 8 1 5[/td][/tr]

[tr][td]
22​
[/td][td]
5​
[/td][td]10 2 5 8 1[/td][/tr]

[tr][td]
23​
[/td][td]
5​
[/td][td]10 2 5 1 8[/td][/tr]

[tr][td]
24​
[/td][td]
5​
[/td][td]10 2 1 8 5[/td][/tr]

[tr][td]
25​
[/td][td]
5​
[/td][td]10 2 1 5 8[/td][/tr]

[tr][td]
26​
[/td][td]
5​
[/td][td]10 1 8 5 2[/td][/tr]

[tr][td]
27​
[/td][td]
5​
[/td][td]10 1 8 2 5[/td][/tr]

[tr][td]
28​
[/td][td]
5​
[/td][td]10 1 5 8 2[/td][/tr]

[tr][td]
29​
[/td][td]
5​
[/td][td]…[/td][/tr]
[/table]


It won't do more than one column.
 
Upvote 0
There's a workbook at https://app.box.com/shared/f3puhccnqa that will do this:

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]
B​
[/TD]
[TD]
C​
[/TD]
[/TR]
[TR]
[TD]
7​
[/TD]
[TD]
m​
[/TD]
[TD]1 2 5 8 10[/TD]
[/TR]
[TR]
[TD]
8​
[/TD]
[TD]
5​
[/TD]
[TD]10 8 5 2 1[/TD]
[/TR]
[TR]
[TD]
9​
[/TD]
[TD]
5​
[/TD]
[TD]10 8 5 1 2[/TD]
[/TR]
[TR]
[TD]
10​
[/TD]
[TD]
5​
[/TD]
[TD]10 8 2 5 1[/TD]
[/TR]
[TR]
[TD]
11​
[/TD]
[TD]
5​
[/TD]
[TD]10 8 2 1 5[/TD]
[/TR]
[TR]
[TD]
12​
[/TD]
[TD]
5​
[/TD]
[TD]10 8 1 5 2[/TD]
[/TR]
[TR]
[TD]
13​
[/TD]
[TD]
5​
[/TD]
[TD]10 8 1 2 5[/TD]
[/TR]
[TR]
[TD]
14​
[/TD]
[TD]
5​
[/TD]
[TD]10 5 8 2 1[/TD]
[/TR]
[TR]
[TD]
15​
[/TD]
[TD]
5​
[/TD]
[TD]10 5 8 1 2[/TD]
[/TR]
[TR]
[TD]
16​
[/TD]
[TD]
5​
[/TD]
[TD]10 5 2 8 1[/TD]
[/TR]
[TR]
[TD]
17​
[/TD]
[TD]
5​
[/TD]
[TD]10 5 2 1 8[/TD]
[/TR]
[TR]
[TD]
18​
[/TD]
[TD]
5​
[/TD]
[TD]10 5 1 8 2[/TD]
[/TR]
[TR]
[TD]
19​
[/TD]
[TD]
5​
[/TD]
[TD]10 5 1 2 8[/TD]
[/TR]
[TR]
[TD]
20​
[/TD]
[TD]
5​
[/TD]
[TD]10 2 8 5 1[/TD]
[/TR]
[TR]
[TD]
21​
[/TD]
[TD]
5​
[/TD]
[TD]10 2 8 1 5[/TD]
[/TR]
[TR]
[TD]
22​
[/TD]
[TD]
5​
[/TD]
[TD]10 2 5 8 1[/TD]
[/TR]
[TR]
[TD]
23​
[/TD]
[TD]
5​
[/TD]
[TD]10 2 5 1 8[/TD]
[/TR]
[TR]
[TD]
24​
[/TD]
[TD]
5​
[/TD]
[TD]10 2 1 8 5[/TD]
[/TR]
[TR]
[TD]
25​
[/TD]
[TD]
5​
[/TD]
[TD]10 2 1 5 8[/TD]
[/TR]
[TR]
[TD]
26​
[/TD]
[TD]
5​
[/TD]
[TD]10 1 8 5 2[/TD]
[/TR]
[TR]
[TD]
27​
[/TD]
[TD]
5​
[/TD]
[TD]10 1 8 2 5[/TD]
[/TR]
[TR]
[TD]
28​
[/TD]
[TD]
5​
[/TD]
[TD]10 1 5 8 2[/TD]
[/TR]
[TR]
[TD]
29​
[/TD]
[TD]
5​
[/TD]
[TD]…[/TD]
[/TR]
</tbody>[/TABLE]


It won't do more than one column.


This is exactly what is wanted. Thank you. Is there by any chance a PERMUTATION version to this exact thing that will take into consideration excel row limit of 1048576 and move to the next column and continue permutation until it is finished?
 
Upvote 0
This is brilliant work my friend, just brilliant its exactly what i wanted. Thank you.

Is there by any chance a PERMUTATION version to this exact thing that will take into consideration that each number is between 1-35 and excel row limit of 1048576 which moves to the next column and continue permutation until it is finished?
 
Upvote 0
Does anyone have a macro that reads 7 numbers and creates a list of all combinations of 5? I'd need to type the 7 numbers each time and then run the macro. Can anyone help? Thanks
John
 
Upvote 0

Forum statistics

Threads
1,223,920
Messages
6,175,373
Members
452,638
Latest member
Oluwabukunmi

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