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.

**Table of Contents**hide

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

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

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