How to Make Union of Two Sheets in Excel (4 Suitable Methods)

Get FREE Advanced Excel Exercises with Solutions!

For work purposes, data entries in Excel by different employees in various worksheets may be a root cause of discomfort for some officials who prefer to see the different entries in a single Excel worksheet. So, to solve the problem we have got various solutions. The best approach in this regard is to use the Excel union. In this article, I will show you 4 effective methods to perform union between two sheets in Excel.


How to Make Union of Two Sheets in Excel: 4 Effective Methods

The Quantity of 10 products for November and December is the dataset we use in the first scenario. The name of the Products and quantities for November are located in cells B5 through C14 of the November sheet.

quantity for November: to show 4 Effective Methods to Perform Union Between Two Sheets

Again, the name of Products and their Quantity for December are located in cells B5 through C14 of the December sheet.

Quantity for December: 4 Effective Methods to Perform Union Between Two Sheets


1. Unify Two Sheets Using Consolidate Feature

To get the solution from Excel Tab we have Consolidate option to merge data in Excel. In this method, we are going to demonstrate the merging of data into one sheet from two different sheets.

📌 Steps:

  • Select cell B5 from where consolidated data will be displayed.
  • Then follow the steps: Data >> Consolidate.

Unify Two Sheets Using Consolidate Feature

  • As a consequence, Consolidate, a little dialog box, will show up.
  • Keep the Function in Sum after that.

Consolidate, a little dialog box

  • After that, choose the November sheet’s cells B4:C14 from the Reference box.
  • Then choose Add.

choose the November sheet's cells B4:C14

  • Similarly, select the December sheet’s cells B4:C14 from the Reference box.
  • Now, click Ok.

select the December sheet's cells B4:C14

Finally, we will be able to perform a union between two sheets and will get the combined data from two sheets to one.

the combined data from two sheets to one

Note:

In this method, you have to ensure the same size of the table in both worksheets. And those tables must be located in the same cells in the two source worksheets.

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


2. Using VLOOKUP Function to Make a Union

In this case, we’ve used the VLOOKUP function to merge the data from the two sheets into a single sheet.

📌 Steps:

  • As we have to combine two datasets, we can do that using the VLOOKUP function.
  • To do so we will enter the following equation in cell C5.
=VLOOKUP(B5,November!B5:C14,2,FALSE)

Using VLOOKUP to Make a Union we have to enter the equation in cell C5

  • Press Enter and move the Fill Handle Tool downward.

Press Enter and move the Fill Handle Tool downward

  • Our desired Quantity in November column will look like the following image.

Quantity in November column

  • The VLOOKUP function can be used to join two datasets, which is what we are going to do.
  • To achieve this, we will type the equation below into cell D5.
=VLOOKUP(B5,December!B5:C14,2,FALSE)

Using VLOOKUP to Make a Union of two sheets

  • After entering the equation, we will drag the fill handle down to copy the formula for the rest of the cells.

drag the fill handle down

Thus, we have made a union of two Excel sheets.

excel union two sheets

Note:

In doing this, there must be a common column in both sheets. The VLOOKUP will work on that column as a lookup value and return values from individual sheets.


3. Use Power Query to Perform a Union Between Two Sheets

In Excel, we have Power Query to merge data in Excel. In this method, we are going to demonstrate the merging of data into one sheet from two different sheets by Power Query.

📌 Steps:

  • Before getting into Power Query, we have to turn our dataset into a Table by following the path: Insert >> Table.

Insert a Table to Perform a Union Between Two Sheets

  • Then our dataset is converted to a table and will look like the below image.

dataset converted to a table

  • Similarly, to the previous stage, we will turn the December dataset into a Table and the result will look like the below image.

turn the December dataset into Table

  • Select a cell from the table of November.
  • Now go to the Data tab and click on the From Table/Range.

By Power Query to Perform a Union Between Two Sheets go to the Data tab and click on the From Table/Range

  • After selecting the From Table/Range, a Table1- Power Query Editor will open up.
  • Here, we will not edit anything just follow the path: Home >> Close & Load >> Close & Load To…

Use Power Query to Perform a Union Between Two Sheets

  • Immediately after selecting Close & Load To…, a window will be opened to determine how the Close & Load operation will be done.
  • The name of the window is Import Data.
  • From the window Import Data, we will choose Only Create Connection and then select OK.

From the window Import Data, we will choose Only Create Connection

  • Now after choosing Close & Load To…, the Queries & Connection side window will be opened in the sidebar of our current worksheet.
  • Under Queries, we will see Table1(Connection Only).

Under Queries, we will see Table1(Connection Only)

  • Choose a cell from the December table.
  • And for now, go to the Data tab and choose “From Table/Range.”

go to the Data tab and choose "From Table/Range

  • A Table2- Power Query Editor will pop up after choosing From Table/Range.
  • Like in the previous steps, we won’t modify anything now; simply go as follows: Home >> Close & Load >> Close & Load To…

Use Power Query to Perform a Union Between Two Sheets

  • As soon as we choose Close & Load To…, a window will appear to provide the Close & Load operation’s parameters.
  • Import Data is the title of the window.
  • We will pick the Only Create Connection option from the Import Data box, then click OK.

pick the Only Create Connection option

  • The Queries & Connection side window will now be presented on the sidebar of our current worksheet after selecting Close & Load To…
  • Table2 will be found under Queries (Connection Only).

Table2 will be found under Queries

  • After the Close & Load operation is done, we will combine those data tables in a vertical combination.
  • To do so, we will follow the path: Data >> Get Data >> Combine Queries >> Append.

follow the path: Data>> Get Data>> Combine Queries>> Append

  • Now just after Close & Load, the Append window will appear.
  • As we are demonstrating Excel union two sheets with two tables, we are going to select Two tables under the line of Concatenate rows from two tables into a single table just like the steps shown in the below image.
  • Now for the First Table select Table1 and for the Second Table choose Table2. 

Append window will appear

  • Now in the Power Query, the consolidated data will look like the below image.
  • To insert the data in the Excel worksheet, we are going to select Close & Load.

we are going to select Close & Load

  • Finally, we have a table from two different sheets that are stacked one after another.

By Power Query to excel union two sheets, we have a table from two different sheets

Read More: How to Perform Union Query in Excel


4. Applying VBA to Make a Union of Two Sheets in Excel

Now we’ll create a macro to laterally integrate the two worksheets into a single worksheet.

Open the worksheet where you wish to combine these files. I’ve opened a worksheet here named Union Sheets Using VBA. Then, follow the steps below to accomplish this.

📌 Steps:

  • Firstly, go to the Developer tab and choose the Visual Basic tool.

Developer tab and choose the Visual Basic tool

  • Then select the Module option under the Insert tab.

select the Module option under the Insert tab to union two sheets

  • As a consequence, Module1 will be a brand-new module.
  • Double-click Module1 after that, and then type the following VBA code into the editor.
Sub Union_Two_Sheets()
Dim ws() As Variant
ws = Array("November", "December")
Result_Sheet = "Union Sheets Using VBA"
Union_Gap = 1
Set Result_Cell = Worksheets(ws(0)).UsedRange.Cells(1, 1)
First_Row = Result_Cell.Row
First_Column = Result_Cell.Column
For i = LBound(ws) To UBound(ws)
    Worksheets(ws(i)).Activate
    Row_Width = ActiveSheet.UsedRange.Columns.Count
    ActiveSheet.UsedRange.Copy
    Worksheets(Result_Sheet).Activate
    ActiveSheet.Cells(First_Row, First_Column).PasteSpecial Paste:=xlPasteAll
    First_Column = First_Column + Row_Width + Union_Gap
Next i
Application.CutCopyMode = False
End Sub

Applying VBA to Make a Union of Two Sheets in Excel

  • Now, press the Run button.

press the Run button

  • After pressing the Run button, the result looks like the following image. Two datasets from two different worksheets have been placed side by side using a VBA code.

excel union two sheets

Note:

Here, you have to edit your target sheet name in the Result_Sheet variable. And, you have to put your desired sheet names that you want to merge in the ws variable in the code.


Download Practice Workbook

You can download the practice workbook from the following download button.


Conclusion

Follow these steps and stages on Excel Union Two Sheets. 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 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:

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