How to generate all combinations of 3 or multiple columns in Excel?

Suppose I have 3 columns of data, and I want to generate or list all combinations of the data in these 3 columns as below screenshot shown. Do you have any good methods for solving this task in Excel?

Generate all combinations based on 3 columns of data by using a formula

Generate all combinations based on 3 or multiple columns of data by using VBA code

Generate all combinations based on 3 or multiple columns of data by using an awesome feature

Generate all combinations based on 3 columns of data by using a formula

The following long formula can help to list all combinations of 3 columns, please do as this:

1. Click a cell where you want to output the result, and then copy and paste the below formula into it:

Note: In this formula: A2:A4, B2:B6, C2:C5 are the data ranges that you want to use.

2. Then, drag the fill handle down to the cells until blank cells are displayed, that means, all combinations of the 3 columns have been listed, see screenshot:

Generate all combinations based on 3 or multiple columns of data by using VBA code

The above long formula can be somewhat difficult to use, if there are multiple columns data need to use, it will be troublesome for modifying. Here, I will introduce a VBA code to deal with it quickly.

1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

VBA code: Generate all combinations of 3 or multiple columns

Sub ListAllCombinations()
'Updateby Extendoffice
Dim xDRg1, xDRg2, xDRg3 As Range
Dim xRg  As Range
Dim xStr As String
Dim xFN1, xFN2, xFN3 As Integer
Dim xSV1, xSV2, xSV3 As String
Set xDRg1 = Range("A2:A4")  'First column data
Set xDRg2 = Range("B2:B6")  'Second column data
Set xDRg3 = Range("C2:C5")  'Third column data
xStr = "-"   'Separator
Set xRg = Range("E2")  'Output cell
For xFN1 = 1 To xDRg1.Count
    xSV1 = xDRg1.Item(xFN1).Text
    For xFN2 = 1 To xDRg2.Count
        xSV2 = xDRg2.Item(xFN2).Text
      For xFN3 = 1 To xDRg3.Count
        xSV3 = xDRg3.Item(xFN3).Text
        xRg.Value = xSV1 & xStr & xSV2 & xStr & xSV3
        Set xRg = xRg.Offset(1, 0)
       Next
    Next
Next
End Sub

Note: In the above code, A2:A4, B2:B6, C2:C5 are the data range that you want to use, E2 is the output cell that you want to locate the results. If you want to get all combinations of more columns, please change and add other parameters to the code as needed.

3. Then, press F5 key to run this code, and all combinations of the 3 or multiple columns will be generated at once, see screenshot:

Generate all combinations based on 3 or multiple columns of data by using an awesome feature

If you have Kutools for Excel, with its powerful List All Combinations feature, you can list all combinations of multiple columns quickly and easily.

1. Click Kutools > Insert > List All Combinations, see screenshot:

2. In the List All Combinations dialog box, specify the columns data and separators for listing the combinations as following screenshot shown:

3. After setting the data and separator, then, click Ok button, in the next prompt box, select a cell to locate the result, see screenshot:

4. And then, click OK button, all combinations have been generated immediately as below screenshot shown: