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.

How to Create Permutation Table in Excel: 4 Easy Methods

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

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

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 – 5, C – x – 6, and so on. As List 1, List 2, and List 3 have 2, 3, and 4 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.

• 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 INDEX, INT, ROW, and COUNTA Functions.

Read More: How to Generate or List All Possible Permutations in Excel

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.

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

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

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.

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

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

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

• Now, insert the following formula in cell D5.

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

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

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

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

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

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

• Next, insert the following formula in cell D5.

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

Read More: How to Perform Permutation and Combination in Excel VBA

Download Practice Workbook

You can download the practice workbook from here.

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

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