Combinations ?

Mark · May 13th 2004

I thought I knew how to do this, but I am stumped by what must be a basic issue. I have spent an hour searching this forum, but to no effect.

I have seven "components" which I want to combine in different ways to make "products"

I want to find out the number of ways I could combine the different components to make different products. The =COMBIN formula doesn't do it properly (ie for all possible combinations)

Having found out this total it would also be nice if I could get a list of the possible combinations <g>

Help much appreciated
regards
Mark

=RSI · May 13th 2004

The total number of ways of arranging n objects is given by n! (factorial). Use the =FACT(n) formula or the equivalent to using the =COMBIN would be to use =PERMUT(n,n). Each would give you 5040 for 7 objects.

Anonymous · May 13th 2004

It's not exactly clear by what you mean when you say:

Quote

I have seven "components" which I want to combine in different ways to make "products"

How many total components are in each product? Can a given component (type) be used more than once? (e.g., 1 of component A and 2 of component B, and 5 of component C)

Your construction/combining rules will determine the answer. It sounds like it is not a straight-forward permutations (n!) or combinations (a!)*((n-a)!)/n! kind of problem.

Anonymous · May 13th 2004

If a "product" is defined by which of the 7 components are in it and the amount of the component doesn't matter and the order doesn't matter, then I think the answer should be 2^7-1 or 127. Each of the 7 components is either in a product or not, so thats 2 choices fro each of the 7 components, or 2^7. There is no product with no copmponents so 1 must be subtracted from 2^7. To find tghe different possibilities, make a column of numbers 1 to 127 and then in the next column convert those integers to binary useing the DEC2BIN function (may be from the Analysis ToolPak add-in)

Mark · May 13th 2004

Hello & Thanks to all for your interest.

Sorry my details were insufficiently clear if I discuss the context it may be explain what I am trying to do ....

I wish to build "products" that will help wedding speakers. I want to offer various components which may be purchased individually or in combinations.

Components:
* 2 speeches pack - Groom
* 5 speeches pack - Groom
* 2 speeches pack - Best Man
* 5 speeches pack - Best Man
* 2 speeches pack - Father of the bride
* 5 speeches pack - Father of the bride
* selected Jokes/Humour
* Selected Toasts & Quotes

(oops, that's

For example a customer may want just 2 example speeches,
or a customer may want to buy speeches for themselves and the other speakers, with or without extra humour and or Quotes & Toasts.

I feel sure excel can tell me how many combinations I am looking at, and a list of them, but I don't know how.

Who knows when I see how big the list is I may decide to cut the number of components <wry grin>

Here's to hoping you can help
regards
Mark

Anonymous · May 13th 2004

Assuming the 5 speech packages include the 2 speech packages there are 3 possibilities for the groom, 3 for the best man, 3 for the father, and 2 for the each of the others for a total of 108, from which we subtract 1 for none of everything, so 107 possible combinations. A simple macro could write all possible combos, but before writing one, have I correctly understood what you want?

Anonymous · May 13th 2004

Here's a macro to try. It will write to the activesheet assuming it's a blank sheet, so be careful.

Code

Sub DoCombos()
Dim i As Integer, g As Integer, b As Integer, f As Integer, j As Integer, t As Integer
i = 1
For g = 1 To 3
For b = 1 To 3
For f = 1 To 3
For j = 1 To 2
For t = 1 To 2
If g + b + f + j + t = 5 Then
Cells(1, 1) = "Package Number"
Cells(1, 2) = "2 speeches pack - Groom"
Cells(1, 3) = "5 speeches pack - Groom"
Cells(1, 4) = "2 speeches pack - Best Man"
Cells(1, 5) = "5 speeches pack - Best Man"
Cells(1, 6) = "2 speeches pack - Father of the bride"
Cells(1, 7) = "5 speeches pack - Father of the bride"
Cells(1, 8) = "Selected Jokes/Humour"
Cells(1, 9) = "Selected Toasts & Quotes"
Range("A1:I1").Orientation = 75
Else
i = i + 1
doLine i, g, b, f, j, t
End If
Next t
Next j
Next f
Next b
Next g
End Sub


Sub doLine(i As Integer, g As Integer, b As Integer, f As Integer, j As Integer, t As Integer)
Cells(i, 1) = i - 1
If g &gt; 1 Then Cells(i, g) = "X"
If b &gt; 1 Then Cells(i, 2 + b) = "X"
If f &gt; 1 Then Cells(i, 4 + f) = "X"
If j &gt; 1 Then Cells(i, 8) = "X"
If t &gt; 1 Then Cells(i, 9) = "X"
End Sub

Display More

Mark · May 13th 2004

Hello Derk

Thank you for your comments and macro. I have spent the last couple of hours trying it out - very clever! way beyond my ability. The results I can understand.

Please may I ask you some follow on questions?

[1] you say...there are 3 possibilities for the groom, 3 for the best man, 3 for the father, and 2 for the each of the others .. how did you determine this? I would have though there were 2 options ie groom2 or groom5 speeches? the start of your macro seems to depend upon this determination in order to work out the number of combinations.

[2] I have fiddled with your macro so it puts "grm,fob,bn,J,QT" in the columns where the x's are. Is it possible to easily change the macro so it puts text such as "grm2,grm5,bm2,bm5,fob2,fob5,J,QT" in the relevant columns. In this way each column would contain a description of the component that I need to add to each 'product'.

[3] in the attached file, when I try to build a file-name based on the components within the file I end up with all sort of spurious characters in positiions for null components.
eg I get GRM2++BM5++FOB2++J+QT
when I would like GRM2+BM5+FOB2+J+QT
How could I get the macro to list the component names in a form like this?

[4] Now that I have seen what is possible and fired with creative ideas I wonder if it is also possible to take the list in [3] and write a line by line bread down of each component
eg for the above
GRMa
GRMb
BMa
BMb
BMc
BMd
BMe
FOBa
FOBb
J
QT

This way I could use each [4]list in the program I use to build the product.

If asking all this is an imposition, my appologies, I am really appreciative of having now an accurate list component mixes which I can work with on paper; your help just seemed like the start of such good ideas <smile>

Regards
Mark

Anonymous · May 14th 2004

1) the three possibilities for the groom are nothing, 2 speeches, and 5 speeches.
2) and 3)
replace the old DoLine sub with

Code

Sub doLine(i As Integer, g As Integer, b As Integer, f As Integer, j As Integer, t As Integer)
    Dim s As String, k As Integer
    Cells(i, 1) = i - 1
    If g = 2 Then
        Cells(i, g) = "GRM2"
    ElseIf g = 3 Then
        Cells(i, g) = "GRM5"
    End If
    If b = 2 Then
        Cells(i, 2 + b) = "BM2"
    ElseIf b = 3 Then
        Cells(i, 2 + b) = "BM5"
    End If
    If f = 2 Then
        Cells(i, 4 + f) = "FOB2"
    ElseIf f = 3 Then
        Cells(i, 4 + f) = "FOB5"
    End If
    If j &gt; 1 Then Cells(i, 8) = "J"
    If t &gt; 1 Then Cells(i, 9) = "QT"
    For k = 2 To 9
        If Not IsEmpty(Cells(i, k)) Then s = s & "+" & Cells(i, k)
    Next k
    Cells(i, 10) = Mid(s, 2)
End Sub

Display More

4) I don't understand what you want. Is it to take the horizontal list and make it vertical? Where would it got for each line. Can you give a fuller example?

Mark · May 26th 2004

Combination -> build product definition file

Quote from Derk

4) I don't understand what you want. Is it to take the horizontal list and make it vertical? Where would it got for each line. Can you give a fuller example?

Hello Derk

Glad to be back after the 'break'

* your revised macro has worked brilliantly. I have tried it out and studdied it. It is still way beyond my coding ability but I have understood it enough to be able to add and subtract products from the combinatins. So for me now it is a very useful tool. Once again thanks.

[4]
As regards point 4.

Each of the product combinations we can now list tells me which components I want in the eventual product file. The Macro also defines for me the name of the file.

Next I have to write product definition files (*.iss) for a setup.exe creation program called INNO setup. I need one *.ISS file for each product file I am building.

Each line in the INNO setup *.iss file determines something about how the final product file will behave on the customers computer. Some of these lines determine which components will be built into the product setup file.

To cut a long story short(er) for each product name defined by the macro I will make an *iss file which then produces the product file that the customer downloads and clicks on to start the setup.

eg
macro ---> b2+f5+g5+J+OT ---> b2+f5+g5+J+OT.iss ----> b2+f5+g5+J+OT.exe

In the attached file I have an example iss file this includes all components in blue text, and the 'standard' text in black. Normally I would delete the unwanted component lines and then copy the text to notepad and save the text with a product-file-name.iss

If there were some way to :-
* for each product combination identified by macro
* insert appropriate components to the "file.iss" sheet
* then save sheet as text with file name = path/product-name.iss

That would be ideal.

Coplications i can foresee:-
* in macro/product name I can say "2 best man speeches = b2"
in the file.iss I need to specify each component as speech-bm-a.html, and speech-bm-b.html

* the blue text in file.iss is not all in one block

Anyway Derk,
Thats what I was thinking of, once again thank for your interest, and any help is much appreciated.

regards
Mark

Anonymous · May 26th 2004

Mark,
I think I understand what you want. I won't have time to work on it today, but will give it a shot tomorrow if no one comes to the rescue in the meantime.

Ranger · May 26th 2004

Hi Mark,

Don't you only have 5 items for combinations, surely no-one will combine a 2 pack and a 5 pack Groom's speech for instance.

Regards,

Bill

Ranger · May 26th 2004

Hi Mark,

Sorry, I missed page 2 of the Posting, for some reason it did not show, but it is now after my post. Derk is of course correct with the 107 combinations.

Regards,

Bill

Anonymous · May 27th 2004

Mark,
Try the code below. I made a new sub called MakeISS that should be called at the end of the DoLine sub. There are several in your workbook, so I leave it to you to pick the right one, but I show an example. The new sub should create and save an ISS sheet for each line. To make this work you will need to delete (not clear) columns B:G of the file.iss sheet to avoid having extra stuff exported. In limited testing, the sub works as I understand what was needed, but I'm unfamiliar with ISS so I my well have misse something. Also I noticed you added CD and CD2 in the main sheet, but there were no lines for them in the file.iss sheet. I leave it to you to insert the correct lines and to adjust the macro accordingly, which right now ignores them.

Code

'end of DoLine sub
For k = 2 To 11
If Not IsEmpty(Cells(i, k)) Then s = s & "+" & Cells(i, k)
Next k
Cells(i, 12) = Mid(s, 2)
MakeISS (i)
End Sub


Sub MakeISS(i)
    Dim s As Worksheet, k As Integer
    Sheets("file.iss").Copy
    Set s = ActiveSheet
    ThisWorkbook.Activate
    s.Name = Cells(i, 12)
    s.Cells(33, 1) = "OutputBaseFilename=" & s.Name
    s.Cells(34, 1) = "DefaultDirName={pf}\FineWeddingSpeeches\" & s.Name


    For k = 27 To 11 Step -1
        Select Case k
            Case 11, 12
                If IsEmpty(Cells(i, 2)) And IsEmpty(Cells(i, 3)) Then s.Rows(k).Delete
            Case 13, 14, 15
                If IsEmpty(Cells(i, 3)) Then s.Rows(k).Delete
            Case 16, 17
                If IsEmpty(Cells(i, 4)) And IsEmpty(Cells(i, 5)) Then s.Rows(k).Delete
            Case 18, 19, 20
                If IsEmpty(Cells(i, 5)) Then s.Rows(k).Delete
            Case 21, 22
                If IsEmpty(Cells(i, 6)) And IsEmpty(Cells(i, 7)) Then s.Rows(k).Delete
            Case 23, 24, 25
                If IsEmpty(Cells(i, 7)) Then s.Rows(k).Delete
            Case 25
                If IsEmpty(Cells(i, 8)) Then s.Rows(k).Delete
            Case 27
                If IsEmpty(Cells(i, 9)) Then s.Rows(k).Delete
        End Select
    Next k
    With s.Parent
        .SaveAs s.Name & ".iss", xlTextWindows
        .Close False
    End With
End Sub

Display More

Mark · May 29th 2004

macro to write out text file from worksheet

Hello Derk

wonderful!

Quote from Derk

Mark,
The new sub should create and save an ISS sheet for each line. ...

yes it works as suggested wirting a new .iss file for each product.

However the .iss files contain formatting which interfers with the operation of the ISS program. Specifically Tab-code is included and there are many extra " " " symbols. I think you may need to refer to the original XL attachment file*iss worksheet to see that many of these are extra to requirements. Unfortunately the extra formatting does not show up if I copy the output into XL to do the comparison

I wonder if there is a way to specify a file save-as type which does not add extra "'s and tabs?

Here is an example .iss file (I hope it displays online the way it shows up locally )
-----------------------------------------------------
; Script generated by the Inno Setup Script Wizard.
; SEE THE DOCUMENTATION FOR DETAILS ON CREATING INNO SETUP SCRIPT FILES!
; Files for Finespeeches.com:-
; INNO setup Speech file name list.xls / INNO.bat /

[Files]
"Source: ""Read-me-first.htm""; DestDir: ""{app}""; Flags: ignoreversion"
"Source: ""offline-*.*""; DestDir: ""{app}""; Flags: ignoreversion"
"Source: ""favicon.ico""; DestDir: ""{app}""; Flags: ignoreversion"
"Source: ""*File.txt""; DestDir: ""{app}""; Flags: ignoreversion"
"Source: ""speech-bm-a.htm""; DestDir: ""{app}""; Flags: ignoreversion"
"Source: ""speech-bm-b.htm""; DestDir: ""{app}""; Flags: ignoreversion"
"Source: ""speech-fob-a.htm""; DestDir: ""{app}""; Flags: ignoreversion"
"Source: ""speech-fob-b.htm""; DestDir: ""{app}""; Flags: ignoreversion"
"Source: ""speech-grm-a.htm""; DestDir: ""{app}""; Flags: ignoreversion"
"Source: ""speech-grm-b.htm""; DestDir: ""{app}""; Flags: ignoreversion"
"Source: ""_Wedding_Jokes.html""; DestDir: ""{app}""; Flags: ignoreversion"

"; NQTE: Don't use ""Flags: ignoreversion"" on any shared system files"

[Setup]
UsePreviousAppDir=no
OutputBaseFilename=b2+f2+g2+J
DefaultDirName={pf}\FineWeddingSpeeches\b2+f2+g2+J

SourceDir=D:\My Documents\_www.finespeeches.com\public\www\components\
OutputDir=../sell
AppName=Fine Wedding Speeches
AppVerName=Wedding Speech Pack Contents
AppPublisher=FineSpeeches.com
AppPublisherURL=http://www.finespeeches.com
AppSupportURL=http://www.finespeeches.com
AppUpdatesURL=http://www.finespeeches.com
DefaultGroupName=Fine Wedding Speeches
AllowNoIcons=yes
LicenseFile=LicenseFile.txt
;InfoBeforeFile=InfoBeforeFile.txt
;InfoAfterFile=InfoAfterFile.txt

[Tasks]
"Name: ""desktopicon""; Description: ""Create a &desktop icon""; GroupDescription: ""Additional icons:"""
"Name: ""quicklaunchicon""; Description: ""Create a &Quick Launch icon""; GroupDescription: ""Additional icons:""; Flags: unchecked"

[INI]
"Filename: ""{app}\Read-me-first.url""; Section: ""InternetShortcut""; Key: ""URL""; String: ""http://www.finespeeches.com/help/"""

[Icons]
"Name: ""{group}\1-Your Fine Wedding Speeches""; Filename: ""{app}\Read-me-first.htm"""
"Name: ""{group}\2-Fine Wedding Speeches Help on the Web""; Filename: ""{app}\Read-me-first.url"""
"Name: ""{group}\4-Uninstall Fine Wedding Speeches""; Filename: ""{uninstallexe}"""
"Name: ""{group}\3-Terms & Conditions""; Filename: ""{app}\LicenseFile.txt"""
"Name: ""{userdesktop}\Fine Wedding Speeches""; Filename: ""{app}\Read-me-first.htm""; Tasks: desktopicon"
"Name: ""{userappdata}\Microsoft\Internet Explorer\Quick Launch\Fine Wedding Speeches""; Filename: ""{app}\Read-me-first.htm""; Tasks: quicklaunchicon"

[Run]
"Filename: ""{app}\Read-me-first.htm""; Description: ""Launch Fine Wedding Speeches""; Flags: shellexec postinstall skipifsilent"

[UninstallDelete]
"Type: files; Name: ""{app}\Read-me-first.url"""
--------------------------------------------------

Once again, many thanks for your help

regards
Mark

Anonymous · May 29th 2004

Did you delete (not clear) columns B:G of the file.iss sheet? When I did that it kept the extra stuff from being exported exported. If that doesn't work for you, there are a variety of filetypes you can try. Use help for Saveas to see the choices, and try various text ones. If the used range of the file.iss sheet is just column A, the extra characters should not appear; thats why deleting the columns that did have info in them is necessary.

Mark · May 30th 2004

Hello Highly Valued Member, Derk!

Thank you for the information & explanation.

I had DELeded every non-A column, however I have double checked and ... a test of ctrl+end leaves the cursor in cell a130, so I geuss all non-col-A cells are empty.

Despite thie the unwanted formatting is still appearing (not the tab-codes this time, only the extra "'s)

I have experimented with the saveas command and discovered that the "Formatted TEXT (SPACE DELIMITED)(*.PRN)" format saves a file with the propper formatting.

please could you tell me the appropriate macro command to replace
.SaveAs s.Name & ".iss", -->xlTextWindows <--
(I tried the help function in macro-visual-basic but it has Never been able to find all its files so no luck this time either)

Saving the files in this format manually insists on appending .prn to the .iss file, ie I end up with *.iss.prn
Using the macro to saveas is it possible to save the file in prn format without the .prn extension?
(if not I can always use the product list to create the content for a batch file which renames *.iss.prn as *.iss; but it would be nice to be more elegant - and it is very informative to see how you have solved the problems along the way.)

Anonymous · Jun 1st 2004

Changing xlTextWindows to xlTextPrinter should eliminate the extra quotes. At least it did for me.

Mark · Jun 3rd 2004

Marvellous.

Yes this works, also it doesn't put the *.prn extension on which is an added bonus.

I had to run the macro 4 times before I got it to go all the way through because I ran into "out of memory" crashes. This was not due to hard drive storage, and I am not sure why on the 4th try it completed properly/fully............

Is there a way to get the script to start part way through? for example one time it successfully completed 419 items before crashing, and there are only 430 in my test alltogether. If I could get it to start from 420 that would be useful.

One other thought - If I run the program without having first deleted any previous *.iss files, I get asked if I want to replace or not. Is there a code setting which will automatically replace?

Other than these 2 "icing on the cake" items I now have a system which
1) Macro:
* determines all possible combinations of components, and
* writes the set-up program definition file.
2) Copy list + formula
* creates the content for a Batch file which runs the setup program for each set-up file/product
* creates the content for a Batch file which runs the Product-file program for each product - ie tests that the product has been properly built

Many thanks indeed for your help and persistence as we worked through the system.

regards
Mark

Anonymous · Jun 3rd 2004

To keep ther alerts from showing just use the following mod to the saveas line
Application.DisplayAlerts = False
.SaveAs s.Name & ".iss", xlTextPrinter
Application.DisplayAlerts = True

As to restarting the process:
You could delete the call to MakeISS when you first run the program. You will then have a sheet with all of the combinations and names. Then in a separate macro you can call MakeIss to process the lines. The i value being passed to MakeISS tells it which line on the sheet it is processing. You could then start writing by

Code

Sub makeSheets()
    Dim i As Integer
    For i = 2 To 431
        MakeISS i
    Next i
End Sub

If it doesn't make it through them all, then just change the 2 to where you like to start again.

Combinations ?

Participate now!

Share

Users Viewing This Thread