How to make Excel list all possible combinations (VBA-code)

Cusuma

Board Regular
Joined
Dec 2, 2004
Messages
91
Hi,

I really need your expert help...hope you can help me :biggrin:

If I have choice between either stocks or bonds (2 choices) and I have a period of 3 months, then how do I get Excel to list all possible combinations? 2 choices and 3 months give 2^3 = 8 possible combinations.

The possible combinations are (B=bonds, S=stocks):

BBB
BBS
BSB
BSS
SBB
SBS
SSB
SSS

BBB means that the investor invest in bonds in all three months and BBS means investment in bonds in two months and stocks in the last etc.

I want a cell for each letter. E.g. the combination BBB should be listed in cell

A1, A2 and A3

and the combinations BBS should be listed in cell

B1, B2 and B3

etc.

I have tried to write out a VBA-code but I cannot not get it to work, so I really hope you can help me.

Thank you in advance

Cheers
Cusuma

PS the choice between stocks and bonds should be a variable and so should the lenghts of the period, so I can change the choices e.g. to three assets and 10 months.
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Try something like this:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Possibilities()

<SPAN style="color:#007F00">'First, define your array of values</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> MyArray(1)

MyArray(0) = "B"
MyArray(1) = "S"

Col = 1

<SPAN style="color:#007F00">'Now, "print" the combinations by looping through your array</SPAN>
<SPAN style="color:#00007F">For</SPAN> i = 0 <SPAN style="color:#00007F">To</SPAN> 1
<SPAN style="color:#00007F">For</SPAN> j = 0 <SPAN style="color:#00007F">To</SPAN> 1
<SPAN style="color:#00007F">For</SPAN> k = 0 <SPAN style="color:#00007F">To</SPAN> 1
    Cells(1, Col) = MyArray(i)
    Cells(2, Col) = MyArray(j)
    Cells(3, Col) = MyArray(k)
    Col = Col + 1
<SPAN style="color:#00007F">Next</SPAN> k
<SPAN style="color:#00007F">Next</SPAN> j
<SPAN style="color:#00007F">Next</SPAN> i

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

The problem with a variable number of months is that you need to add more loops to the code to accomodate 10 months as opposed to three. It would be fairly easy to add another asset, you just expand the array and the number of loops...

I'll think about it some more.
 
Upvote 0
Re: How to make Excel list all possible combinations (VBA-co

Hi yee388,

Thank you very much for your help, it is greatly appreciated.

It works exactly how I wanted it to, but now I have another question :biggrin: How come I cannot add another variable to get another months? For example I tried to run the following code:
---------------------------------------------------------------------
Sub Possibilities2()

'First, define your array of values
Dim MyArray(1)

MyArray(0) = "B"
MyArray(1) = "S"


Dim i
Dim j
Dim k
Dim x

Dim Col
Col = 1

'Now, "print" the combinations by looping through your array
For i = 0 To 1
For j = 0 To 1
For k = 0 To 1
For x = 0 To 1

Cells(1, Col) = MyArray(i)
Cells(2, Col) = MyArray(j)
Cells(3, Col) = MyArray(k)
Cells(4, Col) = MyArray(x)


Col = Col + 1

Next k
Next j
Next i
Next x



End Sub
----------------------------------------------------------------

But VBA debugs. Why can I not run the above code?

Again, thank you for your help.

Cheers,
David
 
Upvote 0
Re: How to make Excel list all possible combinations (VBA-co

sorry guys,

I cannot seem to get it to work when I want more periods. I have tried the codes in the two links listed, but I think I am stucked :rolleyes:

Again, all help is appreciated :)
 
Upvote 0
Check your loop order:

For i...
For j...
For k...
For x...

...some code...

Next x
Next k
Next j
Next i

I think you have the "Next x" in the wrong place.
 
Upvote 0
Re: How to make Excel list all possible combinations (VBA-co

No need for VBA. And, you will get a far more generalized solution without it!

Suppose you have the list of items (B and S in your case, but B, S, and C in my test) in column A starting with A1.

Then, define a name (Insert | Name > Define...)
DataRng =OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),1)
where Sheet2 is the worksheet containing the data.

Then, in F1, enter the formula =OFFSET(DataRng,MOD(INT((ROW()-ROW($F$1))/(COUNTA(DataRng)^(COLUMN()-COLUMN($F$1)))),COUNTA(DataRng)),0,1,1)

For 2 months, copy F1 to G1. Copy F1:G1 to 2:4.
For 3 months, copy F1 to G1:H1 and copy F1:H1 down to 2:8.
For 4 months, copy F1 to G1:I1 and copy F1:I1 down to 2:16.
For 5 months, copy F1 to...you get the idea.

Of course, in my tests because I had values in A1:A3, the results were in 1:3, 1:9, 1:27, etc.

Edit: Generalize the formula so that the result can be entered to start in any row, not just in row 1. Change the *two* references to $F$1 so that they refers to the first cell containing the formula.
 
Upvote 0
Re: How to make Excel list all possible combinations (VBA-co

thank you guys.

yee388, thank you for clearing that up. I placed the the x in the right place and it now works like a charm.

Tusharm, your suggestions also works so thank you.

Take care

Cheers,
Cusuma
 
Upvote 0
ok I have a big problem... similar to this one:

i have to figure out all possible combinations when we have 9 different teams at once and each team can either win, tie or lose:

we can use this as an example:
1=win 0=tie 0=lose
tean1 1 0 0
team2 0 1 0
team3 1 0 0
team4 0 0 1
team5 0 1 0
team6 0 1 0
team7 1 0 0
team8 1 0 0
team9 0 0 1

we have to figure out all possible combinations of all 9 teams

help please!
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,253
Members
448,556
Latest member
peterhess2002

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