# How to Generate or List All Possible Permutations in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.

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 Developer tab.
• Then, we will select the Visual Basic command. ### Step 2: Creating New Module

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

• Here, the Visual Basic window will open.
• After that, from the Insert option, we will choose the new Module to write the 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 F5 to run the 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
``````
• Againwe 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.

## Related Articles #### Bishawajit Chakraborty

I'm Bishawajit Chakraborty. Hello. I graduated from Rajshahi University of Engineering & Technology (RUET) with a degree in Mechanical Engineering. I'm working with ExcelDemy as a Content Developer for Excel & VBA. You can visit our website, Exceldemy if you'd like to read my published articles on MS Excel and VBA.

1. Reply 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

• Reply Bishawajit Chakraborty Apr 3, 2023 at 2:47 PM

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

• Open a new workbook in Excel and create a table with three columns – “Invoice”, “Item”, and “Quantity”.
• Enter the data as follows:
`  `
• Load this data into Power Query by selecting the table and clicking on “From Table/Range” under the “Data” tab.
• In the Power Query Editor window, select the “Item” column and click on “Group By” under the “Transform” tab.
• In the Group By dialog box, set the “New column name” as “Count” and select the “Count Rows” option for the “Operation”.
• Click on “OK” to create a new column that shows the count of each item sold.
• Add a new column and name it “Combination”.
• In the “Combination” column, enter the following formula: =IF([Count]>1,”Coffee and sandwich”,”Coffee only”)
• Click on “Close & Load” to load the transformed data into Excel.
• Insert a Pivot Table and select the transformed data as the source.
• Drag the “Combination” field to the Rows area and the “Count” field to the Values area.
• Add a new field to the Values area and name it “Total items sold”.
• Set the “Total items sold” field to the “Sum” aggregation function.
• Your final result should look like this: I hope this may solve your issue.

Bishawajit, on behalf of ExcelDemy Advanced Excel Exercises with Solutions PDF  