Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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.

Excel All Combinations of 1 Column


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

Combine Excel Formulas to Find All Combinations of 1 Column

  • Then, press Enter to see the output.

Here, 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)))

Combine Excel Formulas to Find All Combinations of 1 Column

  • Afterward, hit Enter to get the output.

In this formula, 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.
=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)),"")))

Combine Excel Formulas to Find All Combinations of 1 Column

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

Apply Excel VBA Code to Extract All Combinations of 1 Column

  • 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

Apply Excel VBA Code to Extract All Combinations of 1 Column

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

Excel All Combinations of 1 Column

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.


Related Articles

Guria

Guria

Hello! Welcome to my blog. I have completed my Bachelor in Architecture from Ashanullah University of Science & Technology, Dhaka. I am a passionate, goal-oriented person with an immense thirst for knowledge and an attitude to grow continuously. Besides Architectural work, I find it very enthusiastic to work in Excel blogging. Exceldemy is a platform where I have got the opportunity to flourish my skill in Microsoft Excel and therefore, here I will be posting blogs related to it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo