Microsoft Excel Tutorial: Combine two workbooks with common column.
Welcome to another episode of the MrExcel Podcast, where we help you learn Excel in a fun and easy way. In today's episode, we will be discussing how to combine two workbooks based on a common column. This question comes from David, who attended one of my seminars in Melbourne, Florida. He has two different workbooks with a common column, and he wants to combine all the columns into one workbook.
To start off, we have Workbook 1 and Workbook 2, both with a product code column. However, each workbook has different items, and David wants to combine them all. I have provided both workbooks for you to follow along, so feel free to download them from the link in the description. To combine these files, we will be using Power Query, which is built into Excel 2016. If you have an older version, you can download Power Query from Microsoft's website.
First, we will open a new blank workbook and save it as "Combined Files.xlsx". Then, we will go to Data, Get Data, From File, and select the first workbook. In the preview, we will select the sheet with the data and choose Load To, Only Create Connection. We will repeat this process for the second workbook. Now, we have both connections present in the panel.
Next, we will go to Data, Get Data, Combine Queries, Merge. In the Merge dialog, we will select both connections and choose the common column, which in this case is the product code. It is important to select the correct join type, which in this case is a full join to include all rows from both files. After clicking OK, we will see the initial result, which may not look like it worked. We will then right-click on the extra column and remove it, and the combined data will appear.
It is important to note that if the underlying data changes, we can simply refresh the query to update the combined file. This is a great feature of Power Query that makes it easy to maintain the combined data. To learn more about Power Query, I highly recommend the book "M is for (DATA) MONKEY" by Ken Puls and Miguel Escobar.
In conclusion, combining two workbooks with a common column can be easily done using Power Query. Just remember to start with a blank workbook, create connections for both files, and use the merge function to combine the data. Thank you for watching this episode of the MrExcel Podcast. Don't forget to download the workbook from the link in the description and stay tuned for more helpful Excel tips and tricks. See you next time!
Buy Bill Jelen's latest Excel book: [ Ссылка ]
You can help my channel by clicking Like or commenting below: [ Ссылка ]
This video answers these common search terms:
how to combine workbooks into once excel sheet
how to combine workbooks excel
can you combine workbooks in excel
how do i combine workbooks in excel
how to combine two workbooks excel
how to combine two workbooks in excel
how to combine excel workbooks
can you combine excel workbooks?
how do i combine excel workbooks into one
how do you combine two workbooks in excel
how to combine 2 excel workbooks
how to combine 2 excel workbooks into 1
how to combine multiple workbooks into one excel
how to combine mutliple workbooks into one excel
how do you combine multiple excel books
how to combine multyple excel work books into 1 spreadsheet
can you combine multiple excel workbooks
how to combine 2 different excel workbooks
how do you combine multiple excel workbooks into one workbook
how to combined excel spreadsheets from mulitple workbooks
how to combine individual worksheets from multiple workbooks in excel
how to combine columns from two different excel sheets
Table of Contents:
(00:00) Combining Two Workbooks Based on a Common Column
(00:23) Example of the Two Workbooks
(00:33) Columns in Each Workbook
(00:43) Preparation for Combining the Files for Power Query
(00:53) Downloading Power Query for Windows 10 or 13
(01:10) Selecting the First File and Load the data
(01:32) Repeating for the Second File
(01:57) Merging the Queries
(02:13) Choosing the Common Columns and selecting join type
(02:36) Removing Unnecessary Column
(03:05) Closing and Loading the File
(03:41) Updating the Merge File
(04:37) Recommendation for Learning Power Query
(04:59) Using Power Query to Solve the Problem
(05:37) Clicking Like really helps the algorithm
David from Florida has two workbooks that he wants to combine.
Both workbooks have the same field in column A, but then different data in the remaining columns.
One workbook might have extra items that are not in the other and David wants to see those.
There are no duplicates in either file
You can use Power Query to solve this.
To download this workbook: [ Ссылка ]
Join the MrExcel Message Board discussion about this video at [ Ссылка ]
