Skip to main content

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

Author: Xiaoyang Last Modified: 2020-12-08

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?

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. Please, click a cell where to output the result, and then copy and paste the below formula into it:

=IFERROR(INDEX($A$2:$A$4,INT((ROW(1:1)-1)/((COUNTA($B$2:$B$6)*(COUNTA($C$2:$C$5)))))+1)&"-"&INDEX($B$2:$B$6,MOD(INT((ROW(1:1)-1)/COUNTA($C$2:$C$5)),COUNTA($B$2:$B$6))+1)&"-"&INDEX($C$2:$C$5,MOD((ROW(1:1)-1),COUNTA($C$2:$C$5))+1),"")

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.

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:

Click to Download Kutools for Excel and free trial Now!


  • 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...
kte tab 201905
  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Comments (19)
Rated 4.5 out of 5 · 1 ratings
This comment was minimized by the moderator on the site
Isn't this permutations without repetition, not combinations (of either variety)?

P(7,3) = 210 and that's the number of "combinations" your formula churns out for 3 columns of 7 rows.

Permutation of 7 choose 3 is 343 (repetition allowed).

C(7,3) = 35

Combination of 7 choose 3 is 84 (repetition allowed).

None of these match the 210 value returned by your formula.

Regardless, I like your formula. It can be extremely useful and taught me a lot about how to get something unusual out of Excel (LibreCalc in my case :) ).
This comment was minimized by the moderator on the site
Thanks! The formula worked nicely
Rated 4.5 out of 5
This comment was minimized by the moderator on the site
Esse código me ajudou bastante e combinou um item de cada coluna entre elas. Mas também preciso combinar dois itens de cada coluna, sem repetição. Alguém poderia me ajudar nisso?
This comment was minimized by the moderator on the site
Esse código me ajudou bastante e combinou um item de cada coluna entre elas. Mas também preciso combinar dois itens de cada coluna, sem repetição. Alguém poderia me ajudar nisso?
This comment was minimized by the moderator on the site
Bonjour, comment faire pour que chaque valeurs soient placées dans une colonne distincte en non séparées par un tiret ?
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations