Bishawajit Chakraborty
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.
Download Practice Workbook
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.
- 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.
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.
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
Thank you, SAJAD, for your wonderful question.
Here is the solution to your question. Please take a look at the below steps.
I hope this may solve your issue.
Bishawajit, on behalf of ExcelDemy