When the order of the arrangements is concerned, a permutation is a mathematical technique that establishes the total number of possible arrangements in a collection. This article will demonstrate how to generate or list all possible permutations in Excel.

## Download Practice Workbook

You may download the following Excel workbook for better understanding and practice it by yourself.

## Step-by-Step Procedures to Generate or List All Possible Permutations in Excel

This article will show you how to generate or list all possible permutations in Excel with the *VBA Macro*. Firstly, you need to write a little bit of code and enter input characters will do. So, follow the next instructions to accomplish this. This article will show you how to generate or list all possible permutations in Excel with the *VBA Macro*. Firstly, you need to write a little bit of code and enter input characters will do. So, follow the next instructions to accomplish this.

### Step 1: Using Developer Tab to Open VBA Window

The **Excel **built-in **Developer **tab offers the tools required to use **Visual Basic** for **Applications **(**VBA**) to 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.

- Firstly, we will open the
tab.*Developer* - Then, we will select the
command.*Visual Basic*

### Step 2: Creating New Module

In this section, you will see how to create a new Module to write *VBA *codes.

- Here, the
window will open.*Visual Basic* - After that, from the
option, we will choose the new*Insert*to write the*Module**VBA*code.

### Step 3: Generating VBA Code for Listing All Possible Permutations

* VBA *is a programming language that may be used for a variety of tasks, and different types of users can use it for those tasks. Using the

**Alt + F11**keyboard shortcut, you can launch the

*VBA*editor. In the last section, we will generate

*VBA*code that makes it very easy to generate or list all possible permutations in

**Excel**.

- So, paste the following
*VBA code*into the*Module*. - Then, save the following code.

```
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
```

- After that, press
to run the*F5**VBA*program.

**VBA Breakdown**

- Firstly, we will define a subroutine name as
**Sub Generate_Permutations()**.

`Sub Generate_Permutations()`

- Secondly, we declare our variables as,

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

- Thirdly, we will show the text box to enter the text for permutations as,

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

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

- Next, apply the 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
```

- Again
**,**we will define a subroutine name as,

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

- Then
**,**we declare our variables as,

`Dim j As Integer, xLength As Integer`

- After that, put the text in the variable as,

`xLength = Len(Text2)`

- After that, apply the 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
```

Finally, end the *VBA Macro *by using

`End Sub`

### Step 4: Opening Input Box for Inserting Permutation Text

After running the *VBA *program according to the previous method, an input box will appear to insert the text for the permutation.

- Therefore, enter text for generating all the possible lists of permutations.
- So, we will write
**AB12**as text to find all possible permutations. - Then, click
**OK**.

### Step 5: Showing Results for All Possible Permutations in a List

In this section, you will get all the possible lists of permutations for a text after running the *VBA Macro*.

- Finally, you will get the following lists of possible permutations in the below image.

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

**Conclusion**

In this article, we’ve covered step-by-step procedures to Generate or List All Possible Permutations in Excel. We sincerely hope you enjoyed and learned a lot from this article. Additionally, if you want to read more articles on Excel, you may visit our website, **Exceldemy**. If you have any questions, comments, or recommendations, kindly leave them in the comment section below.

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