Al Ikram Amit
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. However, in my previous article, I discussed How to Consolidate Columns, you can also visit the article by clicking here. While discussing about the Consolidating Columns, we have not explored combinations among data. So, in this article, we will merge columns for example 4 columns along with the combinations among data.
Download Practice Workbook
You can download the practice workbook from the following download button.
2 Methods to Create All Combinations of 4 Columns in Excel
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 moreover 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).
- And 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 all combinations of 4 columns is shown below.
Read More: How to Create All Combinations of 6 Columns in Excel (2 Methods)
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
In the VBA code,
- X1 to X4 is the range.
- FN1 to FN4 is the integer and
- SV1 to SV4 is the string.
- RG is the range of the output cell which is F5.
- After running the VBA code, the result of Excel’s all combinations of 4 columns is shown below.
Read More: How to Apply All Combinations of 3 Columns in Excel
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 of our blog ExcelDemy.
