A permutation is a mathematical technique that establishes the total number of possible arrangements in a collection. We can generate and list all possible permutations in Excel using a **VBA Macro**. We’ll write some code that takes some text as input, generates the permutations, and lists them in a worksheet.

**Steps:**

The built-in **Developer **tab offers the tools required to use Visual Basic for Applications (**VBA**) to build and execute a Macro. As a convention, the tab is disabled. To make it visible on the toolbar at the top of the Excel window, it has first to be activated in the **Options **section of the **Menu **bar.

- Open the
tab.*Developer* - Select the
command.*Visual Basic*

The** Visual Basic** window will open.

- From the
menu, select the*Insert*, where we’ll write the*Module**VBA*code.

Alternatively, use the **Alt + F11** keyboard shortcut, to launch the *VBA *editor.

- Copy the following
*VBA code*and paste it intoÂ the:*Module*

```
Sub Generate_Permutations()
'Declare variable
Dim xText As String
Dim yRow As Long
Dim zScreen As Boolean
zScreen = Application.ScreenUpdating
Application.ScreenUpdating = False
'Show text box
xText = Application.InputBox("Enter text for permutation:", _
"Possible Permutations", , , , , , 2)
'Apply If Else statement
If Len(xText) < 2 Then Exit Sub
If Len(xText) >= 8 Then
MsgBox "Too many permutations!", vbInformation, "Permutation"
Exit Sub
Else
ActiveSheet.Columns(1).Clear
yRow = 1
Call Permutation_List("", xText, yRow)
End If
Application.ScreenUpdating = zScreen
End Sub
Sub Permutation_List(Text1 As String, Text2 As String, ByRef pRow As Long)
'Declare variable
Dim j As Integer, xLength As Integer
'Put text length
xLength = Len(Text2)
'Apply If Else statement
If xLength < 2 Then
Range("B" & pRow) = Text1 & Text2
pRow = pRow + 1
Else
For j = 1 To xLength
Call Permutation_List(Text1 + Mid(Text2, j, 1), _
Left(Text2, j - 1) + Right(Text2, xLength - j), pRow)
Next
End If
End Sub
```

**Save**the code.- Press
to run the*F5**macro*.

**VBA Breakdown**

- Defines a subroutine name as
**Â Sub Generate_Permutations()**.

`Sub Generate_Permutations()`

- Declares our variables as,

`Dim xText As String Dim yRow As Long Dim zScreen As Boolean`

- Defines the text box where the text for permutations will be entered as,

`xText = Application.InputBox("Enter text for permutation:", _`

"Possible Permutations", , , , , , 2)

- Applies an If Else statement as,

```
If Len(xText) < 2 Then Exit Sub
If Len(xText) >= 8 Then
MsgBox "Too many permutations!", vbInformation, "Permutation"
Exit Sub
Else
ActiveSheet.Columns(1).Clear
yRow = 1
Call Permutation_List("", xText, yRow)
End If
```

- Defines a subroutine name as,
**Â**

`Sub Permutation_List(Text1 As String, Text2 As String, ByRef pRow As Long)`

- Declares our variables as,

`Dim j As Integer, xLength As Integer`

- Puts the input text in the variable as,

`xLength = Len(Text2)`

- Applies an If Else statement as,

```
If xLength < 2 Then
Range("B" & pRow) = Text1 & Text2
pRow = pRow + 1
Else
For j = 1 To xLength
Call Permutation_List(Text1 + Mid(Text2, j, 1), _
Left(Text2, j - 1) + Right(Text2, xLength - j), pRow)
Next
End If
```

Ends the *VBA Macro *with

`End Sub`

When the code is run, an input box will appear to insert the text for the permutation.

- Enter
**AB12**as an example. - Click
**OK**.

The list of all possible permutations is returned.

**Read More: **How to Create Permutation Table in Excel

**Download Practice Workbook**

**Related Articles**

- How to Calculate Permutations Without Repetition in Excel
- How to Perform Permutation and Combination in Excel VBA

**<< Go Back toÂ Excel PERMUT Function | Excel Functions | Learn Excel**

Take this example as data set :

Invoice 1 coffee

Invoice 1 sandwich

Invoice 2 tea.

Wanted to create a set solution in excel as follows ( May use PQ and PP)

Result

Cofee only 0 tea only 1 Cofee amd sandwich 1

Total item sold 3

Thank you, SAJAD, for your wonderful question.

Here is the solution to your question. Please take a look at the below steps.

I hope this may solve your issue.Â

Bishawajit, on behalf ofExcelDemy