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.

Download Practice Workbook

Listing Possible Permutations.xlsm

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

Get FREE Advanced Excel Exercises with Solutions!

2 Comments

Sajad Apr 1, 2023 at 2:05 AM

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
  Thank you, SAJAD, for your wonderful question.
  
  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

How to Generate or List All Possible Permutations in Excel

Related Articles

Leave a reply Cancel reply