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.

**Table of Contents**Expand

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

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 Generate All Possible Combinations of a Set of Numbers 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.

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

**Download Practice Workbook**

Get this sample file to practice by yourself.

## 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 you know any other method to calculate this.

**Related Articles**

- How to Get All Combinations of 2 Columns in Excel
- How to Apply All Combinations of 3 Columns in Excel
- How to Create All Combinations of 4 Columns in Excel
- How to Show All Combinations of 5 Columns in Excel
- How to Create All Combinations of 6 Columns in Excel
- How to Find Combinations Without Repetition in Excel
- How to Sum All Possible Combinations in Excel

**<< Go Back toÂ Excel COMBIN Function | Excel Functions | Learn Excel**

Hi thanks for this

and if i need make same combination number 4

( with 4 words )

But if we need to write all the possibilities in a different order and without repeating the same cost in raw

Hi AMR,

Thanks for reaching out! Could you please explain a bit more about what you want to do? I wasn’t able to fully understand your comment. Do you need to get all the permutations? Let us know in the reply.

Regards

Aniruddah