# Permutations Or Combinations Of A Set Of Numbers

Mar 11, 2009
I want to output every combination of a set of numbers. These permutations must include combinations that use only a few of the numbers as well as all eight...ie.

1,

1,2

1,2,3

as well as 1,2,3,4,5,6,7,8

The macro below is from

http://www.j-walk.com/ss/excel/tips/tip46.htm

This only produces combinations using every number. I'm not sure how this macro works but hopefully someone with better know how could run with it or break it down for me!

I only need to achieve this once but am pretty sure doing it manually will cause error and or madness

Dim CurrentRowSub GetString() Dim InString As String InString = InputBox("Enter text to permute:") If Len(InString) < 2 Then Exit Sub If Len(InString) >= 8 Then MsgBox "Too many permutations!" Exit Sub Else ActiveSheet.Columns(1).Clear CurrentRow = 1 Call GetPermutation("", InString) End IfEnd SubSub GetPermutation(x As String, y As String)' The source of this algorithm is unknown Dim i As Integer, j As Integer j = Len(y) If j < 2 Then Cells(CurrentRow, 1) = x & y CurrentRow = CurrentRow + 1 Else For i = 1 To j Call GetPermutation(x + Mid(y, i, 1), _ Left(y, i - 1) + Right(y, j - i)) Next End IfEnd Sub

View 9 Replies
ADVERTISEMENT
Apr 26, 2006

You will find a VBA Code for a PRIVATE example - displaying all 6 numbers combinations out of 10 (1-10).

My code displays them (in column "A") BUT What my question to you is - does someone have a General/Universal code to handle other kinds of combinations.

To my opinion, the usage of Nested (For-Next) Loops will not be the ideal solution - so maybe by using VBA Recursion ?

View 3 Replies
View Related
Jun 2, 2007

I must first thank the forums for the amazing help in making my life just a little easier, especially member "shg" for helping set up the VBA code to help me.

So I have a sheet to develop all possible combinations of units, the letters with their associated values above, as well as the maximum number of units possible. The user inputs a top value, this is followed by some subtraction leading to a working value with a tolerance level in which the sum of the combinations needs to remain within.

What my predicament is that while the VBA code in the attached book works to develop most combinations, when I input a small number such as 405, the combinations that arise are not complete. The results are shown in the attached workbook: show combinations of H and PP, but any human can read that there should also be 2 HN in the results, as the math add's up to within the tolerance level.

View 9 Replies
View Related
Jun 27, 2009

For Ex:- If there are three Items.. A B C and forming a group of 2

then the the total no of combinations would be permut(3,1) = 6...

I need this to be shown as follows depending on the no of itmes and no of group no=2 in this case...

Permuations & Combinations are as follows:

AB

AC

BC

CA

CB

BA

View 10 Replies
View Related
Mar 1, 2013

I own a custom print shop and I am trying to generate skus based on variables..Column A will only have one item (the product code), column B will have the sizes, Column C may contain more or less colors depending on what is offered for the shirt style, Column D hopefully can contain all the combinations. All this data will be in contained within one worksheet..

Column A

Column B

Column C

Column D

G200

S

Red

[Code] .......

View 3 Replies
View Related
Oct 5, 2006

I have a number 23753.2570, to be precise.

in another column, i have 400 rows filled with different numbers, from 1.4 to 23000,7840.

I need to find out all the possible combinations of which numbers from that 400 can make up 23753.2570.

so if my number was 40,000, and i had 4 cells with 10,000 in them, the outcome will be:

"There are 4 possible combination to your value"

then paste it into a new worksheet with the results

[cell1] = 10000 + [cell2] = 10000 + [cell3] = 10000 + [cell4] = 10000

Total = 40000

i found this code, but i tested it on the example above and it said "All Combinations exhausted" which they wasn't?

Sub findsums()

'This *REQUIRES* VBAProject references to

'Microsoft Scripting Runtime

'Microsoft VBScript Regular Expressions 1.0 or higher

Const TOL As Double = 0.000001 'modify as needed

Dim c As Variant

View 9 Replies
View Related
Oct 24, 2006

I've got what I think is a pretty interesting problem, not sure if it can be solved with Excel, and if not that's fine I'll think of something better but thought I would put it up here.

I have two lists, one of adjectives, one of nouns. I want somehow to generate every possible combination of the two. Eg:

List 1:

Adj1

Adj2

Adj3

List2:

Noun1

Noun2

Noun3

gives us:

'Adj1&Noun1', 'Adj1&Noun2', 'Adj1&Noun3', 'Adj2&Noun1' ... etc etc

Is there a way to solve this problem using a macro or a pivot table?

NB ideally i would like all possible combinations to include reversals. i.e. 'Noun1&Adj1' and 'Adj1&Noun1' etc.

View 9 Replies
View Related
Mar 15, 2007

i've got 2 numeric numbers one in each cell. These numbers indicate Maximum occupancies for a hotel room...

Maximum Adults Maxmium Occ

3 5

The Occupancy table looks like this

Adults Children Infants... so taking the above numbers the table should be built up like this

Adults Children Infants

1 0 0

2 0 0

3 0 0

1 1 0

1 2 0

1 3 0

1 4 0

1 1 1

1 1 2

1 1 3

3 2 0

3 0 2

3 1 1

etc...

so basically to room can have a maxium of 3 adults and 2 children equaling the maxium room occupancy.

i'm trying to create a function that will automatically do this and workout subsquent numbers below the maxmium but i can't get anywhere near the result i am looking for...

View 8 Replies
View Related
Feb 14, 2013

I have two columns of data in a worksheet, and I need a third column that shows all of the possible combinations of the data in those two columns. For example:

A

A

AA

B

B

AB

[Code] .....

I don't need permutations so, for example, the third column should only give the combination "AB" and exclude "BA".

View 4 Replies
View Related
Aug 30, 2006

I was just wondering is it possible that a excel spreadsheet could find all the different combinations for these set of letters. Also i can only have 1 letter from each group per combination.

First Group ABCDE

Second Group FGHIJK

Third Group LMNOP

Forth Group QRST

Anyone know is this is possible to create?

View 4 Replies
View Related
Apr 23, 2008

I'm trying to figure out how to generate all combinations from a list

of N values in a column, let's say column A. In particular, I want to have all combinations of 2 values, 3 values, 4, ... up to 7 values. To give an example: Let's assume I have a list of only 3 values (1,2,3) for which I want to have all combinations of two values. In this case, the result would be 1,2; 1,3; and 2;3. The ordering of the values does not matter, i.e. duplicates should be eliminated.

View 4 Replies
View Related
May 16, 2008

I have a list for example:

apple

pear

grape

orange

i'm after some code that will create every possible combination of the list.

The examples i've found in the archives give all possible combinations of the same list in a different order

e.g apple, pear, orange, grape.......apple, orange, pear, grape.........apple, grape, orange, pear etc etc etc... THIS IS NOT WHAT I'M AFTER.

i'm after every possible combination starting right at the beginning with single words

e.g

apple

apple, pear

apple, pear, orange

apple, pear, orange, grape

pear, orange,

pear, orange, grape

orange,

orange, apple

etc etc you get the idea..... but i do not want repititions like

apple, pear.........pear, apple.

apple, orange, pear ...........pear, orange, apple.

View 9 Replies
View Related
Aug 23, 2009

I need to figure out how I can get excel to write me a list of permutations / combinations for the Group (column a) and Item (column b) columns in the example. The group designates the items that should be inlcuded in the combination / permutation in Related Items (column c). I have thousands of lines to do this with so I need to figure a way to automate it. I have started the Related Items column which is the results I need output. If it could format it like this adding in the space between the rows as it outputs the answer that would be perfect. Anyone know how to do this? I've found many answers, but none that have 2 columns with separate grouping within the column.

View 4 Replies
View Related
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
Dec 13, 2007

I have a set of numbers 1-24, what I would like to have is to list every 4 set combination of 24 numbers. I believe this is 10626 combinations. I would like the combinations to display in seperate cells.

For example: Rows 1, column 1, 2, 3, and 4 would look like 1 2 3 4. No two numbers can be the same within that row. Row 2 would have 1 2 3 5 and so on untill all possible combinations are exhausted.

How does one do something like this within excel?

Also, I would like to be able to change the value of 24, so instead of entering 24 I could say I have a set of 32 and want a list of 4 or 3 permutable combinations. I want to be to make this as custom as possible.

View 6 Replies
View Related
May 21, 2013

I have SEVEN numbers and FOUR groups.

What I would like to do is list ALL the PERMUTATIONS of those SEVEN numbers in FOUR groups.

So basically, the groups would look like...

7000

4210

4201

4111

4003

3400

3310

3301

3301

3220

[code].....

...and EACH group would add up to SEVEN etc.

I think there are a total of 840 different PERMUTATIONS available, from anything from 0 numbers in a group to 7 numbers in a group.

I need ALL these groups listed individually .Is there some code that will do this?

View 4 Replies
View Related
Mar 1, 2014

with this problem (which I've been trying for weeks to solve).

I have a column ("C") of 640 numbers, which I need to randomly sort and place into the adjoining (ten thousand) columns. I then need to correlate each of these 10k columns with column "B".

The problem I have with the attached (which works for small numbers), is that when I increase the table size to 640 rows and anything more than a dozen or so columns, it takes forever to calculate and Excel often crashes.

random ver 1.04 280214.xlsx

View 1 Replies
View Related
Jan 4, 2007

I want to do all columns combination that is (C=4^N/(3*N+1); N=4) for numbers 1,2,3,4.

as like this,

1111....2222....3333.....4444.....

1111....2222....3333.....4444.....

1111....2222....3333.....4444.....

1234....1234....1234.....1234....

View 9 Replies
View Related
Feb 16, 2014

I have stamps of 2 denominations: .32 and .33

I want to know how I would write/format a spreadsheet that would tell me what are all the possible outcomes between the two numbers and what the components of these two numbers would be.

For example.

(8 * .32) + (3 * .33) = 3.55

So somewhere I would be able to see that in order to achieve 3.55 I need (8) .32 stamps and (3).33 stamps

View 6 Replies
View Related
Aug 22, 2012

Lets say I have 10 numbers (1,2,4,5,6,8,9,12,19,13). Now, I am trying to get a list of all possible combinations (single digit, two digit, 3 digit, 4 digit... 13 digits).

Assume that the 10 numbers (this is a variable, it can sometimes be 9 or 11 etc) are in column A and I want all the possible combinations in column B.

View 3 Replies
View Related
Mar 15, 2013

Is there a way in Excel to identifying certain combinations of numbers? If tried every IF statement I know.

I have an Excel with two columns of numbers and I need to identify their combinations.

18

18

18

19

18

19

18

20

18

18

18

20

The combinations 18 and 18 would return a "LOW" message in the third column, the combination 18 and 19 would return a message "HIGH" etc.

View 2 Replies
View Related
Jan 18, 2013

getting All permutation and combinations of 4 letters and 4 numbers?

View 2 Replies
View Related
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
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
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
Aug 28, 2012

I attached partial file so you can see what i mean. I pasted only a few combinations cause the file was to big and i wasnt able to attach it...there are 142506 combinations . But you can see what i mean. A VBA or a macro on the worksheet will do the job?

View 9 Replies
View Related
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
Feb 24, 2009

I'm sure I should be able to do this but my brain is out of gear today!

Can someone tell me how I can create a list of every 5 letter permutation using only letters (A-D).

I also need to do the same for 4 letter and 3 letter, again using A-D.

Would prefer to do it without VB but if this is the only way......

View 6 Replies
View Related
Jul 23, 2007

I am creating a class of permutations. The problem is that there are many ways to describe what a permutation does. for example.

Dim aPerm As New clsPermutation

Dim bPerm As New clsPermutation

aPerm.DoesTranspose(2,3) : Rem aPerm.Action({a,b,c}) = {a,c,b}

MsgBox (aPerm Is aPerm.Inverse)

I want the message box to return True. But since aPerm and aPerm.Inverse are different instances of clsPermutaion, it returns False. (.Inverse is a read-only property that returns a clsPermutation.). What I would like to do is have a collection, myPermutations. When I specify the action that aPerm does, I would like

1) if nothing in myPermutations does the same thing as aPerm, then add aPerm to myPermutations.

2) if something (xPerm) does the same thing as aPerm, then

Set aPerm=xPerm

so that "xPerm Is aPerm" returns True.

The problem I have is that in the class module,.....................

View 8 Replies
View Related
Jul 23, 2012

I need VB codes for an activity in excel. Im trying to create an example file for the suppliers to enter keywords (Search keyword) where it should generate different formats of the search (WORDS) and its case sensitive. Suppliers would be typing the keywords in A2 and it should generate the Permutations horizontally against the keyword (B2 C2 D2 E2 etc). Let me know if you need more clarifications.

Example given below and also attached the file for your reference.

New Keyword – Singular Proper Case

Singular-PR

Singular-LC

Singular-UC

[Code]....

View 4 Replies
View Related
Apr 22, 2014

Simple permutation problem that will most likely requires nested loops. I need a list of every possible permutation of words in a row broken out by column. Order does matter and not every row has the same number of words in it. I have attached a sample worksheet with a sample table and then the needed output format.

View 5 Replies
View Related
Aug 31, 2007

Is there any way to determine all possible unique combinations (or sets) of any 3 strings in a table? I need to make a master set list from a list of strings which could be over 100 elements long.

Once I have a result set, I need to get rid of any groups which have a value contained in another group.

Example:

cells A1 through B100 contain:

Item1 45

Item2 50

Item3 98

...

Item100 75

Result set would contain groups of any 3 item combinations where the corresponding "B" value is within a certain range (between 40 and 80), while also trying to create sets with the lowest possible sum (placed in the 4th column) of the 3 numbers.

One possible combination would be:

Item1 Item2 Item100 170

etc...

I need to list all possible combinations, without re-using an element from a previous (lower sum) combination that matched the criteria.

View 13 Replies
View Related
Nov 26, 2013

I have trying to assign cards to respondents to view. I have 16 cards total which are grouped in sets of 4. Each set (of 4) is given a color, (red, green, blue, and yellow). Each color has a 4 different shades (S1, S2, S3, S4) so I have the following 16 cards:

1: Red_S1

2: Red_S2

3: Red_S3

4: Red_S4

5: Green_S1

6: Green_S2

7: Green_S3

8: Green_S4

9: Blue_S1

10: Blue_S2

11: Blue_S3

12: Blue_S4

13: Yellow_S1

14: Yellow_S2

15: Yellow_S3

16: Yellow_S4

I will have a total of 10 respondents giving their opinions who will each see 5 of the cards, randomly. Each respondents must see each color at least once (given that 5th card will be a repeated color). Each card must be tested 3 times (given that the 2 additional cards can be any of the cards).

If there a function or cell code that I can write so that I can allocate the card #s (1-16) across my 10 respondents?

View 1 Replies
View Related
Nov 15, 2006

This formula will only do 7 permutations, I need it to do 8. Is there some way to do that? Thanks in advance.

=IF(AND(A10,B10),B10,(IF(AND(A10,B11),B11,(IF(AND(A10,B12),B12,(IF(AND(A10,B13),B13,(IF(AND(A10,B14),B14,(IF(AND(A10,B15 ),B15,(IF(AND(A10,B16),B16)))))))))))))

View 9 Replies
View Related
Mar 21, 2007

A1 has some characters

this code will generate all possible words, that can be made using all characters

system

permutate and checkspelling: if OK then write to column B

example

A1: iftrs

results: first frits rifts

Option Explicit

Dim CurrentRow

Const col = 2

Sub correctly_spelled_permutations()

Dim InString As String

Dim CalcSet As Integer

InString = Range("A1")

If Len(InString) < 2 Then Exit Sub

With Application

.ScreenUpdating = False

CalcSet = .Calculation

.Calculation = xlCalculationManual

.EnableCancelKey = xlErrorHandler

.StatusBar = "searching valid combination"....................

View 9 Replies
View Related
Jan 17, 2007

I need to work out a formula which will work out 48 permutations.

There are three headings: Impact, Probability, Risk Category:

ImpactProbabilityRisk Category

VHM = 1

VHH = 1

VHVH = 1

HH = 1

HVH = 1

VHL = 2

HM = 2

MH = 2

MVH = 2

HL = 3

LVH = 3

MM = 4

LH = 4

ML = 5

LL = 5

LM = 5

The formula I need is: if Impact is "L" and probability is "M", then return answer = "5".

The thing is - there are 48 possible answers - and im not sure what formula you could type into 1 cell - which would answer this.

View 9 Replies
View Related
Jul 23, 2009

THREAD SOLVED...and very well too

I need to find all 3, 4 and 5 letter permutations based on 5-8 musical notes as input. FYI the permutations will be chords. i.e. C, D, E, F, Ab, Bb, B as an input musical scale input would yield

All three note combos :

C,D,E

C,D,F

C, D, Ab

....

all 4 note combos

C,D,E,F

C,D,E,Ab

all 5 note combos

C,D,E,F,Ab

C,D,E,F,Bb

...

I'd like to structure the sheet as having 12 columns (1 for each note within an octave) with the combo placed under the right column i.e. C goes in 1, Eb in 4.

View 5 Replies
View Related
Aug 3, 2013

The problem is that there are two sets of up to thirty three (33) numbers each, numbers are NOT duplicated in each set. The goal is to make an excel file that combines all the numbers from both sets in combinations of five numbers each. There should be a maximum of three numbers chosen from each set, in order to limit the total numbers chosen.

I would like to see the five set listed on the left of the excel file and the intended numbers sets on the right of the same sheet. This would permit me to see at a glance if there are any missing numbers.

For instance, from the first set of numbers 1, 2, 3 are chosen and from the second set numbers 30, 31 are chosen. Therefore the table would show as:

1, 2, 3, 30, 31.

1, 2, 3, 30, 32.

1, 2, 3, 30, 33, ...

1, 2, 4, 30, 31

1, 2, 4, 30, 32, etc.

This would continue until all of the numbers are used with each number listed in a separate excel box but next to each other without the comma.

each numbered set of five numbers does not have any numbers repeated (i.e. the number three appears only once per set of five numbers).

If further clarification is needed please go to: [URL] ..... where n=33 and r=3.

Repetitions are NOT allowed and order is important. This will calculate a total of 32,736 permutations to choose from. NOTE: this calculates the first set of three numbers only and is used as an example of the intended output, however the second set of numbers is not included there which I would like to have included in the excel file.

View 2 Replies
View Related
Jan 8, 2008

I have several columns of info on my worksheet. I want to output a list of all possible permutations from this worksheet (not the number of permutations but the actual permutations themselves).

View 9 Replies
View Related
Oct 6, 2009

I have a few calculations I cannot solve for life of me. I know you guys can figure this out. In the example attached you will see the list of the numbers that were drawn (More numbers will be added as time continues).

I was able to use " countif" to find the total number of time each number was drawn.

My delima, I need:

1. Count the number of times each number was drawn by person.

John starting in cell J7

Mary starting in cell J11

2. I need to type in 4 digits in cell J19, and find all possible combinations from the list of numbers drawn

List what combination of numbers that was drawn including the date it was drawn by person starting at cell I20 using the format as shown

View 9 Replies
View Related