How to Find All Combinations of 1 Column in Excel (2 Handy Ways)

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.

• Then, press Enter to see the output.

• Next, apply this formula in Cell C6.

• Afterward, hit Enter to get the output.

• Now, type this array formula in Cell D5.

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

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.

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.

Related Articles

• How to Create All Combinations of 4 Columns in Excel (2 Ways)
• Use COMBIN Function in Excel (4 Examples)
• How to Create All Combinations of 6 Columns in Excel (2 Methods)
• Apply All Combinations of 3 Columns in Excel
• How to Show All Combinations of 5 Columns in Excel