In mathematics, calculating combinations of a certain number of characters is a very common practice. In fact, we can count these combinations in **Microsoft Excel** as well. The combination can be calculated for single or multiple columns in **Excel**. In this article, we will learn how to find **all combinations of 1 column** in excel. We will demonstrate the process in **2** handy ways. So, without further delay, let’s jump into the methods.

## Download Practice Workbook

Get this sample file to practice by yourself.

## 2 Handy Ways to Find All Combinations of 1 Column in Excel

To describe the processes, we have prepared a sample dataset. This dataset shows the** 4 Symbol of Cards** that we usually see in playing **52 **cards. The names are placed in the **Cell range B5:B8**.

Now, we will find out all possible combinations from these symbols following the methods below.

### 1. Combine Excel Formulas to Find All Combinations of 1 Column

In this first method, we will apply some excel formulas to calculate all combinations. To do the task, follow the steps below.

- In the beginning, insert this formula in
**Cell C5**.

`=MAX(LEN(B5:B8))`

- Then, press
**Enter**to see the output.

**the MAX function**returns the largest value from the

**Cell range B5:B8**and

**the LEN function**determines the length of the range.

- Next, apply this formula in
**Cell C6**.

`=CONCAT(B5:B8&REPT(" ",C5-LEN(B5:B8)))`

- Afterward, hit
**Enter**to get the output.

**the CONCAT function**combines the texts of the

**Cell range B5:B8**and

**the REPT function**repeats each character as per the condition in

**Cell C5**. lastly, the

**LEN**function determines the length of the text string.

- Now, type this
**array 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)),"")))`

- Finally, press
**Enter**and then**AutoFill**to get all the combinations at once.

**Note:**In the formula, a

**Comma**(

**,**) is used as the separator. You can use any other separator according to your preference.

**Read More: ****How to Find Combinations Without Repetition in Excel**

### 2. Apply Excel VBA Code to Extract All Combinations of 1 Column

Another useful and fast method is, applying a **VBA Macro** code to find all the combinations from the single column. Following are the steps to do this.

- First, go to the
**Developer**tab on your workbook and select**Visual Basic**under the**Code**section.

- Then, you will see the
**Microsoft Visual Basic for Applications**window. - In the window, choose
**Module**from the**Insert**tab.

- After this, type this code on the blank page.

```
Sub FindCombinations()
Dim xrValue As Variant
Dim xOutRng As Range
Dim xoDictionary As Object
Dim xlF As Long
Dim xsChar As String
xrValue = Range("B5:B8").Value
Set xOutRng = Range("C4")
xsChar = ","
For xlF = 1 To UBound(xrValue)
Set xoDictionary = CreateObject("Scripting.Dictionary")
xoDictionary(0) = "Combination " & xlF
Call JoinValue(xrValue, xoDictionary, 0, xlF, 0, "", xsChar)
xOutRng.Offset(0, xlF - 1).Resize(xoDictionary.Count).Value = WorksheetFunction.Transpose(xoDictionary.Items)
Set xoDictionary = 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
```

- Now, press
**F5**to open the**Macros**window. - Lastly, click on
**Run**to operate the code.

- Finally, you will get all the combinations of
**1**column just like in the following image.

**Read More: ****How to Sum All Possible Combinations in Excel**

## Additional Tip

You can also get the number of each combination with** the COMBIN function**. The syntax of this function is,

**=COMBIN(number, number_chosen)**

Here, insert the number of characters of your dataset in the** number **argument. Along with it, put your required number of combinations in the **number_chosen** argument. For example, insert this formula to get each symbol of the card once in the combination.

`=COMBIN(4,1)`

## Conclusion

That’s all for today. Here, we tried to illustrate how to find **all combinations of 1 column** in excel in **2** handy ways. Let us know if your know any other method to calculate this. Furthermore, if you want to learn more about Excel, follow **ExcelDemy**.