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.


How to Generate or List All Possible Permutations in Excel: with Easy Steps

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

Using Developer Tab to Open VBA Window to Generate or List All Possible Permutations in Excel


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.

Creating New Module to Generate or List All Possible Permutations in Excel


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

Opening InputBox for Inserting Permutation Text in Excel


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.

Showing Results for All Possible Permutations in a List in Excel

Read More: How to Create Permutation Table in Excel


Download Practice Workbook

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


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. If you have any questions, comments, or recommendations, kindly leave them in the comment section below.


Related Articles


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Bishawajit Chakraborty
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

2 Comments
  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

    • Reply Bishawajit Chakraborty
      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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo