Hi
I'm looking for VBA code to help create a table similar to the one below that shows the list of all possible combinations of N objects taken in groups of P.
For example, this would be partial results of 6 objects (A,B,C,D, E F) taken 3 at a time.
Hopefully, someone may be able to help.
Please Login or Register to view this content.
- Stu
Try this
Please Login or Register to view this content.
Happy with my advice? Click on the * reputation button below
Thank you Crooza for your sample macro. It got me started towards a solution.
After DIM-ing all the variables (I use 'Option Explicit'), it produced results for 3 groups of 3 items taken from 6.
Here's my modified code...
Next I wanted to allow a variable number of items rather than 4.Please Login or Register to view this content.
Experimenting a bit gave this code for 4 items out of N.
( N is entered in cell B1 and a button starts the macro; This makes it more flexible.)
Extrapolating from this, I can see that making groups of 5 would require 5 counters and FOR loops, groups of 6 would need 6, and so on.Please Login or Register to view this content.
My next task is to generalize this to have one routine to make the groups of size K chosen from N items.
For my purposes, going up to groups of 10 chosen from 15 items would be sufficient.
When (and if) that's working, I will post it also.
-Stu
Last edited by StuCram; 04-29-2015 at 10:38 AM.
OK, I've now made a demo to generate all possible groups of size K from a set of N items.
The Excel file with the VBA macro program is attached for anyone curious.
I used a SELECT/CASE structure to execute the appropriate group of FOR loops.
I'm pleased to at how quickly the macro runs, even with several nested FOR loops
- Stu
combinations.xlsm
Here's an alternative that works for any combination of n and m without nested loops:
Please Login or Register to view this content.
Last edited by shg; 04-29-2015 at 12:28 PM.
Entia non sunt multiplicanda sine necessitate
Thank you SHG for your alternate solution.
I'm always intrigued that there frequently is more than one method to attack a problem.
I like how your solution generates the item#s within the group.
That however is not what I needed; I need the 'x' to appear in the appropriate columns as shown in my attachment.
It should not be difficult, if I wish, to adapt your method to do that.
The beauty of your solution of course is it's generality for the # of items and the group size.
Thanks, again.
- Stu
You're welcome.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks