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

Get FREE Advanced Excel Exercises with Solutions!

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.

excel union two columns

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

  • Following that, press ENTER.

Applying CONCAT Function to do union of two columns in Excel

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.

Final output of method 1 to do union of two columns in Excel


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.

Utilizing TEXTJOIN Function to do union of two columns in Excel

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.

Final output of method 2 to do union of two columns in Excel


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.

Using Ampersand Operator to do union of two columns in Excel

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.

Final output of method 3 to do union of two columns in Excel

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.

Incorporating VBA Macro to do union of two columns in Excel

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

Writing VBA code to do union of two columns in Excel

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.

Final output of method 4 to do union of two columns in Excel


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.

Stacking One Column Over Another to do union of two columns in Excel

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 the range argument.
    • “<>” → It refers to the criteria argument.
    • Output4.
  • Now, in the INDIRECT function,
    • “D”&ROW()-4 → This represents the ref_text argument.
    • 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 the logical_test argument.
    • C5 → This is the [value_if_true] argument.
    • {0} → It refers to the [value_if_false] argument.
    • Output90.
  • 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.

Final output of method 5 to do union of two columns in Excel


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.

How to Merge Two Columns Without Losing Data in Excel

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

Final output of method 6 to Merge Two Columns Without Losing Data in Excel


Practice Section

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

practice section to do union of two columns in Excel


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


<< Go Back to Excel Union | Excel OperatorsExcel Formulas | 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.

Tags:

Zahid Hasan
Zahid Hasan

Zahid Hassan, BSc, Industrial and Production Engineering, Bangladesh University of Engineering and Technology, has worked with Exceldmy for 1.5 years. He has written 95+ articles for Exceldemy. He has worked as an Excel & VBA Content Developer. He also worked as a VBA Developer for the Template team. Currently, he is working as a Junior Software Developer for the Excel Add-in project. He is interested in Software Development, Python, VBA, VB.NET, and Data Science, expanding his expertise in... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo