Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Create Permutation Table in Excel (4 Easy Methods)

This tutorial will demonstrate how to determine Excel permutation table. We use the Permutation in Excel where in the data the order of the data matters a lot. That means, the position of the data also plays an important role in this scenario. In modern computer science, it plays an essential role in the case of sorting data or algorithms. Moreover, in biology, it is used to describe the RNA and DNA sequences of a genome, and in quantum physics, it is used for representing the different possible states of a certain particle. So, it is extremely important to learn how to determine permutation tables in Excel.


Download Practice Workbook

You can download the practice workbook from here.


4 Effective Methods to Create Permutation Table in Excel

We’ll use a sample dataset overview as an example in Excel to understand easily.  If you follow the steps correctly, you should learn how to determine an Excel permutation table. We will discuss the methods below.


1. Using Combined Excel Functions to Determine Permutation Table

Now, we want to determine the Excel permutation table using combinations of we have to use the combination of INDEX, INT, ROW, and COUNTA functions in Excel. When you intend to return a value (or values) from multiple ranges, you will use the reference form of the INDEX function. Moreover, if you have a decimal number in your dataset, you can use the INT function in Excel to get the nearest integer. The ROW function returns the row number for a given reference for a larger set of data in excel. The COUNTA function is generally used to count the cells containing the non-empty character(s) only. We can learn the method by following the below steps.

Steps:

  • At first, we arranged a dataset similar to the below image. we have the List 1 dataset in Column B, the List 2 dataset in Column C, and List 3 in Column D. We want to determine All Permutations (shown in Column E).

Dataset to Determine Permutation Table in Excel

  • After that, insert the following formula in cell E5.

=IFERROR(INDEX($B$5:$B$8,INT((ROW(B5)-5)/((COUNTA($C$5:$C$7)*(COUNTA($D$5:$D$8)))))+1)&" - "&INDEX($C$5:$C$7,MOD(INT((ROW(B5)-5)/COUNTA($D$5:$D$8)),COUNTA($C$5:$C$7))+1)&" - "&INDEX($D$5:$D$8,MOD((ROW(B5)-5),COUNTA($D$5:$D$8))+1),"")

Insert Formula to Determine Permutation Table in Excel

🔎 How Does the Formula Work?

The formula uses a set of different functions to determine the permutation table. Let’s analyze them part by part.

  • COUNTA($D$5:$D$8) —-> Calculates the number of items (non empty cells) in the range D5:D8.
    • Output: 4
  • ROW(B5)-5 —-> returns the number argument for the MOD
    • Output: 0
  • MOD((ROW(B5)-5),COUNTA($D$5:$D$8))+1 —-> turns into
  • MOD({0},4)+1 —-> then returns
    • Output: {1}
  • INDEX($D$5:$D$8,MOD((ROW(B5)-5),COUNTA($D$5:$D$8))+1) —-> simplifies to
  • INDEX($D$5:$D$8,{1}) —-> this results in the cell item of D5.
    • Output: 5
  • INDEX($C$5:$C$7,MOD(INT((ROW(B5)-5)/COUNTA($D$5:$D$8)),COUNTA($C$5:$C$7))+1) —-> similarly returns the cell value of C5.
    • Output: x
  • INDEX($B$5:$B$8,INT((ROW(B5)-5)/((COUNTA($C$5:$C$7)*(COUNTA($D$5:$D$8)))))+1) —-> also follows the similar calculations and returns the List 1 value from B5.
    • Output: C
  • Finally, the whole formula simplifies to
  • IFERROR(“C – x – 5″,””) —-> which returns
    • Output: C -x – 5

Here, we are making a permutation table where List 3 items will appear with each item in List 2. Then they will combine with each item of List 1. So the output will be like C – x – 5C – x – 6, and so on. As List 1List 2, and List 3 have 23, and items respectively, the possible number of permutations will be 2x3x4 or 24.

  • Therefore, you will get the result for this cell and then use the Fill Handle to apply the formula to all the cells.

Using FIll Hanle to Determine Permutation Table in Excel

  • Last, if you press the Enter button, then you will find the final result.

FInal Result to Determine Permutation Table in Excel

Hence, we have determined the Excel permutation table using combinations of INDEX, INT, ROW, and COUNTA Functions.


2. Applying Excel PERMUTATIONA Function to Create Permutation Table

In this case, our goal is to determine the Excel permutation table using the PERMUTATIONA function. We use this function to determine all the possible permutations in Excel. We can learn the method by following the below steps.

Steps:

  • First, arrange a dataset like the below image. We have the Total Numbers dataset in Column B and the Chosen Numbers in Column C. We want to determine All Permutations (shown in Column D).

  • Now, insert the following formula in cell D5.
=PERMUTATIONA(B5,C5)

Insert Function to Determine Permutation Table in Excel

  • Next, you will get the result for this cell, and then use the Fill Handle option to apply the formula to all the cells.

Using Fill Handle to Determine Permutation Table in Excel

  • Finally, you will get the final result using the PERMUTATIONA function.

Final Result to Determine Permutation Table in Excel

Thus, we have determined the Excel permutation table using the PERMUTATIONA function.


3. Use of PERMUT Function

Now, we want to determine the Excel permutation table by using the PERMUT Function in Excel. The function works the same as the PERMUTATIONA function with a slight change. In cases where you need to count the repetitions, you have to use the PERMUTATIONA function but if you don’t need to count the repetitive values then you have to use the PERMUT function. We can learn the method by following the below steps.

Steps:

  • First, arrange a dataset similar to the second method.
  • Afterward, insert the following formula in cell D5.
=PERMUT(B5,C5)

Insert Function to Determine Permutation Table in Excel

  • Then, you will get the result for this cell and then use the Fill Handle option to apply the formula to all the cells.

Using Fill Handle to Determine Permutation Table in Excel

  • Last, you will get the final result using the PERMUT function.

Final Result to Determine Permutation Table in Excel

Hence, we have determined the Excel permutation table using PERMUT functions.


4. Calculating Unique Permutation Numbers

Now, we want to determine the Excel permutation table using combinations of COUNTA and PERMUT functions in Excel. The PERMUT function determines the permutation with a count of no repetitive values and the COUNTA function counts the total number of the values and represents them. We can learn the method by following the below steps.

Steps:

  • At first, we arranged a dataset similar to the below image. For instance, we have the Symbol of Cards dataset in Column B and the Helper Column in Column C. We want to determine Possible Permutations (shown in Column D).

Dataset to Determine Permutation Table in Excel

  • Now, insert the following formula in cell D5.
=PERMUT(COUNTA($B$5:$B$8),C5)

Insert formula to Determine Permutation Table in Excel

  • Subsequently, you will get the result for this cell and then use the Fill Handle to apply the formula to all the cells.

Using Fill Handle to Determine Permutation Table in Excel

  • Last, you will get the final result using the combinations of PERMUT and COUNTA functions.

Final Result to Determine Permutation Table in Excel

Hence, we have determined the Excel permutation table using combinations of COUNTA and PERMUT functions in Excel.

Note:

As we have 4 data, the number we use in the Helper Column cannot exceed 4. And also, they have to be Natural Numbers.


How to Use Combined Functions to Determine Combination in Excel

Now, we want to determine the Excel permutation table using combinations of INDIRECT, ROW, COUNTA, TEXTJOIN, and TRIM Functions in Excel. The ROW function returns the row number for a given reference for a larger set of data in excel. the COUNTA function is generally used to count the cells containing the non-empty character(s) only. The INDIRECT function is generally applied to store a cell reference and then use the reference value with other functions to perform multiple operations in the spreadsheets. The TEXTJOIN function concatenates a list or range of text strings into a single string using a delimiter and can include both empty cells and non-empty cells. The Excel TRIM function removes the extra spaces from a text string.
At first, the combined functions take a Diamond card and shows us the possible combinations, and after that, it takes a Club card to use other possible combinations. Thus, the combined functions give us the output accordingly. We can learn the method by following the below steps.

Steps:

  • At first, we arranged a dataset similar to the below image. we have the Symbol of Cards dataset in Column B and the Helper Column in Column C. We want to determine All Combinations (shown in Column D).

  • Then, insert the following formula in cell D5.
=MAX(LEN(B5:B8))

  • In addition, if you press the Enter button, then you will find the result for this cell.

  • Moreover, insert the following formula in cell D5.
=CONCAT(B5:B8&REPT(" ",C5-LEN(B5:B8)))

  • Afterward, if you press the Enter button, then you will find the result for this cell.

  • Next, insert the following formula in cell D5.
=IF(ROW()>2^(COUNTA($B$5:$B$8)),"",TEXTJOIN(" , ",TRUE,IF(MID(TEXT(DEC2BIN(ROW()-1),REPT("0",COUNTA($B$5:$B$8))),ROW(INDIRECT("1:"&COUNTA($B$5:$B$8))),1)+0,TRIM(MID($C$6,(ROW(INDIRECT("1:"&COUNTA($B$5:$B$8)))-1)*$C$5+1,$C$5)),"")))

🔎 How Does the Formula Work?

  • COUNTA($B$5:$B$8): In the first portion, the value range you want to count in this scenario
  • ROW(INDIRECT(“1:”&COUNTA($B$5:$B$8)))-1)*$C$5+1,$C$5)): Now, we have used the INDIRECT function to mark it for many applications. Then we used the ROW function to set it as the row input value for this case.
  • IF(ROW()>2^(COUNTA($B$5:$B$8)): This portion sets the condition we want to use in this case.
  • TRIM(MID($C$6,(ROW(INDIRECT(“1:”&COUNTA($B$5:$B$8)))-1)*$C$5+1,$C$5)): In the first portion, the TRIM function removes extra space and MID function works on the middle column of this Excel sheet data.
  • IF(ROW()>2^(COUNTA($B$5:$B$8)),””, TEXTJOIN(“, “, TRUE, IF(MID(TEXT(DEC2BIN(ROW()-1), REPT(“0”, COUNTA($B$5:$B$8))), ROW(INDIRECT(“1:”&COUNTA($B$5:$B$8))),1)+0, TRIM(MID($C$6,(ROW(INDIRECT(“1:”&COUNTA($B$5:$B$8)))-1)*$C$5+1,$C$5)),””))): At last, we have combined all the sections along with the TEXTJOIN The whole formula combines all the functions to show all combinations in our dataset.
  • Therefore, you will get the result for this cell and then use the Fill Handle option to apply the formula to all the cells.

  • Last, if you press the Enter button, then you will find the final result.

Hence, we have determined the Excel permutation table using combinations of ROW, COUNTA, TEXTJOIN, and TRIM Functions.


How to Apply VBA Code to Determine Combination in Excel

We can also determine the Excel combination table by applying the VBA code. We can learn the method by following the below steps.

Steps:

  • To begin with, we arranged a dataset similar to the below image. we have the Symbol of Cards dataset in Column B, Combination 1, Combination 2, Combination 3, and Combination 4 in Columns C, D, E, and F.

  • In addition, go to the Developer Tab and then select Visual Basic.

  • Furthermore, the VBA editor will appear. Select Insert >> Module to open a VBA Module.

  • Moreover, type the following code in the VBA Module.
Sub DeterminePermutations()
Dim InValue1 As Variant
Dim OutValue1 As Range
Dim xDict As Object
Dim xlF As Long
Dim xCha As String
InValue1 = Range("B5:B8").Value
Set OutValue1 = Range("C4")
xCha = ","
For xlF = 1 To UBound(InValue1)
    Set xDict = CreateObject("Scripting.Dictionary")
    xDict(0) = "Combination " & xlF
    Call JoinValue(InValue1, xDict, 0, xlF, 0, "", xCha)
    OutValue1.Offset(0, xlF - 1).Resize(xDict.Count).Value = WorksheetFunction.Transpose(xDict.Items)
    Set xDict = Nothing
Next
End Sub
Sub JoinValue(ByRef prValue, ByRef poDictionary, ByRef pvLevel, ByVal pxMaxLevel, ByVal pdIndex, ByVal pxValue, ByVal pxChar)
Dim xlF As Long
If pvLevel = pxMaxLevel Then
    poDictionary(poDictionary.Count + 1) = pxValue
    Exit Sub
End If
For xlF = pdIndex + 1 To UBound(prValue)
    If pxValue = "" Then
        Call JoinValue(prValue, poDictionary, pvLevel + 1, pxMaxLevel, xlF, prValue(xlF, 1), pxChar)
    Else
        Call JoinValue(prValue, poDictionary, pvLevel + 1, pxMaxLevel, xlF, pxValue & pxChar & prValue(xlF, 1), pxChar)
    End If
Next
End Sub

  • Finally, if you press the Run or F5 button, then you will find the result of all the cells by using the VBA code.

At last, we have determined the Excel permutation table using the VBA in Excel.


Conclusion

Henceforth, follow the above-described methods. These methods will help you to determine the Excel permutation table. We will be glad to know if you can execute the task in any other way. Follow the ExcelDemy website for more articles like this. Please feel free to add comments, suggestions, or questions in the section below if you have any confusion or face any problems. We will try our best to solve the problem or work with your suggestions.

Zehad Rian Jim

Zehad Rian Jim

Hi there! I am Zehad Rian Jim. I graduated with a bachelor's degree in engineering from BUET. Currently, I am working as a technical content writer at ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of the workplace, my hobbies and interests include watching movies, tv series, and meeting new people. I also enjoy sports. My favorite sports are Cricket (to watch and play) and Badminton (play).

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo