Specific Number Permutations In Excel

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


ADVERTISEMENT

Output Huge Number Of Permutations

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

Allocation Of Permutations In Excel

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

Excel 2007 :: Permutations Of Text String For Phone Scripts

Mar 21, 2012

I have phone scripts that can be built from 6 different categories. Each of the 6 categories has anywhere from 1 to 11 different scripts within them.

I want to generate every possible script from these options, i.e:

Part I:
"Hi, as of 8am, on march 20th"

Part II:
"our records indicate 2 changes"
"our records indicate 50 changes"
"our records indicate no changes"

[Code] ......

I am using Excel 2007.

View 1 Replies View Related

Excel 2003 :: Get Number Of Items Against Specific Department?

Sep 7, 2012

I'm trying to get the number of items against a specific department which are contained in a sheet called "data", the sheet is contained in the same workbook.

There are named ranges for
Department (text)
Date_From & Date_To (in the format dd/mm/yyyy)
Band (Numeric 1 to 3)
Items (Numeric)

Using the following formula I get an #NUM error (using Excel2003)

=SUMPRODUCT(--(Department=A7),--(Date_From>=$B$2),--(Date_To

View 9 Replies View Related

Excel 2003 :: Convert Specific Date Text To Number?

Jun 20, 2014

I want to use a formula, in another cell, to convert ‎"Friday, ‎30 ‎May ‎2014, ‏‎3:47:16 PM" to a value. I am using Excel 2003.

View 2 Replies View Related

Excel 2010 :: Unhiding Specific Number Of Worksheets Based On Cell Value

Jun 26, 2013

I have been attempting to cobble together code from different forums for the past several hours to make a macro to unhide a specific number of worksheets based on a cell value, and have failed miserably thus far. Here are the specifics of what I have going on:

Microsoft Excel 2010
Workbook Name: TASERR Model - 06-26-13.xlsm

In this workbook, I have a worksheet called "Input". In cell C21 of that worksheet, workbook users will fill in a number between 1 and 50 (I have the value for that cell limited to those numbers through data validation). Based on the value in that cell (C21), I want to make a macro to unhide that specific number of hidden worksheets. I have 50 hidden worksheets which are named "Route (1)", "Route (2)", "Route (3)", etc.. If the value in cell C21 is 7, I want the worksheets named "Route (1)", "Route (2)", "Route (3)", "Route (4)", "Route (5)", "Route (6)", and "Route (7)" to unhide. If the value in cell C21 is 3, I want the worksheets named "Route (1)", "Route (2)", and "Route (3)" to unhide. I'm sure y'all can see the functionality I'm going for at this point.

View 8 Replies View Related

Writing Specific Content From Excel To External File Of Specific Name

Apr 29, 2013

I have a worksheet that contains two basic columns of data, A and B. What I would like to do is based on the value in column A, I would like to copy the contents of column B to a textfile (preserving the basic line structure and hopefully without any extra characters like quotation marks attached). So for all the values in column A = 'Account', Id like to take all the corresponding values in Column E (for example) and save them to the same file, preferably where the filename itself as 'Account.xyz'. And I would like to do this for all unique values in Column A, that is do it for 'Account' and 'AccountPrivilege' etc. Note Column A is essentially presorted alphabetically already. There are actually 1000s of rows with hundreds of unique A values so Id like to find a way to automate this process.

A
B

1
Account
Company Information

2
Account
Company

[Code] ........

View 2 Replies View Related

Entering Specific Number To Effect Number Section On Another Page

Jan 8, 2014

I am working on a concrete pour summary, I would like to have the specific codes on each tab be affected from one summary page. So when I enter a certain number on sheet 1 , it will affect that same number section that is on a different sheet. It will be adding a certain amount to that code section every time its entered. What functions need to be used to make this work..?

View 6 Replies View Related

Formatting A Number Field To Hold A Specific Number Of Digits

Feb 12, 2009

I wrote an excel program in Excel'03 for a dental office to manage state assistance patients and one of the table columns is the state assistance number. The problem is that the program defaults to a number format when it is entered instead of a text format, which is what I want. The issue is that state assistance numbers are always 8 digits and when it defaults to a number field the program drops the required preceeding zeros.

For example the number 00123456 will sometimes show up as 123456, which is wrong. It's like sometimes it will show up right and other times it gets a butterfly in its brain and deletes those zeros irritating the receptionist here. So, how do I get the table to either stay in text format or set up a number format that keeps the preceeding zeros?

View 3 Replies View Related

Entering Specific Number To Effect Number Section On Another Page?

Jan 8, 2014

I am working on a concrete pour summary, I would like to have the specific codes on each tab be affected from one summary page. So when I enter a certain number on sheet 1 , it will affect that same number section that is on a different sheet. It will be adding a certain amount to that code section every time its entered. What functions need to be used to make this work..?

View 3 Replies View Related

Find All Number Combinations In Dataset That Add Up To Specific Number?

Jun 3, 2014

I have attached an example. If I have a set of numbers such as the one attached, is it possible to create a formula that will show me all the combinations of numbers that add up to 55.52? In the attached I have highlighted in different colours all the number combinations that add up to 55.52. The numbers highlighted in blue appear within more then one combination. Is there a formula that can do this for me, instead of randomly adding numbers hoping they add up to 55.52.

View 1 Replies View Related

Find All Number Combinations In Dataset That Add Up To Specific Number

Jun 4, 2014

I have attached an example. If I have a set of numbers such as the one attached, is it possible to create a formula that will show me all the combinations of numbers that add up to 55.52? In the attached I have highlighted in different colours all the number combinations that add up to 55.52. The numbers highlighted in blue appear within more then one combination. Is there a formula that can do this for me, instead of randomly adding numbers hoping they add up to 55.52.

examples.xlsx‎

View 1 Replies View Related

How To Translate Number Into Specific Letter Related To Number

Feb 3, 2014

How to translate a specific number into a specific letter related to the number itself.

I have lots of different numbers for example all going down a specific cell (These numbers I want to separate into 3 different catagories. The positive numbers I want to be calculated as the same Letter that is next to that row directly over to the left of that cell.

So if the letter

B / cell / cell / +9 / Then I want if its a positive number like +9 to answer in the formula with the same letter (B) listed to the right in the same row in a different cell of course

B / cell / cell / -6 / Then I want if its any negative number like -6 to answer in the formula as the opposite Letter of (P) listed to the right in the same row in a different cell of course

P / cell / cell / +17 / Then I want if its a positive number like +17 to answer in the formula as the same letter (P) listed to the right in the same row in a different cell of course

P / cell / cell / -3 / Then I want if its a negative number like -3 to answer in the formula as the opposite Letter of (B) listed to the right in the same row in a different cell of course

and thirdly if its a (0) zero and only (zero's) Then I want the answer to stay (0) zero.

The number can be any numbers it doesn't matter. Only distinction is that positive number of +1 and up repeat and stay the same letter that the cell on the far left is already stated.

and all the negative number of -1 amd below all reverse the letter to the opposite letter of (B to a P) or (P to a B) listed to the right in the same row in a different cell of course

and of course (0) zero would be listed as a positive number and only return a (0) zero as the result of the formula.

View 9 Replies View Related

VBA To Number Rows In A Column But Starting From Specific Number

Mar 10, 2014

I would like to create a vba script when ran an input.box comes up and asks you to "enter number you want to start numbering from" and when the number is entered in the input box. e.g "7654", excel starts numbering each cell in column A from 7654 until there is an empty cell in column B.

View 7 Replies View Related

Permutations And Combinationns

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

Combinations (not Permutations)

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

All Combinations/Permutations

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

Class Of Permutations

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

Minus Number From Cell And Result In Specific Cell And Specific Column

Jan 20, 2009

I have number in cell (A1) = 100. when I enter number in any cell of column(B) for example (B1)=10. then in cell (C1) the result of (A1) - (B1) = (90) and if I add in cell (B2)=10 then in cell (C1) the result of (A1) - (B1+B2) = 80 .accumulatively in cell (C1). and any number in column (B), the result will be (A1) minus any number in column(B) accumulated in (C1)

second question

I have number in cell (A1) = 100. when I enter number at cell (B1) = 10 then the result would be in the adjacent cell (C1) = (A1) - ( B1) = 90 and If I enter a new number in cell (B2) = 10 then the result would be in the adjacent cell (C2) = (A1) - (B1+B2) = 80 and If I enter a new number in cell (B3) = 10 then the result would be in the adjacent cell (C3) = (A1) - (B1+B2+B3) = 70 and so on. I want the result to be add automatically to adjacent cell in column (C)

View 3 Replies View Related

Permutations For WORDS In A Cell

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

VBA To Create Permutations Of Different Rows

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

Permutations Of A List (groups Of 3)

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

Generating Permutations And Combinations

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

VB For Combinations Permutations (3 Datasets)

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

IF - FORMULA RESTRICTED TO 7 PERMUTATIONS

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

Permutations Checkspelling Speed

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

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 View Related

Combinations/Permutations Of Values

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

All Combinations Or Permutations From 2 Lists

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







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