# How to Generate or List All Possible Permutations in Excel

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 Developer tab.
• Select the Visual Basic command.

The Visual Basic window will open.

• From the Insert menu, select the Module, where we’ll write the 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 F5 to run the 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

## Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
Bishawajit Chakraborty

Bishawajit Chakraborty, a Rajshahi University of Engineering & Technology graduate with a B.Sc. in Mechanical Engineering, has been associated with ExcelDemy since 2022. Presently, he is a content developer, specializing in Excel Power Query, Data Analysis and VBA. It is worth mentioning that he has authored more than 90 articles on VBA content development. His profound interest lies in the fields of data analytics and data science. He possesses expertise in VBA, Power BI, machine learning, and Python... Read Full Bio

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

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