List combinations with parameters?

[julios]

Hi all. I am new both here and in excel. I did a quick search, but couldn't find what i was looking for (possibly cause i wouldn't recognize it even if i saw it). I have a problem and i need to ask if it is possible to solve through excel.

I have 3 categories of numbers say in 3 columns of a worksheet. For example category A has the numbers 73, 31, 55, 68. Category B 59, 57, 21, 35, 72, 65, 44, 58, 32, 33 and category C 72, 21, 37, 48, 68, 70, 49, 26, 41, 85.

I need to make combinations of 10, yes i know they would be a lot, so i want to include some parameters that will make the results fewer.

For example every 10-numbered combination should get only 2 numbers of category A, 4 numbers of category B and another 4 from category C.

The sum of each combination when the numbers are added should be no more than 500.

Is it possible for excel to generate and display all possible combinations given the above parameters??

If there is a solution and since I'm a newbie, please explain as if to an ape.

Thank you very much in advance.

 

[xenou]

If i understand correctly, you didn't use excel to generate the combinations. There is no actual formula in excel to do this, is there? (i mean i can't just tip in new numbers in your file and get other results?)

That is correct. Excel supports a programming language called VBA, which is how I came up with my results.

ξ

 

[xenou]

Note: to get less than 1000 combinations:

1) Given your original rules (picking 2 of the first group of 4 numbers, 4 of the second group of 10 numbers, and 4 of the third group of 10 numbers).
2) Applying a constraint that the numbers all add up to x or less, where x is a number such as the 500 you originally chose.

The constraint value for x would need to be 391 (986 combinations) or 392 (1066 combinations).
<a href="http://northernocean.net/etc/mrexcel/20101226_lt392.zip">List of Combinations</a>
(md5 for zip file: 5a7f6c1c4eb1d6331fe58fba4a399eb9)

Note, here, by the way are the number of possible combinations given each value of x up to 392 (if I had thought of this sooner I would have gone up to 500 for you but I truncated my list of combinations already at 392):
332 1
339 1
340 1
341 1
343 2
344 2
345 1
348 1
349 1
350 2
351 3
352 3
353 2
354 4
355 5
356 5
357 5
358 3
359 4
360 2
361 5
362 9
363 9
364 9
365 11
366 13
367 14
368 15
369 16
370 14
371 12
372 15
373 17
374 20
375 20
376 30
377 27
378 29
379 31
380 38
381 42
382 35
383 42
384 42
385 49
386 49
387 61
388 64
389 65
390 63
391 71
392 80

 

[shg]

There a workbook that implements your problem at http://www.box.net/shared/mhpyhnziua

It's designed to expanded in both groups and the numbers in each group, but mininimally tested.

 

[julios]

@xenou: i played around with your VBA code a bit and reduced the numbers in each category to 3, 6 and 6. Finally got under 1000 results, half of which are over 500sum.

There a workbook that implements your problem at http://www.box.net/shared/mhpyhnziua

It's designed to expanded in both groups and the numbers in each group, but mininimally tested.

That should be exactly what i was looking for. Unfortunately i can't use it. When i push run, i get a message that my security level is too high and that i either need to set security at a lower level or get a signed certificate by the author (or sth like that). When i set security to a lower level, the VBA window opens and i get a popup saying "Compile error. Can't find project or library" and if i try again i get a "Can't execute code in break mode" popup. I look to run the macro manually, but it is not on the list

 

[shg]

Don't ever set your security level to Low. Set it to disable macros with notifications, then enable the content if you both trust the source and need to enable macros.

When you get the message, what's the line of code is highlighted?

From the VBE (the place where you can see the code) do Tools > References, and see if one of the ticked boxes (they are all at the top of the dialog) starts with "MISSING". If so, untick the reference and try again.

 

[xenou]

I found I need to change one line in shg4421's code (though I can't say why - it's not a common error I encounter. In fact, this has never been a compile error in any code I've encountered before):

In function OutIt()

        wksOut.Rows(nOut + 1).Resize(, 2).Value = Array(dSum, Left(sOut, Len(sOut) - 1))

Change to:

        wksOut.Rows(nOut + 1).Resize(, 2).Value = Array(dSum, [COLOR="Red"]VBA.[/COLOR]Left(sOut, Len(sOut) - 1))

I'm pleasantly surprised to see you got some mileage out of my combination algorithm. It's simple but straightforward (I hope) if you can run it straight from the Visual Basic editor.

 

[xenou]

@shg4421
In the vba project explorer the project has a reference to personal.xls. Is that an oversight? I dont' have a personal.xls, at any rate. (note: solved as described above, however, in post 15)
ξ

 

[xenou]

Also I get this lovely message (having downloaded shg4421's workbook). If anyone knows wtf and how to stop this "feature" from popping up every time I save the workbook. I envision saying "okay" for the rest of my life... :

<img alt="warning popup" src="http://northernocean.net/etc/mrexcel/20101226_warning.png" />

ξ

 

[shg]

I have my security settings set to disable personal info from being saved with the file. Disable that to disable the warning.

The reference to Personal.xls is standard in my default template, but unnecessary here. Remove the reference.

I'm pleasantly surprised to see you got some mileage out of my combination algorithm.

I've no doubt your algorithm is good xenou, but I haven't looked at it. Mine is from Donald Knuth; the module from which the code was cut has detailed references that I'd be happy to provide.

 

[xenou]

Donald Knuth. That name sounds familiar. Yes, I'd be interested. I realized from a glance at your code that the algorithm was not in my vocabulary (then again, very few are. I even use bubble sorts still). pgc01 has a good one for Excel combinations and permutations floating around here too.

Note:
Just found this on Knuth: http://xkcd.com/163/
Art of Computer Programming - that's were I've seen the name.

("pleasantly surprised" - that was for julios)