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.
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.
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.
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.
I have attached a file to better describe my issue, essentially I would like to count all the 'A's, all the 'B's and so on in the file and have the count next to it, so something like
A 6 B 3 D 5 F 1
In the file I uploaded I have column B as a count of the numbers, is there a way I could get just the final number for each letter, that would solve my problem.
I want to take a list of data and then subtotal the value by certain groups eg Toys. I then want to sort those subtotals by value descending. I then want to rank the sub totals
So basically i want to first select the group total and if that subtotal is the highest rank it. Its almost as if I need two subtotals (although Excel only gives me one)
I have a list of 600 figures (this may vary) but i need a formula that will look a one particular figure in this list and return if it is one of 5 equal groups. e.g in this example the lowest 125 figures in numeric order would be in group 1 up to the highest 125 figures would be in group 5.
I realise if I limit it to 4 groups then i could use the 'quartile' functions but i am stuck with 5 groups.
I'm trying to make a excel template that will take a list of names, changes but generally around 100, and randomly separate them into 4 sets of 8 groups evenly.
This grouping would be repeated 4 times, but there are some conditions.
Firstly, the same person cannot be put into a group again with someone they have previously been grouped with. Secondly, someone appearing in the first or last groups cannot appear in that group again.
I've been trying to do it via some complex cell formulas but they are quickly becoming overly complex and im not sure if it will actually work which has completely demotivated me. Ive not been looking at VBA but am now thinking its the right route. Ive been thinking of having a master list and then have an attribute of who people have been grouped with before during each of the groupings, and also what number group they were in and then checking against that or something, but im not sure if that is the most efficient solution.
I attached an example dataset : demo dataset.xlsx
Is it possible to use the Combobox ListFillRange to list values from two groups of cells? For example, in column 'A', I have the values, 1,2,3; and in Column 'B', I have the values, A, B, C. I want to list the values in column 'A' along with the value of say, B, in Column 'B'.
combobox.listfillrange = "Sheet1!A1:A3" < ---- how to include value B from column B in this list? Or, how to get value A from column B?
I would like to be able to count the amount of entries in column C and depending on the amount group them in either groups of 3 or 4, all names would be unique...so if there are 14 names in the list they would need to be grouped in to two groups of 4 and two groups of 3, if there were 19 then 4 groups of 4 and 1 group of 3 etc to a maximum 50 people, the results could appearon a seperate worksheet say pasted on to the worksheet starting with the groups of 3 (so paste a group of 3 then skip 3 rows then paste groups of 4 skip 2 rows, the row skipping is to allow seperation and manual entry of extra data). There will never be groups of 5 or more and never less than 3
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.
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,.....................
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
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.
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..
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:
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?
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
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:
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.
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 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.
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".
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.
