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:

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.

The following VBA script created for me this spreadsheet:

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.

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.