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

Supposing, I have 3 columns of data, now, 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?

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 is somewhat difficult for using, 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 your need.

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.

Tips:To apply this List All Combinations feature, firstly, you should download the Kutools for Excel, and then apply the feature quickly and easily.

Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...

Merge Cells/Rows/Columns and Keeping Data; Split Cells Content; Combine Duplicate Rows and Sum/Average... Prevent Duplicate Cells; Compare Ranges...

Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...

Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...

Favorite and Quickly Insert Formulas, Ranges, Charts and Pictures; Encrypt Cells with password; Create Mailing List and send emails...

Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...

Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...

Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...

Pivot Table Grouping by week number, day of week and more... Show Unlocked, Locked Cells by different colors; Highlight Cells That Have Formula/Name...

Sub ListAllCombinations() 'Updateby Extendoffice Dim xDRg1, xDRg2, xDRg3, xDRg4, xDRg5, xDRg6, xDRg7, xDRg8, xDRg9 As Range Dim xRg As Range Dim xStr As String Dim xFN1, xFN2, xFN3, xFN4, xFN5, xFN6, xFN7, xFN8, xFN9 As Integer Dim xSV1, xSV2, xSV3, xSV4, xSV5, xSV6, xSV7, xSV8, xSV9 As String Set xDRg1 = Range("A2:A3") 'First column data Set xDRg2 = Range("B2:B3") 'Second column data Set xDRg3 = Range("C2:C10") 'Third column data Set xDRg4 = Range("D2:D2") 'Third column data Set xDRg5 = Range("E2:E3") 'Third column data Set xDRg6 = Range("F2:F3") 'Third column data Set xDRg7 = Range("G2:G4") 'Third column data Set xDRg8 = Range("H2:H3") 'Third column data Set xDRg9 = Range("I2:I3") 'Third column data xStr = "-" 'Separator Set xRg = Range("K2") '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 For xFN4 = 1 To xDRg4.Count xSV4 = xDRg4.Item(xFN4).Text For xFN5 = 1 To xDRg5.Count xSV5 = xDRg5.Item(xFN5).Text For xFN6 = 1 To xDRg6.Count xSV6 = xDRg6.Item(xFN6).Text For xFN7 = 1 To xDRg7.Count xSV7 = xDRg7.Item(xFN7).Text For xFN8 = 1 To xDRg8.Count xSV8 = xDRg8.Item(xFN8).Text For xFN9 = 1 To xDRg9.Count xSV9 = xDRg9.Item(xFN9).Text xRg.Value = xSV1 & xStr & xSV2 & xStr & xSV3 & xStr & xSV4 & xStr & xSV5 & xStr & xSV6 & xStr & xSV7 & xStr & xSV8 & xStr & xSV9 Set xRg = xRg.Offset(1, 0) Next Next Next Next Next Next Next Next Next End Sub

In VBA code I used four column and range of the column are E2:E75, B2:B267, C2:C195 & D2:D267. Out put range is J2. In this case out put result was exceed row limit. Please help to solve the error

For getting all combinations from 5 columns, may be the below VBA code can help you, please change the cell references to your data.

Sub ListAllCombinations()
'Updateby Extendoffice
Dim xDRg1, xDRg2, xDRg3, xDRg4, xDRg5 As Range
Dim xRg As Range
Dim xStr As String
Dim xFN1, xFN2, xFN3, xFN4, xFN5 As Integer
Dim xSV1, xSV2, xSV3, xSV4, xSV5 As String
Set xDRg1 = Range("A2:A7") 'First column data
Set xDRg2 = Range("B2:B7") 'Second column data
Set xDRg3 = Range("C2:C7") 'Third column data
Set xDRg4 = Range("D2:D7") 'Fourth column data
Set xDRg5 = Range("E2:E7") 'Fifth column data
xStr = "-" 'Separator
Set xRg = Range("H2") '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
For xFN4 = 1 To xDRg4.Count
xSV4 = xDRg4.Item(xFN4).Text
For xFN5 = 1 To xDRg5.Count
xSV5 = xDRg5.Item(xFN5).Text
xRg.Value = xSV1 & xStr & xSV2 & xStr & xSV3 & xStr & xSV4 & xStr & xSV5
Set xRg = xRg.Offset(1, 0)
Next
Next
Next
Next
Next
End Sub

To generate any random combination we can use
=INDEX($A$2:$A$4,RANDBETWEEN(1,3))&"-"&INDEX($B$2:$B$6,RANDBETWEEN(1,5))&"-"&INDEX($C$2:$C$5,RANDBETWEEN(1,4))