Permutations Or Combinations Of A Set Of Numbers
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.jwalk.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 Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Combinations From Set Of Numbers
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 Replies!
View Related
All Combinations/Permutations
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 Replies!
View Related
Combinations (not Permutations)
You will find a VBA Code for a PRIVATE example  displaying all 6 numbers combinations out of 10 (110). 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 (ForNext) Loops will not be the ideal solution  so maybe by using VBA Recursion ?
View Replies!
View Related
Generating Permutations And Combinations
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 Replies!
View Related
Combinations/Permutations Of Values
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 Replies!
View Related
All Combinations Or Permutations From 2 Lists
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 Replies!
View Related
Matrix Combinations And Permutations
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 Replies!
View Related
Unique Combinations/Permutations Of Words
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 Replies!
View Related
Combinations/Permutations Of Words/Text
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 Replies!
View Related
Permutations/Combinations For Column Relative To Groups In Another
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 Replies!
View Related
Permutations For Defined Numbers
I have a set of numbers 124, 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 Replies!
View Related
Getting Excel To Calculate All Combinations Of A Set Of Data
I've found myself having to use a spreadsheet to calculate some acoustics problems, (problematic frequencies, called modes, in orders ranging from 0,1,2,3....). Ideally what I want to be able to do is simply to get Excell to calculate all the possible combinations of these modes. A combination could be: 000 011 101 110 111 012 143 etc. Obviously this is a nightmare to input by hand, is there any kind of function I can use to help with this process? Also these frequencies only need to be calculated up to a certain point, so is it possible I could also instruct the cell to only display a value if it is less than a value in another cell?
View Replies!
View Related
Match Number Combinations To Equal Set Value
I am looking to do the following: Say I enter the following values into a spreadsheet: 5 7 4 24 32 Is there a way to enter the value "36" and have Excel find the two values of 32 and 4 which add up to 36 for me? I will obviously be dealing with much larger spreadsheets and would like to somehow save time by not having to hunt down possible combinations of the total value I'm searching for.
View Replies!
View Related
List All The Combinations Of A Group Of Cells Containing Letters, But Not Numbers
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 Replies!
View Related
Number Combinations :: Between 7 To 15 (6 Digit Combinations)
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 Replies!
View Related
Averaging A Set Of Numbers
I need to average out a set of numbers but the amount of numbers inputed will vary, these number can also be either positive numbers or negative numbers but will never be 0. How do i do this and I would like this in formula terms? Say cells a3,b3,c3 were the numbers that I want averaged but there is not always a number inputted in each of these cells. I want the answer to show up in cell a4
View Replies!
View Related
Set Of Numbers From Cells A2 To F6
*ABCDEFGHIJK1n1n2n3n4n5n61 to 910 to 1920 to 2930 to 3940 to 49225182130312,5182130,310330343843474800030,34,3843,47,48419242934384801924,2934,3848525122224422,51222,240426911243136499112431,3649 I have a set of numbers from cells A2 to F6. I want numbers from each row to be separated in groups from cells G2 to K6 For example: Numbers in row 2 are separated first from 1 to 9 (cell G2) with comas, giving an answer of (2,5), then 10 to 19, then 20 to 29 and like that successively with each corresponding row. Any formula to do this automatically?..
View Replies!
View Related
Formula: Insert A Zero Between A Set Of Numbers
I need a formula to take a 6Digit Number and change it into a 7Digit Number. Probably a if statement would better because i have 7 digit numbers in the same column with 6digit numbers. I need to insert zero between the 4th digit and the 5th digit of a 6digit number. For Example:............
View Replies!
View Related
Add Numbers To A Set Total
I’m creating a spreadsheet (attached) that needs to divide up aisles in a warehouse into sections, so that each section has an equal (or as close as possible) amount of work. The “To, From” columns are aisle ranges and the “Moves” column is the amount of work in that range. I need a macro/formula to add up numbers under “Moves”, but only up to the point where the sum is as close as possible to the number in K2 (variable). It also needs to look at the number under “sections required” (I2) (variable) and then split the aisle ranges into that many sections.
View Replies!
View Related
Good, Average, Fair, Poor: Automatically Use One Of The Numbers That I Have Set And Add It To Total
In creating a chart that calculates home prices I need to be able to just enter "Good", "Fixer", "Remodeled", etc...and have that automatically use one of the numbers that I have set and add it to my total. I have created a box with variables for "Good", "Fixer", "Remodeled", etc...How do I make it so I can put any of those variables into the condition of the house row and make it add the dollar amount specified into the totals box? This is the very simple code that I am using to find the totals so far: =SUM(C5:J5) Here's what I need: Also, the plug in values in the first picture WILL CHANGE and I don't want to have to change the formula. So I need something to point to those cells.
View Replies!
View Related
Conditional Summation Formula: Set SUM Code To Grab All Numbers Next To Codes
I am creating a billing sheet that has a column where charges are coded (to determine what they are for) and I want a "Grand Total" for everything followed by a breakdown "Total" for things with the same code. Is there a way to set SUM code to grab all numbers next to codes?? (EX. Column A is Billing Code, column B is Cost in $$; I want a total at the bottom that takes the different amounts in column B based on the various information/codes from column A).
View Replies!
View Related
Permutations And Combinationns
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 (AD). I also need to do the same for 4 letter and 3 letter, again using AD. Would prefer to do it without VB but if this is the only way......
View Replies!
View Related
Class Of Permutations
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 readonly 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 Replies!
View Related
IF  FORMULA RESTRICTED TO 7 PERMUTATIONS
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 Replies!
View Related
Permutations Checkspelling Speed
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 Replies!
View Related
Formula Which Will Work Out 48 Permutations
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 Replies!
View Related
Permutations Of A List (groups Of 3)
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 reusing an element from a previous (lower sum) combination that matched the criteria.
View Replies!
View Related
Permutations Function For Musical Notes
THREAD SOLVED...and very well too I need to find all 3, 4 and 5 letter permutations based on 58 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 Replies!
View Related
Count With Multiple Criteria And Find Permutations
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 Replies!
View Related
Set A Range And Find All Numbers In That Range
I have the following columns and rows that I need to MOD by 15. Once they have been MOD, I need to set a range and find all numbers in that range of say, eg; all number between 9.6 and 9.8. Really hope someone can help, have been working on it day and night. I can do them one at a time, but is very time consuming. Is there a formula that will do the whole worksheet in record time? 2/18/1975, 156.7519, 175.8083, 235.4675, 253.765 4/11/1973, 237,7642, 346.5342, 113.3145, 321.435 5/12/1985, 342.7625, 123.4233, 253.4321, 357.234 etc. etc. etc.
View Replies!
View Related
Combinations Using Formula
I've attached an Excel..Its a small 4X4 matrix with numbers filled inside...Next to the right is a Combinations Column..I want to know how can i use just forumla to optain 4digit combination based on the 4X4 matrix. Rule is only numbers from the 4X4 be used.
View Replies!
View Related
Given Number Combinations
I want to have a sheet with all the previous drawings for the florida lottery. Then I want excel to ask me how many numbers do I wish to play. Then when I say 12 (for example) it would pick 12 numbers from 1 to 53 and match all possible comibinations of thoses numbers to the the previous drawings and tell me wich 12 number combination would have won more times then others. So it would need to compare each possible 12 nmber combination to each other and see wich one won more 6outof6, 5outof5, 4outof4, and 3outof3. Then give me the one with the highest winings
View Replies!
View Related
How To Calculate Combinations ?
I want to see how much combinations are possible when i got 6 numbers..  got this numbers 123456  want to calculate how much combinations of ( 2 ) numbers possible  want to calculate how much of ( 3 ) numbers possible  want to calculate how much of ( 4 ) numbers possible  want to calculate how much of ( 5) numbers possible  never 2 same numbers together (22) or (223) or (2234) may not be in list  How can i make the result visible in kind of list ? I would be nice if somebody knows a good solution..
View Replies!
View Related
Figure Out The Combinations Available
i'm doing a process and in column N, i would need to have the following combinations: A1 A2 C1 C2 H1 I1 so basically, a formula that would figure out the combinations available! can this be done using a formula? ******** ******************** ************************************************************************>Microsoft Excel  Cost CalculatorV5Try.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutK39= KLMN39A12 40B 41C12 42D 43E 44F 45G 46H11 47I11 48J Cost Calculator [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
View Replies!
View Related
Median Of All Combinations
I have 5 numbers (can be with decimals) in column A (A1:A5). For these numbers, I want all combinations as well as the median of these combinations. I'm pretty far already. However, the median seems to be calculated wrong.
View Replies!
View Related
Display The Combinations
I have a list of numbers from 1 to 20 form a2 to a21, what I would like to do if possible if figure out many combinations of 5 there are for these numbers, and then second if possible display the combinations. E.g. say there was a total 250000 combinations then show those 250000
View Replies!
View Related
Creating All Possible Combinations
I am trying to create a combination list. I have 48 items and I want to create all possible comibinations of 3 items from this group of 48 items. I did something similar to create all possible combinations of 2 items from the group of 48 items in the attached spreadsheet. This was done manually and was time consumning. I want to do something similar for all possible combinations of 3 items from this group of 48 items. All my 48 items are listed in rows 4 and 5. I want to enter the number "1" in each column to indicate my selection. Right now I have 2 ones per row. But I want to do 3 ones per row.
View Replies!
View Related
Generate All Possible Combinations
Is there an Excel Addin that will let me figure out the total number of combinations for any size number pool I specify (39,49, 53) and with any combination quantity I specify (3, 4, 5, 6)? And most important is that all these combinations be displayed in a column separated by commas. Is a VBA script designed to handle something this complex.
View Replies!
View Related
Find Possible Combinations
I need to find possible combinations for the following example: a*x + b*y + c*z = t p = 10 Where a = 0 b = 5 c = 10 t = 30 x + y + z has to = p a, b, c, t, and p would be variables that I would specify. The numbers above are just examples. What I would need to see as output would be: 7a, 0b, 3c 4a, 6b, 0c 5a, 4b, 1c etc...
View Replies!
View Related
Number Combinations
Here is what my table kind of looks like A B 4 0.25 7 0.3 2 0.1 3 0.15 4 0.30 1 0.4 8 0.05 2 0.1 Now there is the number and the number(A) that it value it represents (B). This is what I want. I want to see all possible number combinations between the upper and lower A columns listed veritcally under the whole set, with out duplicating any numbers and with the upper numbers only being in the 1st position and the lower numbers being in the second position Ex( 41, 48, 42, 74, 71, ect, ect (listed vertically). Now in column B under, right beside each possible combination I want to see the result of the two values multiplied (ex. beside 41 I would like to see 0.1) So below that mess listed above I want to see something like this A B 41 0.1 48 0.0125 ect ect ect I want to be able to swith the values around and have it automatically make the correction.
View Replies!
View Related
How To Count All Possible Combinations
I have a table: VOICEDATAIPADSLCustomer 1YESCustomer 2YESYESCustomer 3YESYESCustomer 4YESYES And i need to count all possible combinatins. Result could be as: Custome who use Voice also use combination:1VOICE ADSLCustome who use Data also use combination:1DATAIP2DATAADSLCustome who use IP also use combination:1IPDATACustome who use ADSL also use combination:1ADSLDATA
View Replies!
View Related
Permutation/Combinations
Can this be done in Excel? Using the digits 0,1,2,3,4,5,6,7,8,9 create all the permutations/combinations with limits on some of the digits. The digits 0,7,8,9 can only be used a max of 3 times in any one permutation/combination, digits 2,3,4,5,6 can only be used 4 time in any one permutaion/combination, producing a 5digit string. Example of expected results. 0 1 0 2 7
View Replies!
View Related
All Possible Combinations From Groups
I have a set of 13 groups, each with anywhere from 19 different variables for a total of 56 variables. I want to find all the possible combinations of 15 variables. Only one variable can be used from each group in a combination. Please see attached data sheet.
View Replies!
View Related
Combinations Which Are Exclusive Of Each Other
in columns CT, CU, and CV i have listed all possible combinations of 3 produced from the 12 variables (a through l). each combination has a score (listed in column cw). I have sorted all combinations in descending order by score. is there a way I can search all the combinations and select the 4 which have the highest score but are also exclusive? that is, none of the 4 combinations may contain the same variables.
View Replies!
View Related
