We help IT Professionals succeed at work.
Get Started

Make unique  combinations in separate sheets

265 Views
Last Modified: 2020-02-16
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.
Combination-Sheets.xlsx
Matched-and-Unmatched.xlsb
Comment
Watch Question
Social distance - Wear a mask - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2020
Commented:
This problem has been solved!
Unlock 1 Answer and 50 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant

An Experts Exchange subscription includes unlimited access to online courses.

Get Started
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE