want to list/display all possible combinations

Hi jcbasch:

Welcome to the Board!

How about posting some sample data to show waht you are working with. It will also help if you will post what formula (if any) that you have used, the results you got and what you think are the right results.

The following is based on my solution here:

http://216.92.17.166/board2/viewtopic.php?t=44585

I'll put a new version of the code anyway....

Based on this worksheet:

I created the following scenario next to it:

where F1 is named 'Concat', F2 'Separator', F3 'InsSeparator' and F5 'Result'

The value of F1 was created using MCONCAT from MoreFunc.xll but that is not the important thing, the important is to create a string that has the same structure (Each member of the column separated by "InsSeparator" and those three strings separated by "Separator")

Next, I run the macro Test to get the results:<font face=Courier New><SPAN style="color:darkblue">Option</SPAN><SPAN style="color:darkblue">Explicit</SPAN><SPAN style="color:darkblue">Sub</SPAN> Test()
    <SPAN style="color:darkblue">Dim</SPAN> Ans<SPAN style="color:darkblue">As</SPAN><SPAN style="color:darkblue">Boolean</SPAN>, Col<SPAN style="color:darkblue">As</SPAN> Collection, i<SPAN style="color:darkblue">As</SPAN><SPAN style="color:darkblue">Long</SPAN>
    <SPAN style="color:darkblue">Set</SPAN> Col =<SPAN style="color:darkblue">New</SPAN> Collection
    Ans = BuildLoops(Range("Concat").Value, Range("Separator").Value, _
                     Range("InsSeparator").Value, Col)
    <SPAN style="color:darkblue">If</SPAN> Ans<SPAN style="color:darkblue">Then</SPAN>
        <SPAN style="color:darkblue">With</SPAN> Range("Result")
            <SPAN style="color:darkblue">For</SPAN> i = 1<SPAN style="color:darkblue">To</SPAN> Col.Count
                .Offset(i).Value = Col(i)
            <SPAN style="color:darkblue">Next</SPAN> i
        <SPAN style="color:darkblue">End</SPAN><SPAN style="color:darkblue">With</SPAN>
    <SPAN style="color:darkblue">Else</SPAN>
        MsgBox "Error !", vbCritical
    <SPAN style="color:darkblue">End</SPAN><SPAN style="color:darkblue">If</SPAN><SPAN style="color:darkblue">End</SPAN><SPAN style="color:darkblue">Sub</SPAN><SPAN style="color:darkblue">Function</SPAN> BuildLoops(<SPAN style="color:darkblue">ByVal</SPAN> St<SPAN style="color:darkblue">As</SPAN><SPAN style="color:darkblue">String</SPAN>, _
                    <SPAN style="color:darkblue">ByVal</SPAN> Sep<SPAN style="color:darkblue">As</SPAN><SPAN style="color:darkblue">String</SPAN>, _
                    <SPAN style="color:darkblue">ByVal</SPAN> Sep2<SPAN style="color:darkblue">As</SPAN><SPAN style="color:darkblue">String</SPAN>, _
                    <SPAN style="color:darkblue">ByRef</SPAN> Col<SPAN style="color:darkblue">As</SPAN> Collection)<SPAN style="color:darkblue">As</SPAN><SPAN style="color:darkblue">Boolean</SPAN>
    <SPAN style="color:darkblue">Dim</SPAN> Ar<SPAN style="color:darkblue">As</SPAN><SPAN style="color:darkblue">Variant</SPAN>, Ar2<SPAN style="color:darkblue">As</SPAN><SPAN style="color:darkblue">Variant</SPAN>, ArMain<SPAN style="color:darkblue">As</SPAN><SPAN style="color:darkblue">Variant</SPAN>
    <SPAN style="color:darkblue">Dim</SPAN> i<SPAN style="color:darkblue">As</SPAN><SPAN style="color:darkblue">Long</SPAN>, j<SPAN style="color:darkblue">As</SPAN><SPAN style="color:darkblue">Long</SPAN>, Ctr<SPAN style="color:darkblue">As</SPAN><SPAN style="color:darkblue">Long</SPAN>, TempSt<SPAN style="color:darkblue">As</SPAN><SPAN style="color:darkblue">String</SPAN>
    St = Application.Substitute(St, " ", "")
    Ar = Split(St, Sep)
    <SPAN style="color:darkblue">If</SPAN><SPAN style="color:darkblue">Not</SPAN> IsArray(Ar)<SPAN style="color:darkblue">Then</SPAN><SPAN style="color:darkblue">Exit</SPAN><SPAN style="color:darkblue">Function</SPAN>
    <SPAN style="color:darkblue">ReDim</SPAN> ArMain(1<SPAN style="color:darkblue">To</SPAN><SPAN style="color:darkblue">UBound</SPAN>(Ar) -<SPAN style="color:darkblue">LBound</SPAN>(Ar) + 1)
    <SPAN style="color:darkblue">For</SPAN> i =<SPAN style="color:darkblue">LBound</SPAN>(Ar)<SPAN style="color:darkblue">To</SPAN><SPAN style="color:darkblue">UBound</SPAN>(Ar)
        Ar2 = Split(Ar(i), Sep2)
        Ctr = Ctr + 1
        ArMain(Ctr) = Ar2
    <SPAN style="color:darkblue">Next</SPAN> i
    <SPAN style="color:darkblue">For</SPAN> j =<SPAN style="color:darkblue">LBound</SPAN>(ArMain(1))<SPAN style="color:darkblue">To</SPAN><SPAN style="color:darkblue">UBound</SPAN>(ArMain(1))
        TempSt = ArMain(1)(j)
        BuildString 1, ArMain, Col, TempSt, Sep
    <SPAN style="color:darkblue">Next</SPAN> j
    BuildLoops =<SPAN style="color:darkblue">True</SPAN><SPAN style="color:darkblue">End</SPAN><SPAN style="color:darkblue">Function</SPAN><SPAN style="color:darkblue">Private</SPAN><SPAN style="color:darkblue">Function</SPAN> BuildString(<SPAN style="color:darkblue">ByRef</SPAN> i<SPAN style="color:darkblue">As</SPAN><SPAN style="color:darkblue">Long</SPAN>, _
                            <SPAN style="color:darkblue">ByRef</SPAN> ArMain<SPAN style="color:darkblue">As</SPAN><SPAN style="color:darkblue">Variant</SPAN>, _
                            <SPAN style="color:darkblue">ByRef</SPAN> Col<SPAN style="color:darkblue">As</SPAN> Collection, _
                            <SPAN style="color:darkblue">ByRef</SPAN> TempSt<SPAN style="color:darkblue">As</SPAN><SPAN style="color:darkblue">String</SPAN>, _
                            <SPAN style="color:darkblue">ByRef</SPAN> Sep<SPAN style="color:darkblue">As</SPAN><SPAN style="color:darkblue">String</SPAN>)
    <SPAN style="color:darkblue">Dim</SPAN> j<SPAN style="color:darkblue">As</SPAN><SPAN style="color:darkblue">Long</SPAN>, St<SPAN style="color:darkblue">As</SPAN><SPAN style="color:darkblue">String</SPAN>
    St = TempSt
    <SPAN style="color:darkblue">If</SPAN> i<<SPAN style="color:darkblue">UBound</SPAN>(ArMain)<SPAN style="color:darkblue">Then</SPAN>
        <SPAN style="color:darkblue">For</SPAN> j =<SPAN style="color:darkblue">LBound</SPAN>(ArMain(i + 1))<SPAN style="color:darkblue">To</SPAN><SPAN style="color:darkblue">UBound</SPAN>(ArMain(i + 1))
            TempSt = St & Sep & ArMain(i + 1)(j)
            BuildString i + 1, ArMain, Col, TempSt, Sep
        <SPAN style="color:darkblue">Next</SPAN> j
    <SPAN style="color:darkblue">Else</SPAN>
        Col.Add TempSt
    <SPAN style="color:darkblue">End</SPAN><SPAN style="color:darkblue">If</SPAN><SPAN style="color:darkblue">End</SPAN><SPAN style="color:darkblue">Function</SPAN></FONT>

The results I get (Not showing all 30...) are the following:

If you need to have each entry in a separate column, use Text To Columns after running the macro.

Excel's COMBIN function indicates that there are 120 possible combinations.

COMBIN "Returns the number of combinations for a given number of items. Use COMBIN to determine the total possible number of groups for a given number of items" (from the Excel help file).

Syntax:
=COMBIN(Number of items, Size of the Group)

Number of items: 10
Size of the Group: 3

=COMBIN(10,3)
=120

Using Juan's data, you could have:

TermA1,TermB1,TermC1
TermA1,TermC1,TermB1
TermB1,TermA1,TermC1
TermC1,TermA1,TermB1

Etc.

I suppose it depends on how the OP defines "combination".

Regards,


Mike

Mike, I got a 'thank you' PM, so I guess this approach worked.

And there's one flaw in your logic of the combinations, because you're assuming that all 10 items are part of the same group, where actually you have 3 different groups, each with a different number of items within.

The basic logic that the OP applied is the easiest (And fastest) to get the count of possible combinations:

ItemsInA * ItemsInB * ItemsInC

Or, if you want a more techincal and statistical approach:

Combinat(5, 1) * Combinat(3, 1) * Combinat(2, 1)

which just results in the same 30 items...

Juan Pablo González said:

Mike, I got a 'thank you' PM, so I guess this approach worked.

And there's one flaw in your logic of the combinations, because you're assuming that all 10 items are part of the same group, where actually you have 3 different groups, each with a different number of items within.

The basic logic that the OP applied is the easiest (And fastest) to get the count of possible combinations:

ItemsInA * ItemsInB * ItemsInC

Or, if you want a more techincal and statistical approach:

Combinat(5, 1) * Combinat(3, 1) * Combinat(2, 1)

which just results in the same 30 items...

Click to expand...

Hi Juan, actually I did receive an error when trying to run the macro yesterday, but wanted to try to work through it if I could.

I was able to assemble the concant:

TermA1+TermA2+TermA3+TermA4+TermA5,TermB1+TermB2+TermB3,TermC1+TermC2

I then copied your macro code into the vis basic module, ran the macro "test" and received a "run time error 1004 method 'range' of object of object' _global' failed"

when I clicked "debug" the vis basic highlighted this part of the code:

Ans = BuildLoops(Range("Concat").Value, Range("Separator").Value, _
Range("InsSeparator").Value, Col)

would you have any suggestions? I'm working on getting the download to display the actual sheets in html, but for now I hope this is a fairly clear explanation. using exel 2000

Did you name the cell references ?

where F1 is named 'Concat', F2 'Separator', F3 'InsSeparator' and F5 'Result'

Click to expand...

Select each cell, click on the name box (The one to the left of the formula bar), and put the name. Or change the code to use the "real" addresses, where it says Concat, put F1, where it says Separator put F2, etc.

many thanks again Juan, that was it.

check out this thread

http://www.mrexcel.com/board2/viewtopic.php?t=40076&highlight=