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?
The following long formula can help to list all combinations of 3 columns, please do as this:
1. Please, click a cell where 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:
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:
If you have Kutools for Excel, with its powerful List All Combinations feature, you can list all combinations of multiple columns quickly and easily.
After installing Kutools for Excel, please do as this:
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: