How to list all possible combinations from single column in Excel?

If you want to return all possible combinations from single column data to get the result as below screenshot shown, do you have any quick ways for dealing with this task in Excel?

List all possible combinations from single column with formulas

List all possible combinations from single column with VBA code

List all possible combinations from single column with formulas

The following array formulas can help you to achieve this job, please do step by step:

1. First, you should create two helper formula cells. In cell C1, please enter the below formula, and press Ctrl + Shift + Enter keys to get the result:

2. In cell C2, enter the following formula, and press Ctrl + Shift + Enter keys together to get the second result, see screenshot:

3. Then, copy and paste the following formula in cell D2, and press Ctrl + Shift + Enter keys together to get the first result, see screenshot:

4. And then, select this formula cell, and drag the fill handle down until blank cells appear. Now, you can see all combinations of the specified column data are displayed as below demo shown:

List all possible combinations from single column with VBA code

The above formulas are only available for newer Excel versions, if you have earlier Excel versions, the following VBA code can do you a favor.

1. Press Alt+ F11 keys simultaneously to open the Microsoft Visual Basic for Applications window.

2. Then, click Insert > Module, copy and paste the below VBA code into the window.

VBA code: List all possible combinations from single column

Sub ConnectArr()
'Updateby ExtendOffice
Dim xDValue As Variant
Dim xOutRg As Range
Dim xDictionary As Object
Dim xF As Long
Dim xChar As String
xDValue = Range("A2:A6").Value 'the data range
Set xOutRg = Range("C1") 'output range
xChar = "," 'separator
For xF = 1 To UBound(xDValue)
    Set xDictionary = CreateObject("Scripting.Dictionary")
    xDictionary(0) = "Sets of " & xF
    Call ConnectValue(xDValue, xDictionary, 0, xF, 0, "", xChar)
    xOutRg.Offset(0, xF - 1).Resize(xDictionary.Count).Value = WorksheetFunction.Transpose(xDictionary.Items)
    Set xDictionary = Nothing
Next
End Sub
Sub ConnectValue(ByRef pDValue, ByRef pDictionary, ByRef pLevel, ByVal pMaxLevel, ByVal pIndex, ByVal pValue, ByVal pChar)
Dim xF As Long
If pLevel = pMaxLevel Then
    pDictionary(pDictionary.Count + 1) = pValue
    Exit Sub
End If
For xF = pIndex + 1 To UBound(pDValue)
    If pValue = "" Then
        Call ConnectValue(pDValue, pDictionary, pLevel + 1, pMaxLevel, xF, pDValue(xF, 1), pChar)
    Else
        Call ConnectValue(pDValue, pDictionary, pLevel + 1, pMaxLevel, xF, pValue & pChar & pDValue(xF, 1), pChar)
    End If
Next
End Sub

3. And Then, press F5 key to execute this code. All combinations from the single column are listed as below screenshot shown: