List Possible Combinations With Conditions/Constraints

Aug 9, 2008

I am trying to write VBA code that will list all possible combinations of data contained in a worksheet based on a few conditions. I have a list that varies in length. In the first column is a list of numbers denoting a particular course number. In the second column is a list of numbers denoting the specific class number for the course. As an example, I have a 6 courses. The first course has 1 class, the second course has 3 classes, the third has 11, the fourth has 3, the fifth has 7, and the sixth has 6. Thus, my columns look like this:

A B
1 1
2 1
2 2
2 3
3 1
3 2
3 3
3 4
3 5
...

I need to list out all possible combinations of classes given that I must have all six courses. EXAMPLE (Course, Class):.......................

View 2 Replies


ADVERTISEMENT

List Of All Possible Combinations Of The Integers

Jan 19, 2009

A user would input 4 integers.
How can I generate a list of all possible combinations of the integers?

View 9 Replies View Related

Generating A Combinations List

Feb 5, 2007

I have ten numbers (i.e. 1,2,3,4,5,6,7,8,9,10) and I'm trying to figure out how to make Excel generate a list of all the combinations possible for these 10 numbers. I'm trying to avoid using a VBA script. Is it possible to accomplish this task only using a function formula? This way I will have the ability to tweak the formula and test different qauntities of numbers (i.e. 1,2,3,4,5,6,7 or 44,56,77,33). Is getting a list of all possible combinations to output on a single column with commas between numbers possible with a formula?

View 5 Replies View Related

List Character Combinations

Feb 3, 2008

I know the COMBIN function, i want to list all combinations from a given set of cells (numbers), each item of the combination - in separate cell. Example:

1 2 3 4 5 a b 6 ....

after combin (x,3) i want to list me this: 123........but each number must be in separate cell (1-one cell, 2 -one cell, 3 -one cell)

124
125
12a
12b
126
234
etc

View 3 Replies View Related

List Out 6_49 Lottery Combinations Only Those With The Sum Of 100

Sep 3, 2009

I need a vba that can generate all set of 6 number combinations with the total sum of 100.
For example: 3, 6, 4, 15, 35, 37 = 100.

View 9 Replies View Related

Output All Combinations Of List Of Given Numbers With Repetition?

May 10, 2014

I need to be able to create a list of all combinations (where position doesn't matter, therefore its not a permutation) of a list of numbers with repetition enabled. I need to provide anywhere up to at least 10-15 numbers if possible and they are integers normally between 1 and 72. Example: (for numbers 1, 2, 3)

VB:
1
2
3

[Code]....

View 5 Replies View Related

Produce A List Of Combinations From A Range Of Numbers?

May 9, 2014

Am looking for an easy way to produce a list of combinations (maximum 6 numbers) from a range of numbers listed in 6 different columns:

Example
Column A contains : 1,2,3
Column B contains : 7,8,11,15
Column C contains : 12,16,18,19
Column D contains : 17,30,31
Column E contains : 30,31,32,33,34
Column F contains : 37,39,40
The rules are:

In each combination of 6 numbers, numbers should always be taken from ALL 6 columns. In each combination, numbers cannot repeat.

show me a formula to arrive the results.

View 3 Replies View Related

Generate List Of Combinations - 20 Questions With 3 Answers

Feb 26, 2011

How to generate a list of numbers of this function in Excel: COMBIN(20,3), but i want the combinations of numbers. I want to create a list of 20 questions and every question has 3 answer. I think that are 1140 combinations.

View 9 Replies View Related

Finding All Unique Combinations Of List Of Words?

Dec 6, 2011

I have 15 different words that I want to order in as many ways as possible, but only 3 at a time.

So let's say the words are:

cat
dog
elf
clown
monkey
rock
bananas
(etc)

then I want a list that has all of these possible combinations:

cat dog elf
cat dog clown
cat dog monkey
cat dog rock
cat dog bananas
cat elf clown
cat elf monkey
(etc)

until all are listed. I understand there is a huge number, I don't mind having a couple of thousand as long as they are all genuinely unique.

I CAN have elf dog cat, elf cat dog, dog elf cat later on ... it's just the order that needs to be unique not the words in the phrase.

View 9 Replies View Related

List All Possible Outcome Combinations For 50 Football Matches

Dec 20, 2012

An example will be as follows. List all possible outcomes for 3 matches. That will be 27 possible outcomes.

I would like results for my request of 50 matches to be displayed as follows.

HHH
HHD
HHA
HDH
HDD
HDA

[Code] ...........

Where:
H=HOME
D=DRAW
A=AWAY

Is there a way i can have the possible outcomes listed as above for the outcomes of 50 football matches? I do know that the outcomes will be hundreds of millions if not billions.

View 9 Replies View Related

List All The Combinations Of A Group Of Cells Containing Letters, But Not Numbers

Feb 9, 2008

I have 7 cells containing strings but not numbers on a row.

Now I want to list out all the combinations of drawing out 3 cells out of these 7 cells while the remaining cells that haven't been drawn out could also be listed out one column next to the drawn cells.

For example, I got 7 cells like this.

A B C D E F G (each letter in ONE cell)

And I want to list out all the combinations like this:

ABC DEFG
ABD CEFG
ABE CDFG
ABF CDEG

etc.

For more details, please refer to the attached sample (an .xls file being zipped).

View 14 Replies View Related

All Combinations Of Breeding Outcome From List Of Male And Female

Jul 17, 2012

I breed snakes as a hobby and have around 30 females and 15 males Different morphs of Royal Python

There are a few things i am trying to do and will start of by asking the most simple one

If i have a column of females and a column of males can a formula be made to show all the possible pairings between male and female?

View 8 Replies View Related

List All Possible Combinations Of Single Range In Two Separate Columns

Apr 25, 2013

I have a single rangle of numbers in Column A, and in Columns B and C and want to list all possible combinations of these numbers. If there is a possible solution without using a Macro I would perfer that, but if not I'll take what I can get.

Example Desired Output:

Column A:
Column B:
Column C

0001
0001
0001

[Code] ......

View 7 Replies View Related

How To Generate All Combinations Of Ten Values List / Picking In 4 Each Combination

Oct 20, 2013

I have a list in excel with ten values, but I need to get all combinations possible, picking 4 of 10 each combination. This must be as a combination and no permutation or variations either, I mean, values can't be repeated in a single combination.

I'm struggling to do this in a Macro but I don't get it.

View 9 Replies View Related

Number Combinations :: 25 Random Numbers (5 Digit Combinations)

Dec 15, 2008

I have 25 random numbers and I would like to get a possible 5 digit combinations of these numbers. Can anybody help me with the possible formula?

View 9 Replies View Related

Generating List Of Combinations With Both Unique / Independent And Dependent Input

Mar 26, 2012

I coordinate access requests for several contracts, and I have to list the approved accesses in a list where each line represents one person and one contract. For each access request, there will be an arbitrary number of persons obtaining access to an arbitrary number of contracts.

The input would then be as follows: Joe A and Jill B request access to contracts 1001, 1002 and 1003 ->

Joe A   1001
Joe A   1002
Joe A   1003
Jill B     1001
Jill B     1002
Jill B     1003

To automate this task, I have made a simple macro for generating a combination list of all persons having obtained access to a selection of contracts.

My macro worked well when I only wanted to list unique and independent list items, but now I have been asked to include each person's email address. How I can change my code so that only one email address is copied into my list for each person?

Code:
Sub AccessList()
Sheets("requests").Select
Dim rng As Range, c As Range
Dim rng1 As Range, c1 As Range
Dim rng2 As Range, c2 As Range

[Code]....

View 1 Replies View Related

Number Combinations :: Between 7 To 15 (6 Digit Combinations)

Jun 30, 2008

What I am looking for is to select between 7 and 15 numbers in total, I want all the possible 6 digit combinations for this.

EG: if I choose 2,9,11,13,15,17&26, it would look something like this

2,9,11,13,15,17
2,9,11,13,15,26
9,11,13,15,17,26
And so on.

If I chose more numbers (10) 1,2,3,4,3,6,7,8,9,10 it would start something like this

1,2,3,4,5,6
1,2,3,4,5,7
1,2,3,4,5,8
1,2,3,4,5,9
1,2,3,4,5,10
And so on.

Please remenber I would like to be able to secelt between 7 and 15 number and be given all the possible combinations.

I would like it to be in one sheet but if that can not be done on as many as it takes.

It would be good if I could just type the required number into A1,B1,C1 and so on and they just gave the combinations required.

View 9 Replies View Related

How To Set Constraints As Integer In Solver

Mar 26, 2012

I want to set constraints as an integer in solver. But I don't know how to do it.

View 5 Replies View Related

Solver Add In But Unfortunately It Cant Take More Then 200 Constraints And Hence Gives An Error

Mar 9, 2008

I was able to find a solution using the excel solver add in but unfortunately it cant take more then 200 constraints and hence gives an error. I am unable to find a different approach to this problem.......

View 9 Replies View Related

Maximize Value Within Several Defined Sets Of Constraints?

Jul 22, 2014

I am trying to maximize fantasy football points based on projected points scored, salary and positional limits.

I have multiple sheets dedicated one per position (i.e. sheet named "QB", sheet named "RB", etc.). The relevant columns are: Name; FPT (projected points); Cost

NOTE: I have combined all the data into "sheet1" if necessary.

I need a VBA program or Solver to diagnose the largest possible combination of projected points based on the following constraints:

Constraint 1: Total value must be less-than or equal to $1,000,000
Constraint 2: Total players used must be equal to 10
Constraint 3-?: Of the 10, 2 must be QB, 2 must be RB, 2 must be WR, 1 must be TE, 1 must be DEF.....the other 2 can be RB, WR or TE

Obviously, each player can only be used once and I'm not sure if Solver can abide by that constraint, nor do I know how to apply the others. I'm thinking VBA could work, but I'm not familiar enough with the language to hammer out the code.

I have a small macro that downloads some data from a website and tweaks it Daily fantasy cheatsheet.zipa little, but I hope that won't affect any computations involving those values.

View 4 Replies View Related

Solver With Constraints For Inventory Levels

Nov 9, 2008

I have a maximization problem. I have to maximize Kitchen Sets given available materials. I have 11 different Kitchen Sets and every set requires different material.I have attached an excel spreadsheet for making the problem more clear.

I have T1;T2;T3;T4;M1;M2;M3;M4;O1;O2;O3;O4;Z1;Z2;Z3;Z4 different materials. Every kitchen sets requires one material from T which is fixed (for example T2), one material from M which is fixed again (for example M3), one material from O which is fixed (for example O1) and one material from Z, which is fixed (for example Z4).To complete a set the sum of all materials used should equal to 4. I have some constrains T1+T2+T3+T4<=2 it is the same for M1+M2+M3+M4<=2, O1+O2+O3+O4<=2 and Z1+Z2+Z3+Z4<=2.

So basically my goal is to say which materials should I keep in stock in order to maximize the Kitchen Sets.

View 9 Replies View Related

Calculate Time Spans With Constraints

Dec 20, 2006

I need to calculate time period worked between 19h00 and 24h00 in following exemples,

start time 08h00 and End time 20h00, would be 1 hour

start time 20h00 and End time 08h00, would be 4 hours

I can't get IF fonction to work with times..

View 9 Replies View Related

Calculate Time Difference With Constraints

Sep 30, 2007

i've two time constraints with 22:00~6:00 and 6:00~22:00. i'll apply time span to two constraints,calculate time covering on two constraints. it will start at anywhere of 00:00~24:00, time span will be 00:00 ~24:00. i add some formula, a3=start time, b3=end time. time constraints with 22:00~06:00:

=IF(AND(A3<=22/24,B3>=6/24,A3>B3),8/24,IF(AND(A3<6/24,OR(B3>22/24,AND(B3>0,B3<6/24,A3>B3))),IF(B3>=22/24,(B3-22/24)+A3-6/24,(B3+1-22/24)+MOD(6/24-A3,1)),IF(AND(OR(A3>=22/24,A3<B3,A3=0),B3<=6/24),IF(OR(A3=0,A3=1),B3,IF(A3>B3,B3+1-A3,B3-A3)),IF(AND(A3<=22/24,B3<=6/24),MOD(B3-22/24,1),IF(AND(A3>=6/24,B3<=22/24,A3<B3),0,IF(AND(A3>=22/24,B3>=6/24),(1-A3)+6/24,IF(AND(A3<=22/24,B3>22/24),B3-22/24,6/24-A3)))))))

time constraints with 06:00~22:00. :C$3=start time,$B4=end time...............................

View 5 Replies View Related

IF Statement With List And Conditions

Oct 5, 2009

I am trying to create an IF statement that will double the unit value of a certain box (J15) if the number of I15 is less than or equal to 160 AND either "A,B,C, or D" is selected from a list in H15. If it does not qualify with both, it would just be the single value of J15, not doubled.

So far I can only get it to work with one item from the list in the IF statement:

=IF(AND(H15="A",I15<=160),J15*2,J15)

However, I don't know how to add in the other list options B-D and get it to work.

Basically I need these four statements combined into one:

=IF(AND(H15="A",I15<=160),J15*2,J15)
=IF(AND(H15="B",I15<=160),J15*2,J15)
=IF(AND(H15="C",I15<=160),J15*2,J15)
=IF(AND(H15="D",I15<=160),J15*2,J15)

View 4 Replies View Related

Vehicle Routing With Two Dimensional Loading Constraints?

Jul 9, 2014

Whenever or not I should use VBA or ordinary formulas in Excel in order to do Vehicle Routing With Two-Dimensional Loading Constraints case. What I need is when I have coordinates of four bottom-right corners:

X Y
24 0
30 0
10 24
8 38

2L-CVRP, what I want to know is: In reality I have set of 200 coordinates (x,y) and I need to search for lowest y (its (24,0) and (30,0)) then I choose one with maximum x ((30,0) in this case). Then if for some reason item cannot be packed there, then I shall go to next lowest y, which is 24 giving coordinate (10,24), if I cannot use this corner, I search for next lowest y with as high x as possible, etc. BTW I use Evolver in order to find satisfying solution.

View 3 Replies View Related

Optimal Combination Model With Constraints & Variables

Aug 11, 2008

I need to optimize an Excel model with 5 variables each of which cam be any value from 1 to 20. The solution space, therefore, contains 3,200,000 combinations. I have a software(Palisade Risk) which can give out a random sampling of this solution space. I need to know how many combinations can be considered as a representative sample of this solution space - e.g. can it be 1%?.

View 5 Replies View Related

Custom Validation List With Conditions

Oct 17, 2013

I am trying to create a validation list, with its source data being filtered from another sheet of the same workbook. I also need to validate the drop list against some conditions in the source table.

Here is an example of the data am trying to create`(I want to create the droplist for the column Contract):

"Contract Definition" Sheet or Table:
-----------------------------------
Company Contract Service Report End date: Status
--------- --------- -------------- --------- ------
CompanyA ContractA1 Service 1 31/12/2014 Active
CompanyA ContractA2 Service 2 31/12/2014 Active
CompanyB ContractB1 Service 1 31/12/2014 Inactive

"Service Reports" Sheet or Table:
--------------------------------
Company Contract Service Report
--------- --------- ----------------
CompanyA ContractA1 Service 1
CompanyA ContractA1 Service 2
CompanyA ContractA2 Service 3

In the Service Reports Sheet, i want to create a list for each Contract, based on the specified Company and on the Status field, as defined in the Contract definition table.

View 8 Replies View Related

Extract From List Based On Conditions

Jul 27, 2007

I want to know how I can make Excel look at a list of information on one worksheet, extract certain items from the list, and place it on another workshet

For example, the following list would be imported from an inventory program as text and pasted on worksheet 1:

P+green beans
d-2.99
c=2
cd+10258

p+cherry tomatoes
d-3.99
c=1
cd+11204

p+red apples
d-.69/lb
c=3
cd+08902

p+tissue
d-1.99
c-2
cd+20023

How can I have Excel take this information and create a list like the following on worksheet 2? ...

View 9 Replies View Related

Creating Proper Constraints To Create Solver Model?

Apr 11, 2014

I want to take a shot at this spreadsheet exercise. Setup similar spreadsheet models.

Excel Exercise.xlsx

View 3 Replies View Related

Pop Up Textbox In Data Validation In Protected Spreadsheet With Certain Constraints

Feb 17, 2009

This is the code where you click on a cell that has been input from Data Validation, it gets a pop up textbox showing the text input. One drawback I see here is that when the sheet is protected, one needs to tick 'Edit Objects' in 'Allow all users of this worksheet to:' window to get the textbox to pop-up otherwise no textbox is shown.

So this leads to the accessed user to have the ability of deleting and moving the textbox in the protected spreadsheet. Do you have any idea to Protect the sheet, Get the textbox to pop up,
Disallow user to delete/move the textbox.

View 10 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved