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.

**Table of Contents**Expand

## 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

**and the**

*Street***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**

*City**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**.

`=CONCAT(C6,", ",D6)`

Here, cell **C6 **indicates the first cell of the ** Street** column, and cell

**D6**refers to the first cell of the

**column.**

*City*- 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**.

`=TEXTJOIN(", ",TRUE,C6,D6)`

- After that, hit
**ENTER**from your keyboard.

Subsequently, you will have the ** Address **of

**in cell**

*Harry Maguire***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**.

`=C6&", "&D6`

- Then, press
**ENTER**.

Consequently, you will have the ** Address** of

**in cell**

*Harry Maguire***C14**as demonstrated in the following image.

- Finally, apply Excel’s
**AutoFill**option to get thefor the rest of the employees.*Addresses*

**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
```

**Code Breakdown**

- Firstly, we introduced a sub-procedure named
**union_of_two_columns**. - Then, we declared two variables named
**starting_row_number**, and**ending_row_number**and specified their data type as**Long**. - Following that, we used the
**With statement**to specify the worksheet where the macro will work. - Then, we assigned the value of the
**ending_row_number**variable. - After that, we used a For Next loop to do union of two columns along with a comma and a space.
- Here, we used the
**Ampersand**operator to join the two columns. - Now, we closed the
**For Next**loop. - Following that, we ended the
**With**statement. - Finally, we terminated the sub-procedure.

- 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

**column over the**

*Math***column. To do this, let’s follow the instructions outlined below.**

*Science*__Steps:__

- Firstly, apply the following formula in cell
**F5**.

`=IF(C5<>"",C5,INDIRECT("D"&ROW()-COUNTIF(C$5:C$8,"<>")))`

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.

**Formula Breakdown**

- Here, in the
**COUNTIF**function,**C$5:C$8**→ This indicates theargument.*range***“<>”**→ It refers to theargument.*criteria***Output**→**4**.

- Now, in
**the INDIRECT function**,**“D”&ROW()-4**→ This represents theargument.*ref_text***Output**→**{0}**.

- Subsequently, the formula becomes
**=IF(C5<>””,C5,INDIRECT(“D”&ROW()-COUNTIF(C$5:C$8,”<>”)))**→**=IF(C5<>””,C5,{0})**. - In the
**IF**function,**C5<>””**→ This indicates theargument.*logical_test***C5**→ This is theargument.*[value_if_true]***{0}**→ It refers to theargument.*[value_if_false]***Output**→**90**.

- 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

**column, and the**

*Category***column to get the**

*Serial Number***. Let’s follow the steps mentioned below to do this.**

*ID Number*__Steps:__

- Firstly, use the following formula in cell
**D5**.

`=CONCAT(B5,C5)`

Here, cell **B5 **represents the first cell of the ** Category **column, and cell

**C5**represents the first cell of the

**column.**

*Serial Number*- 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 theas demonstrated in the picture below.*ID Numbers*

**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**