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

In this article, I will show you how to create combinations of 4 columns in Excel. Combination in Excel is one of the most frequently used processes that most Excel users encounter in their day-to-day experience. In this article, we will merge columns for example 4 columns along with the combinations among data.

How to Create All Combinations of 4 Columns in Excel: 2 Easy Methods

Suppose we have some digits in 4 columns. In the following article, we are going to merge these columns with multiple combinations.

1. Create All Combinations of 4 Columns Using Power Query Tool of Excel

First of all, we are going to make all combinations of 4 columns using Power Query. Follow the steps below to combine columns in multiple ways and consolidate all four columns.

📌 Steps:

• In the first stage, we will select the entire data range like the image below.
• Then, we will go through the following path: Data tab >> select From Table/Range.

• After selecting From Table/Range, a dialogue box will appear called Create Table.
• Now, we will check the box beside My table has headers.

• At this moment, we will see a Power Query Editor.
• In the Power Query Editor, choose Close & Load >> Close & Load To.

• After selecting Close & Load To, the Import Data dialog box will appear.
• Now we will mark the box beside Only Create Connection.

• Now follow the path, Data tab >> click on Get Data >> select Launch Power Query Editor.

• In this stage, we will select Custom Column under Add Column.

• Eventually, the Custom Column dialog box will appear.
• Set the New column name= Custom.
• And also set the Custom column formula as =1.
• Lastly, click OK.

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

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

• Just like the image below, we will expand Table1 and click on OK.

• After doing so, we will select Close & Load to take all combinations of 4 columns to Excel.

• Now, the result of Excel’s combinations of 4 columns is shown below.

Read More: How to Generate All Possible Combinations of a Set of Numbers in Excel

2. Create Combinations of 4 Columns Using VBA

One of the suitable approaches to solve this kind of problem is to use Microsoft Visual Basics for Applications. In the following method, we will explore easy steps to combine 4 columns.

📌 Steps:

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

• After that, Microsoft Visual Basics for Application will appear.
• Insert tab >> pick Module >> Module1 will appear in the side box like the image shown below.

• Now, in the Module1, we will insert the following VBA:

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

• After running the VBA code, the result of Excel’s combinations of 4 columns is shown below.

Download Practice Workbook

You can download the practice workbook from the following download button.

Conclusion

Follow these steps and stages to create all combinations of 4 columns in Excel. You are welcome to download the workbook and use it for your own practice. If you have any questions, concerns, or suggestions, please leave them in the comments section.

Related Articles

• How to Find All Combinations of 1 Column in Excel
• How to Get All Combinations of 2 Columns in Excel
• How to Apply All Combinations of 3 Columns in Excel
• How to Show All Combinations of 5 Columns in Excel
• How to Create All Combinations of 6 Columns in Excel
• How to Sum All Possible Combinations in Excel
• Find Combinations Without Repetition in Excel

<< Go Back to Excel COMBIN Function | Excel Functions | Learn Excel