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!

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

2 thoughts on “How to Generate or List All Possible Permutations in Excel”

Sajad says:

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

1. Bishawajit Chakraborty says:
  
  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

2 thoughts on “How to Generate or List All Possible Permutations in Excel”

Leave a Reply Cancel reply