So the entire sequence of r elements, also called a. Edit: Okay, not sure if my way is faster than the solution posted by Andreas (factoring as a product of cycles), I guess both are useful to know. If we choose r elements from a set size of n, each element r can be chosen n ways. The fastest way (without using tools such as Lagrange's theorem) is to calculate by repeated squaring: A9 (((A2)2)2)A A 9 ( ( ( A 2) 2) 2) A. Calculate the permutations for P R (n,r) n r. The macro will work for any number of items in the A column, within Excel limits. For a permutation replacement sample of r elements taken from a set of n distinct objects, order matters and replacements are allowed. If (UBound(vresult) = UBound(unique)) ThenĬall PermutationsNPR(vElements, p, vresult, lRow, iIndex + 1)įunction UniqueArray(todoarray As Variant) As Variant Sub PermutationsNPR(vElements As Variant, p As Long, vresult As Variant, lRow As Long, iIndex As Integer) VElements = Application.Transpose(Range("A1", Range("A1").End(xlDown)))Ĭall PermutationsNPR(vElements, i, vresult, lRow, 1) The following VBA script created for me this spreadsheet:ĭim vElements As Variant, vresult As Variant It doesn't give me all possible combinations permutations, it just changes what I typed in vertically down a column, and outputs the results horizontally. In column B the VBA gives me (in a single cell): one,two,three In column A, if I type (one word per row): One I have given this a try but it does not seem to work. I would like to be able to type in any number of input values and the input values will be any word, phrase, number, letters, or a combination permutation of all of those. Here is what it should look like if 3 values are given:ĭoes anyone know how to do this in Excel? It can help to draw out the recursion tree on paper at each step. Then you need to understand how the recursive algorithm is applied to carry out this concept. The output generated should be something like this One In this case the first thing you need to understand, conceptually, is how to create all permutations by swapping various element pairs in the array. How do I go about creating a formula which takes any number of given input values, and then generates an output value with all possible combinations permutations based on the input values given.įor example, if the input values where as following One Therefore, the suggested duplicate does not answer my question. The suggested duplicate always shows column A at the left most position of the output, column b in the middle of the output and column c at the left of the output. The suggested duplicate always provides 3 inputs per variation.įurthermore, my question also asks for variations to show all possible orders of the inputs too. The suggested duplicate does not show all possible variations based on the number of options, so an input of 3 can have a variation of 1, 2, and 3 of the inputs. Please note my question is different from the suggested duplicate.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |