troubleshooting Question

Make unique  combinations in separate sheets

Avatar of Pedro
Pedro asked on
VB ScriptMicrosoft ExcelMicrosoft Office
50 Comments1 Solution287 ViewsLast Modified:
The idea is to create unique combinations from the numbers in B11:AJ15.

The number sets are listed in B11:AJ15. I'd prefer a prompt to select the numbers as the sets may change.

1)  In order to so unique numbers must be identified. I have done this in AM11:AV17. The unique numbers in AM11:AV17 need not be used as this may create errors in the code.

2) to limit the number of combinations these should be limited as follows:

a)  each combination is made up of 5 unique numbers per set. An example =  1,3,11,12, 21
b)  no more than one sequential number per set. In the example above,11 and 12 are sequential, thus no other sequential numbers can appear.

c) limit of two numbers from same "deci". A deci is defined as: 1-9, 10-19, 20-29, 30-39, 40-49, 50-59, 60-69, 70-79. For instance, where 11 and 12 appear 13 cannot. It would violate the deci rule.

The result should be a page of combinations. These pages should:

1) begin with the numbers in B3:L3. In other words the first page should be labeled "Number_3" and begin with the number 3 in B5. The next page should be labeled "Number_4" and begin with number 4 in B5, etc.

2) if the code is run again it should delete the prior pages created and should begin with numbers from B3:L3

The file Combinations Sheets contains the fields referred to above.

The file "Matched-And_Unmatched.xlsb" contains a sample script that makes pages but does not let me choose the numbers I wish to make combinations with. This is the added feature I need.
Join our community to see this answer!
Unlock 1 Answer and 50 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 50 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros