Welcome to another episode of the MrExcel podcast, where we help you learn Excel the fun and easy way. In today's episode we will see 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 wants to combine all the columns into one workbook.
To start, we have Binder 1 and Binder 2, both with a product code column. However, each workbook contains different elements and David wants to combine them all. I have provided both workbooks for you, so feel free to download them from the link in the description. To combine these files, we will use Power Query, built into Excel 2016. If you have an older version, you can download Power Query from the Microsoft website.
First, we'll open a new blank workbook and save it as /"Combined Files.xlsx/". Next we will go to Data, Get Data, From File and select the first workbook. In the preview, we will select the sheet containing the data and choose Load To, Create Connection Only. We will repeat this process for the second workbook. Now we have the two connections present in the panel.
Then we will go to Data, Get Data, Combine Queries, Merge. In the Merge dialog box, 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 seem to have worked. We will then right-click on the additional column and delete it, and the combined data will appear.
It's 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 easier to maintain 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 achieved using Power Query. Remember to start with a blank workbook, create connections for both files, and use the merge function to combine the data. Thanks 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 useful Excel tips and tricks. See you next time!
Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/
You can help my channel by clicking like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/
This video answers these common search terms:
how to combine workbooks into a single excel sheet
how to combine excel workbooks
can you combine workbooks in excel
how to combine workbooks in Excel
how to combine two excel workbooks
how to combine two workbooks in Excel
how to combine excel workbooks
Can you combine Excel workbooks?
how to combine excel workbooks into one
how to 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 multiple workbooks into one Excel
how to combine multiple excel books
how to combine multiple excel workbooks into one spreadsheet
can you combine multiple excel workbooks
how to combine 2 different excel workbooks
how to combine multiple excel workbooks into one workbook
how to combine excel worksheets from multiple workbooks
how to combine individual worksheets from multiple workbooks in Excel
how to combine columns from two different excel sheets
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) Preparing to combine files for Power Query
(00:53) Power Query download for Windows 10 or 13
(01:10) Selecting the first file and loading the data
(01:32) Repeat for the second file
(01:57) Merging queries
(02:13) Choosing 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 resolve the issue
(05:37) Clicking like really helps the algorithm
David from Florida has two workbooks he wants to combine.
Both workbooks have the same field in column A, but different data in the remaining columns.
One workbook may contain additional items that are not in the other and David wants to see them.
There are no duplicates in either file
You can use Power Query to resolve this issue.
To download this workbook: https://www.mrexcel.com/download-center/2018/06/combine-based-on-common-column.xlsx
Join the discussion on the MrExcel forum about this video at https://www.mrexcel.com/board/threads/1154045/
Please take the opportunity to connect and share this video with your friends and family if you find it useful.
No Comments