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

## Download Practice Workbook

## 5 Effective Methods to Do Union of Two Columns in Excel

In this section of the article, we will discuss ** 5** effective methods to do union of two columns in Excel. Let’s say, we have the

**as our dataset. In the dataset, we have information of the**

*Addresses of Employees of ABC Company***and the**

*Street***for each employee. Our goal is to do 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 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 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 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*

### 4. Incorporating VBA Macro

Incorporating the **VBA Macro** option is another smart way to do union of two columns in Excel. Using this **VBA **code provides a one-click solution to do 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.

**Read More: VBA Union Method to Join Range in Excel (3 Examples)**

### 5. Stacking One Column Over Another

In this section of the article, we will learn to do 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*

**Read More: How to Make Union of Two Sheets in Excel (4 Suitable Methods)**

## Practice Section

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

## 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, **ExcelDemy**, a one-stop Excel solution provider.