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.

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.

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

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.

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.

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.

All Combinations of 1 Column.xlsm

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.

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

Get FREE Advanced Excel Exercises with Solutions!

2 Comments

Amr Dec 23, 2023 at 6:52 PM

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
- Reply
  Aniruddah Alam Dec 24, 2023 at 4:40 PM
  
  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