Trying to find all possible combinations

Question

I have been trying to work through the following problem using excel and am not sure where to start, I have tried using combinations and permeation but only have a basic understanding of them, how would you guys approach solving this?

There are 6 kinds of coffee and there are 10 kinds of flavor shots and you can put one, two or three shots in each kind of coffee. Based on this, I would like to know (and list) the unique flavor combinations and how long you could go without having the same cup of coffee.

Answer 1

Very simple with macros.

The first thing to note is that we need combinations rather than permutations. This is because {Kona,vanella,chocolate} is the same as {Kona,chocolate,vanilla}.

Place the 6 coffee is A1 thru A6

Place the 10 flavors in B2 thru B11. Leave B1 empty.

Enter and run the following VBA macro:

Sub CoffeeMixer()
    Dim k As Long, _
        i As Long, _
        j As Long, _
        l As Long, _
        Z As Long
    Z = 1
    For i = 1 To 10
        cf = Cells(i, 1).Value
        For j = 1 To 11
            fl1 = Cells(i, 2).Value
            For k = j+1 To 11
                fl2 = Cells(k, 2).Value
                For l = k+1 To 11
                    fl3 = Cells(l, 2).Value
                    If fl1 = fl2 Or fl1 = fl3 Or fl2 = fl3 Then
                    Else
                        Cells(Z, "C").Value = cf
                        Cells(Z, "D").Value = fl1
                        Cells(Z, "E").Value = fl2
                        Cells(Z, "F").Value = fl3
                        Z = Z + 1
                    End If
                Next l
            Next k
        Next j
    Next i
End Sub

This will produce the 875 samples.

Macros are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the macro:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the macro from Excel:

1. ALT-F8
2. Select the macro
3. Touch RUN

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

Macros must be enabled for this to work!

Here is a sample:

.

.

EDIT#1

Here is the updated code:

Sub CoffeeMixer()
    Range("C:F").Clear
    Dim k As Long, _
        i As Long, _
        j As Long, _
        l As Long, _
        Z As Long
    Z = 1
    For i = 1 To 6
        cf = Cells(i, 1).Value
        For j = 1 To 11
            fl1 = Cells(j, 2).Value
            kk = j + 1
            If j = 11 Then kk = 11
            For k = kk To 11
                fl2 = Cells(k, 2).Value
                ll = 1 + k
                If k = 11 Then ll = 11
                For l = ll To 11
                    fl3 = Cells(l, 2).Value
                        Cells(Z, "C").Value = cf
                        Cells(Z, "D").Value = fl1
                        Cells(Z, "E").Value = fl2
                        Cells(Z, "F").Value = fl3
                        Z = Z + 1
                Next l
            Next k
        Next j
    Next i
End Sub

Using this version, you must fill from B1 thru B10 and leave B11 blank!

Answer 2

Build your table like so:

A       B
Coffee1 Syrup1
Coffee2 Syrup2
Coffee3 Syrup3
Coffee4 Syrup4
Coffee5 Syrup5
Coffee6 Syrup6
        Syrup7
        Syrup8
        Syrup9
        Syrup10

For the above data, the below formula will calculate the total number of combinations:

=COUNTA(A:A)*COUNTA(B:B)
+COUNTA(A:A)*COUNTA(B:B)^2
+COUNTA(A:A)*COUNTA(B:B)^3

The length of time taken to try all the different combinations depends on the level of your caffeine addiction.