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

`=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),"")`

**🔎 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**

- Output:
**ROW(B5)-5 —->**returns the**number**argument for the**MOD**- Output:
**0**

- Output:
**MOD((ROW(B5)-5),COUNTA($D$5:$D$8))+1 —->**turns into**MOD({0},4)+1 —->**then returns- Output:
**{1}**

- Output:
**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**

- Output:
**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**

- Output:
**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**

- Output:
- Finally, the whole formula simplifies to
**IFERROR(“C – x – 5″,””) —->**which returns- Output:
**C -x – 5**

- Output:

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.

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

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

`=PERMUT(B5,C5)`

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

`=PERMUT(COUNTA($B$5:$B$8),C5)`

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

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

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

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