3

Please note my question is different from the suggested duplicate. 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. The suggested duplicate always provides 3 inputs per variation.

Furthermore, my question also asks for variations to show all possible orders of the inputs too. 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.

Therefore, the suggested duplicate does not answer my question.


Original Question

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.

For example, if the input values where as following

One
Two

The output generated should be something like this

One
OneTwo
Two
TwoOne

Here is what it should look like if 3 values are given:

enter image description here

Does anyone know how to do this in Excel?

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.

I have given this a try https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/146983-all-possible-combinations-from-single-column but it does not seem to work.

In column A, if I type (one word per row):

One
Two
Three

In column B the VBA gives me (in a single cell):

one,two,three

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.

11
  • 1
    @cybernetic.nomad It looks like the Poster wants all the permutations associated with each combination – Gary's Student Jan 15 '19 at 0:16
  • ORDER is important. in the linked question, order is not important. – Forward Ed Jan 15 '19 at 0:24
  • 2
    Because there are a finite number of rows (1 048 576) to excel you will be limited to only 9 entries if you wish all the permutations to be written out in a single column. If I did my math right, you will be looking at 986 409 permutations. 10 entries would give 9 864 100 which exceeds the number of available rows. – Forward Ed Jan 15 '19 at 0:27
  • 1
    I'd be looking at nested loops in vba. Start by writing out all the single entries, then repeat the process starting with the first entry, and loop through the remaining letters, then bump to the next entry and loop through the remaining letters until all the 2 combinations are written. just keep repeating the pattern and eventually all will be written out. – Forward Ed Jan 15 '19 at 0:31
  • 1
    Here's a screen shot in case it rings a bell for an idea with any Excel functions or add-on features people may be familiar with: i.imgur.com/zTh37v6.png Perhaps there's a PowerShell that can do this, if I knew of something I'd try but this should be easy with a relation type query or join of the data in a table object or something to itself if applicable with some of those features. – Drink More Pimp Juice IT Jan 18 '19 at 1:53
4
+250

The following VBA script created for me this spreadsheet:

enter image description here

This is the script:

Option Explicit

Sub PermutationsN()
Dim vElements As Variant, vresult As Variant
Dim lRow As Long, i As Long

vElements = Application.Transpose(Range("A1", Range("A1").End(xlDown)))
Columns("B:Z").Clear

For i = 1 To UBound(vElements)
    ReDim vresult(1 To i)
    Call PermutationsNPR(vElements, i, vresult, lRow, 1)
Next i
End Sub

Sub PermutationsNPR(vElements As Variant, p As Long, vresult As Variant, lRow As Long, iIndex As Integer)
Dim i As Long, unique As Variant

For i = 1 To UBound(vElements)
    vresult(iIndex) = vElements(i)
    If iIndex = p Then
        unique = UniqueArray(vresult)
        If (UBound(vresult) = UBound(unique)) Then
            lRow = lRow + 1
            Cells(lRow, 3).Value = Join(unique)
        End If
    Else
        Call PermutationsNPR(vElements, p, vresult, lRow, iIndex + 1)
    End If
Next i
End Sub


Function UniqueArray(todoarray As Variant) As Variant
  Dim arr As New Collection, a
  Dim i As Long
  On Error Resume Next
  For Each a In todoarray
     arr.Add a, a
  Next
  ReDim returnVal(1 To arr.count)
  For i = 1 To arr.count
     returnVal(i) = arr(i)
  Next
  UniqueArray = returnVal
End Function

The macro will work for any number of items in the A column, within Excel limits.

1
  • Very nice script Harry +1. I tested and confirmed that this does exactly what is requested. You must have worked hard for the money on this one sir, nice job!! – Drink More Pimp Juice IT Jan 18 '19 at 17:41
2

consider this example A column data:- Numbers(header) one two three four five B column data:- OutPut(header)

paste this formula in B2 :-

=IFERROR(IF(INDEX(OFFSET($A$1,1,0,COUNTA($A$2:$A$20)),ROUNDUP(ROWS($A$1:A1)/COUNTA($A$2:$A$20),))=INDEX(OFFSET($A$1,1,0,COUNTA($A$2:$A$20)),MOD(ROWS($A$1:A1)-1,COUNTA($A$2:$A$20))+1),INDEX(OFFSET($A$1,1,0,COUNTA($A$2:$A$20)),ROUNDUP(ROWS($A$1:A1)/COUNTA($A$2:$A$20),)),INDEX(OFFSET($A$1,1,0,COUNTA($A$2:$A$20)),ROUNDUP(ROWS($A$1:A1)/COUNTA($A$2:$A$20),))&INDEX(OFFSET($A$1,1,0,COUNTA($A$2:$A$20)),MOD(ROWS($A$1:A1)-1,COUNTA($A$2:$A$20))+1)),"")

this function works "Numbers" column having data upto A20.

permutationexcelimage

1
  • Your formula just gives the permutations for the terms taken one and two at a time, not all permutations. It's possible to do this kind of formula for a specific number of terms, although it quickly gets insane. Even at three entries, the formula is outrageous. See Forward Ed's comment about 9 entries being all that would fit in Excel's row limit, which tells you that if your formula goes up to 19 entries, it's not doing what was asked in the question. – fixer1234 Jan 18 '19 at 10:25

Not the answer you're looking for? Browse other questions tagged or ask your own question.