How to Create All Combinations of 4 Columns in Excel (2 Ways)

We have some digits in 4 columns. We are going to merge these columns with multiple combinations.

Method 1 – Create All Combinations of 4 Columns Using Power Query

Steps:

Select the entire data range like the image below.
Go to the Data tab and select From Table/Range.

A dialog box will appear called Create Table.
Check the box My table has headers.

You’ll get the Power Query Editor.
Choose Close & Load, then Close & Load To.

The Import Data dialog box will appear.
Mark the box for Only Create Connection.

Go to the Data tab, click on Get Data, and select Launch Power Query Editor.

Select Custom Column under Add Column.

The Custom Column dialog box will appear.
Set the New column name to Custom.
Set the Custom column formula as =1.
Click OK.

In the Power Query Editor, go to the Home tab, select Merge Queries, then click Merge Queries as New.

In the Merge dialog box, choose Custom (or whatever you named) column in both portions. Make the Join Kind as Full Outer (all rows from both).
Click OK.

Expand Table1 and click on OK.

Select Close & Load to take all combinations of 4 columns to Excel.

The result of Excel’s combinations of 4 columns is shown below.

Method 2 – Create Combinations of 4 Columns Using VBA

Steps:

Press Alt + F11 or go to the Developer tab and click on Visual Basic.

The Microsoft Visual Basics for Application window will appear.
In the Insert tab, pick Module, and Module1 will appear in the side box.

Insert the following VBA code in the module:

Sub CombinationsFor4Columns()
Dim X1, X2, X3, X4 As Range
Dim RG As Range
Dim xStr As String
Dim FN1, FN2, FN3, FN4 As Integer
Dim SV1, SV2, SV3, SV4 As String
Set X1 = Range("B5:B7")
Set X2 = Range("C5:C7")
Set X3 = Range("D5:D7")
Set X4 = Range("E5:E7")
xStr = "-"
Set RG = Range("F5")
For FN1 = 1 To X1.Count
SV1 = X1.Item(FN1).Text
For FN2 = 1 To X2.Count
SV2 = X2.Item(FN2).Text
For FN3 = 1 To X3.Count
SV3 = X3.Item(FN3).Text
For FN4 = 1 To X4.Count
SV4 = X4.Item(FN4).Text
RG.Value = SV1 & xStr & SV2 & xStr & SV3 & xStr & SV4
Set RG = RG.Offset(1, 0)
Next
Next
Next
Next
End Sub