excel - Generate all possible combinations of choices from mutually exclusive options -

i have optimisation problem requires me test potential combinations of choices of potential portfolios, need able adapt exclude choices.

this must done in excel.

the rules sanitised example below:

• i can chose buy fruit of 3 grocers
• the grocers may have different amount of aisles, , different combinations of fruit select from
• i can pick 1 selection of fruit (or no selection @ all) grocers

combinations

1. my first combintaon no fruit grocer
2. in next pick apples aisle 3 grocer3
3. then apples aisle 2 grocer3
4. then apples aisle 1 grocer3
5. then pick apples aisle 2 grocer2 , nothing grocer 3 (ie same choice grocer 3 combination 1 etc)
6. theni pick apples aisle 2 grocer2, , apples aisle 3 grocer3 (ie same choice grocer 3 combination 2) , on

all give me 7*4*4 = 112 possible combinations consisting of

• 7 choices grocer 1 (6 selection choices + 1 nothing)
• 4 choices grocer 2 (3 selection choices + 1 nothing)
• 4 choices grocer 3 (3 selection choices + 1 nothing)

1. unconstrained problem

my actual problem far more complicated basic structure holds true.

what have excel-formula or vba approach populate available choices for:

1. the unconstrained problem.
2. a constrained problem (for example turn off aisle 2 giving me 45 valid combinations)

2. constrained problem

what have tried

i did solve intial problem number of grocer options same mod\int approach. simple single formula patterns repeatable.

if there smart formula solution preferred, i'm open code (and route trying)

in experts-exchange paq http://rdsrc.us/qdl6tl worked on similar problem enumerate every combination of 5 different categories of things. number of things in each category varied. enumeration had consider possibility of no selection in category 1 selection drawn category.

i approached problem writing 5 digit number, number of possible digits @ each position in number variable.

sub combinatrixplus() 'forms combinations of @ least 2 subattributes taken selection. _     no more 1 subattribute may taken row. 'uses variable base counting method  dim long, ii long, j long, k long, lensep long, _     m long, mcol long, msheet long, mrow long, _     n long, nblock long, nmax long, nwide long dim v variant, vinputs variant, vresults variant dim rg range, rgdest range dim ws worksheet dim s string, sep string  application.screenupdating = false sep = ", "      'separator substring between each subattribute in results set ws = worksheets("sheet2")   'put first batch of results in worksheet set rgdest = ws.[a2]      'put results starting in cell msheet = rgdest.worksheet.index mcol = rgdest.column lensep = len(sep) set rg = selection      'cells containing subattributes nblock = 16384          'maximum number of values in results array  'clear previous results application.displayalerts = false = worksheets.count ws.index step -1     worksheets(i).cells.clear                   'clear cells     if > ws.index worksheets(i).delete   'delete sheet next application.displayalerts = true  n = rg.rows.count nwide = n       'if results lists subattributes in separate cells 'nwide = 1      'if results lists subattributes single string separators redim v(n, 1 2) vinputs = rg.value v(0, 2) = 1 = 1 n     v(i, 1) = application.counta(rg.rows(i))     v(i, 2) = (v(i, 1) + 1) * v(i - 1, 2) next nmax = v(n, 2) - 1   redim vresults(1 nblock, 1 nwide) = 1 nmax     s = ""     m = 0     ii = ii + 1     j = 1 n         k = (i mod v(j, 2)) \ v(j - 1, 2)         if k <> 0             m = m + 1             if nwide > 1 vresults(ii, j) = vinputs(j, k)             s = s & sep & vinputs(j, k)         end if     next     s = mid$(s, lensep + 1)     if nwide = 1 vresults(ii, 1) = s  'results in concatentated string     if m < 2 ii = ii - 1      if ii = nblock         application.statusbar = "now posting combination " & & " of " & nmax         mrow = rgdest.worksheet.cells(rows.count, mcol).end(xlup).row         if rgdest.worksheet.cells(mrow, mcol) <> "" mrow = mrow + 1         if mrow < rgdest.row mrow = rgdest.row         if (rows.count - mrow) >= nblock             rgdest.worksheet.cells(mrow, mcol).resize(nblock, nwide).value = vresults         else             msheet = msheet + 1             if worksheets.count < msheet worksheets.add after:=worksheets(msheet - 1)             activesheet                 set rgdest = .range(rgdest.address)                 j = 1 n                     .columns(j).columnwidth = ws.columns(j).columnwidth                 next                 mrow = rgdest.row                 .cells(mrow, mcol).resize(nblock, nwide).value = vresults             end         end if         ii = 0         redim vresults(1 nblock, 1 nwide)     end if next  if ii > 0         application.statusbar = "now posting combination " & & " of " & nmax         mrow = rgdest.worksheet.cells(rows.count, mcol).end(xlup).row         if rgdest.worksheet.cells(mrow, mcol) <> "" mrow = mrow + 1         if mrow < rgdest.row mrow = rgdest.row         if (rows.count - mrow) >= nblock             rgdest.worksheet.cells(mrow, mcol).resize(nblock, nwide).value = vresults         else             msheet = msheet + 1             if worksheets.count < msheet worksheets.add after:=worksheets(msheet - 1)             activesheet                 set rgdest = .range(rgdest.address)                 j = 1 n                     .columns(i).columnwidth = ws.columns(j).columnwidth                 next                 mrow = rgdest.row                 .cells(mrow, mcol).resize(nblock, nwide).value = vresults             end         end if     = rgdest.worksheet.usedrange.rows.count   'reset scrollbar end if application.statusbar = false   'clear status bar application.screenupdating = true end sub