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

[Cusuma]

Hi,

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

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.

 

[yee388]

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.

 

[yee388]

Also, check out http://www.mrexcel.com/board2/viewtopic.php?t=114976&highlight= for a more complex version of your question.

 

[Cusuma]

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 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

 

[Juan Pablo González]

Re: How to make Excel list all possible combinations (VBA-co

This post could help too

http://www.mrexcel.com/board2/viewtopic.php?t=49774

 

[Cusuma]

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

Again, all help is appreciated

 

[yee388]

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.

 

[tusharm]

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.

 

[Cusuma]

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

 

[SLY007]

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!