Generate all possible permutations given input values

Question

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.

Answer 1

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.

Answer 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.