How to Do Union of Two Columns in Excel (5 Easy Ways)

In Excel, we often need to do union of two columns. We can manually merge two columns in Excel. But for a large, this will require a lot of time. Whereas, we can do the union of two columns by using some of the easiest functions and features of Excel. In this article, we will discuss 5 simple yet effective methods to do union of two columns in Excel. So, let’s start this article and explore these methods.

How to Do Union of Two Columns in Excel: 5 Effective Methods

In this section of the article, we will discuss 5 effective methods to do a union of two columns in Excel. Let’s say, we have the Addresses of Employees of ABC Company as our dataset. In the dataset, we have information on the Street and the City for each employee. Our goal is to do a union of these two columns and display the full address in a single cell. For better demonstration, we will use a comma and a space between the Street and the City.

Not to mention, we used the Microsoft Excel 365 version for this article; however, you can use any version according to your preference.

1. Applying CONCAT Function

Applying the CONCAT function is one of the easiest ways to do a union of two columns in Excel. To work properly, the CONCAT function only requires the addresses of the cells that you want to join together. Now, let’s follow the steps mentioned below.

Steps:

• Firstly, use the following formula in cell C14.

Here, cell C6 indicates the first cell of the Street column, and cell D6 refers to the first cell of the City column.

• Following that, press ENTER.

As a result, the union of cell C6 and cell D6  along with a space and a comma will appear in cell C14.

• Finally, use Excel’s AutoFill feature to get the rest of the Addresses as demonstrated in the following image.

2. Utilizing TEXTJOIN Function

Utilizing the TEXTJOIN function is another efficient way to do a union of two columns in Excel. Let’s follow the instructions outlined in the following section to do this.

Steps:

• Firstly, apply the formula given below in cell C14.

• After that, hit ENTER from your keyboard.

Subsequently, you will have the Address of Harry Maguire in cell C14 as shown in the image below.

• Lastly, use Excel’s AutoFill feature to obtain the remaining outputs.

3. Using Ampersand Operator

In this section of the article, we will learn to do a union of two columns in Excel by using the Ampersand Operator. The Ampersand operator allows us to join multiple text strings into a single text string. Let’s follow the procedure discussed in the following section.

Steps:

• Firstly, use the following formula in cell C14.

• Then, press ENTER.

Consequently, you will have the Address of Harry Maguire in cell C14 as demonstrated in the following image.

• Finally, apply Excel’s AutoFill option to get the Addresses for the rest of the employees.

Read More: How to Create Union of Two Tables in Excel

4. Incorporating VBA Macro

Incorporating the VBA Macro option is another smart way to do a union of two columns in Excel. Using this VBA code provides a one-click solution to do a union of two columns in Excel. Let’s follow the steps outlined below to do this.

Steps:

• Firstly, go to the Developer tab from Ribbon.
• Following that, click on the Visual Basic option from the Code group.

As a result, the Microsoft Visual Basic for Applications window will appear on your worksheet.

• Now, go to the Insert tab in the Microsoft Visual Basic for Applications window.
• Then, choose the Module option from the drop-down.

• After that, write the following code in the newly created Module.

Sub union_of_two_columns()
Dim starting_row_number As Long, ending_row_number As Long
With Sheets("VBA Macro")
ending_row_number = .Range("B" & Rows.Count).End(xlUp).Row
For starting_row_number = 5 To ending_row_number
Sheets("VBA Macro").Cells(starting_row_number, 5) = _
.Cells(starting_row_number, 3) & ", " & .Cells(starting_row_number, 4)
Next starting_row_number
End With
End Sub

• After the code, click on the Save icon.

• Afterward, use the keyboard shortcut ALT + F11 to return back to the worksheet.
• Then, press the keyboard shortcut ALT + F8 to open the Macro dialogue box as shown in the following picture.

• Now, in the Macro dialogue box, select the union_of_two_columns option.
• After that, click on Run.

Consequently, you will have the Addresses of all employees as demonstrated in the following image.

5. Stacking One Column Over Another

In this section of the article, we will learn to do a union of two columns in Excel vertically. Let’s say, we have the Marks of Grade 6 Students as our dataset. Here, we will stack the cells of the Math column over the Science column. To do this, let’s follow the instructions outlined below.

Steps:

• Firstly, apply the following formula in cell F5.

Here, cell C5 indicates the first cell of the Math column, and the range C$5:C$8 represent the cells of the Math column.

• Following that, hit ENTER.

Consequently, you will have the following output on your worksheet.

• Finally, use Excel’s AutoFill feature to get the remaining outputs as shown in the picture below.

How to Merge Two Columns Without Losing Data in Excel

While working in Excel, we often need to merge two columns. If we use the Merge & Center option of Excel, the data of the second column gets lost. But there are some alternative ways where we can merge two columns without losing any data in Excel. Let’s say, we have the ID Numbers of Employees of XYZ Company as our dataset. Our goal is to merge the Category column, and the Serial Number column to get the ID Number. Let’s follow the steps mentioned below to do this.

Steps:

• Firstly, use the following formula in cell D5.

Here, cell B5 represents the first cell of the Category column, and cell C5 represents the first cell of the Serial Number column.

• Now, press ENTER.

Subsequently, you will have the first ID Number as shown in the following image.

• Finally, use Excel’s AutoFill feature to get the rest of the ID Numbers as demonstrated in the picture below.

Practice Section

In the Excel Workbook, we have provided a Practice Section on the right side of the worksheet. Please practice it yourself.

Download Practice Workbook

Conclusion

So, these are the most common and effective methods you can use anytime while working with your Excel datasheet to do union of two columns in Excel. If you have any questions, suggestions, or feedback related to this article, you can comment below. You can also have a look at our other useful articles on Excel functions and formulas on our website.

Related Articles

• How to Perform Union Query in Excel
• How to Make Union of Two Sheets in Excel
• How to Use VBA Union Function in Excel

<< Go Back to Excel Union | Excel Operators | Excel Formulas | Learn Excel