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

Get FREE Advanced Excel Exercises with Solutions!

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.

sample data


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.

From 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.

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.

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.

mark the box beside Only Create Connection

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

select Launch Power Query Editor

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

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.

Custom Column dialog box will appear

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

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

  • 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.

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

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

expand Table1 and click on OK.

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

select Close & Load to take all combinations

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

the result of Excel all combinations

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.

Press Alt+F11

  • 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.

Microsoft Visual Basics for Application will appear

  • 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

Create Combinations of 4 Columns Using VBA

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 combinations of 4 columns is shown below.

the result of Excel all combinations


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


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Al Ikram Amit
Al Ikram Amit

Al Ikram Amit, holding a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, serves as a key Excel & VBA Content Developer at ExcelDemy. Driven by a profound passion for research and innovation, he actively immerses himself in Excel. In his role, Amit not only skillfully addresses complex challenges but also exhibits enthusiasm and expertise in gracefully navigating tough situations, emphasizing his steadfast commitment to consistently delivering exceptional, high-quality content that adds significant... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo