Combinations ?

  • 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&gt;

    Help much appreciated

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

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


    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.

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

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

    * 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 8)

    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&gt;

    Here's to hoping you can help

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

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

  • 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

    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&gt;


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

    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?

  • Combination -&gt; 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.

    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.

    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.


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

  • macro to write out text file from worksheet

    Hello Derk


    Quote from Derk

    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.
    ; Files for
    ; INNO setup Speech file name list.xls / INNO.bat /

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


    SourceDir=D:\My Documents\\public\www\components\
    AppName=Fine Wedding Speeches
    AppVerName=Wedding Speech Pack Contents
    DefaultGroupName=Fine Wedding Speeches

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

    "Filename: ""{app}\Read-me-first.url""; Section: ""InternetShortcut""; Key: ""URL""; String: """""

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

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

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

    Once again, many thanks for your help


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

  • 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.)

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


  • 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

    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.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!