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".
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 ?
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.
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
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] .......
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
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
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.
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...
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?
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.
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.
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 RelatedIs 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.
I have a code that generate some combinations for a specific sum from a given data set. This code has two errors.
1. code does not look into data. it generates
2. Code generate those combinations for which sum is greater than target value.
E.g. first combination code generates is 3+7+10+12+12
This wrong by two means sum is
1) 44 while we need 40
2) 12 is not in data.
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 RelatedHow can I generate all the combinations a 3 digit number produces? Example 123 can be written as the following:
123
321
213
312
132
231
I THINK this is all the combinations it will produce.
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.
The aim is to find those combinations of variable values which generate highest total gain. I attached the spreadsheet which shows the variables (A through K) and a Gain column. I created 5 additional tabs which show all possible 2,3,4 and 5-member combinations of the variables. These tabs are like coordinates of which variable combinations should be examined. As an example I used the first combination from the second tab = A and B. If you look at these two columns on the EXAMPLE CALCULATION tab you will see 7,7 in the Number combination which is the first number pair for these two variables. The headings of the red and the yellow columns calculate the total count for this number pair and the total gain. These were recorded on a separate EXAMPLE RESULTS tab along with some other pairs which appear afterwards (these were recorded only from the first 39 rows of the AB data). I need a macro which will cycle through each variable pair (only using the combinations from the tab 2 for now, annd later from 3,4 and 5 tabs) collecting statistics for each unique number combination it encounters (printing to a separate sheet one after one), such as shown on the EXAMPLE RESULTS.
View 9 Replies View RelatedI need to count how many times a code appears over a 6mth period only on a single day. The data would look something like:-
1/1 2/1 3/1 4/1 5/1 6/1 7/1 8/1 9/1 10/1 etc
a b c a a b a a b b
If counting b the result would be 2 on the above 10 days as it appears on the 9/1 & 10/1 it would not be included. It is to be used to count the number of times somebody only appears on a single day over a period of time but not count them if they come back the next day.
I cant think of a way to do this using in built formulas (Countifs, sumproduct, etc) The example is basic and there could be up to 20 codes.
I have two columns of data, one with an ID # and the other one with a date. Some ID# + date combinations are repeated and I need to delete the duplicates. Here is a visual:
ID# date
54 19720211
54 19720211
54 19810512
67 19590312
89 19931104
89 19931104
I need to keep only unique ID/date combinations. I would need the final list to look like this:
ID# Date
54 19720211
54 19810512
67 19590312
89 19931104
I have a spreadsheet with two columns:
The first one will always have a 'W' or a 'E'.
The second one will have a three digit code. Like this:
W 507
W 507
W 507
E 504
E 504
W 505
W 505
W 504
W 504
Is it possible to have output that show how many of each combination there is? Like this(for the example above):
W 504 2
W 505 2
W 507 3
E 504 2
Have a monthly report process in which I need to count to combinations of values in 2 columns. Example:
A 1
A 1
A 2
A 3
B 2
B 1
B 3
B 3
B 4
Count results: A&1 = 2, A&2 = 1, etc. I'd like to get a summary table of the results. I see how to use sumproduct to count combinations, but that requires a sumproduct formula for every combination. The 2 colums are lengthy and the contents vary each month, so building the formulas each month will be a fair amount of work. Is there another way?
What I am trying to achieve using the example below:
ID Subject 1 Subject 2 Subject 3
1 Italian French German
2 Italian Art Physics
3 German French Italian
4 French Italian German
the result:
Italian French German 3
Italian Art Physics 1
As in the example, the combinations of Italian, French and German where counted, irrelevant of whether the subjects are in 2nd, 3rd or 4th column.
I tried to do this task by creating a pivot table but there are so many permutations and subjects that it would take me a long time to add the combinations.
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 RelatedI have a table with 6 columns (A to F) and multiple rows each, with cells containing words. Taking the words in any one cell from each of the columns in order from A to F will form a complete sentence each time. I need a solution to display all unique possible combinations in column G.
The number of rows is different for each column. A successful result in column G has to include cells from all columns (A to F).
I searched this forum and found a few analogous questions/solutions, but nothing close enough for me to apply to my case. I tried using a concatenation formula, but I have to manually edit the formula in each cell to get all unique combinations (and that would mean thousands of times). If I just drag the formula down it will increment all cell rows instead of one cell's row at a time.
Here's an example : all possible unique combinations.jpg
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] ......
I have 7 columns x 3 rows with unique numbers. I wish to determine every combination of the above numbers without repeats.
5
11
17
24
29
35
40
[Code] ........
Column 1 would only use column 1 numbers.
Column 2 would only use column 2 numbers...etc.
Also have the same for 5 rows of unique numbers.
I've been working on this for 10 years and this is the last step to finalizing the WINNING numbers.
Back checking using the 5 row technique I would have spent 10,000,000.00 playing over 8 years with a winners purse of 33,000,000.00
With the 3 row I would have spent close to 200k and won 2.5 mil.
Only problem is that I don't have the 10 to start with, the 200k is a maybe .
I need to be able to generate the combinations to play the numbers.
I have two columns in a spreadsheet, and I need to write a formula to determine how many times each combination shows up. Example:
SKU-A Location1
SKU-A Location1
SKU-A Location2
SKU-B Location1
SKU-B Location7
SKU-B Location7
SKU-B Location8
SKU-B Location8
SKU-B Location8
SKU-B Location8
I want it to have the data converted to be like this (with the third column being the the number of times that combination appears):
SKU-A Location1 2
SKU-A Location2 1
SKU-B Location1 1
SKU-B Location7 2
SKU-B Location8 4
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