Excel Workbook Fusion: Combine Workbooks with Common Column - Episode 2216
ฝัง
- เผยแพร่เมื่อ 4 ส.ค. 2024
- 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: www.mrexcel.com/products/latest/
You can help my channel by clicking Like or commenting below: www.mrexcel.com/like-mrexcel-...
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: www.mrexcel.com/download-cent...
Join the MrExcel Message Board discussion about this video at www.mrexcel.com/board/threads...
![[TH] LEV vs TLN | TE vs VIT - VALORANT Champions 2024 DAY 3](http://i.ytimg.com/vi/_AbmrWv2lO0/mqdefault.jpg)
To download the workbook from today's video, use the link at the bottom of this article: www.mrexcel.com/excel-tips/combine-based-on-common-column/
How would this work if you wanted to merge 3 files instead of 2?
@@ThePhilPeterson After merging File 1 and File 2 into a TempResult query, you would create a connection to File 3 and then Merge TempResult with File 3.
4 years later and this is exactly what I needed! Thank you SO MUCH!
The watermelon appears, the apple change to 99 and it's a beautiful day! - You make my day with that ending, Mr. Excel! Great tutorial. Thanks
LOVE the new Thumb Nail!!!!! How cool to wake up with coffee and see you, Bill Mr Excel Jelen, on the Thumbs Nail saying Let's do cool things with Excel!!! And, yes, Power Query is amazing : )
Thanks Mike! I appreciate it. Zeke is going to be working on updating thumbnails for the historical videos.
Wow!!! Cool : ) I know how hard it is to do all the things necessary to run a TH-cam channel, I just wish that I had someone to help me like Zeke... O, wait. Maybe Isaac can help me : )
I like the way you explain things, you saved me lots of time. Thanks
I've wanted to learn this for years! So awesome, thanks so much! Was able to merge two complex datasets with thousands of rows based on an ID #.
Excellent help. Perfect
"It's a beautiful thing." Indeed!
It is on fire!
Thank you. Very simple and precise
So Clear!! Thanks a million 😀
This is awesome! Thank you Mate! 这片视频解释得易懂，简洁！
Exactly what I was looking for, thank you!
thank you. I watched hours of videos about Power Queries and Pivot Tables and none of them had what I was trying to do, simply add to workbooks together and only shared the first (name) column and with a few various there as well. thank you.
Wow, awesomy good sir. Amazing, thanks for sharing this wonderful video
That is the key, what i was looking for! God bless you ;)
Thank you very much very helpful saved me lots of time!
Thank u very much. It solved my problem that I could not able to do for years.
STILL useful. Thanks for the very powerful tip!
this saved me today
it helps a lot.thanks
you are very nice sir, thankyou for helping us.
Power Query rocks!
Sir, THANK YOU!!!
You’re the best!!
Thank you 🙏
Great video man lifesaver
thank you very much
You are welcome
Thanks Bill
very well explained
thank you!!!!!!!!!!!!!!!!
Awesome as always Bill. Day after day I see power query is kinda replace excel functions and some VBA stuff. I have tried it couple of times and it was amazing.
There’s one thing odd about it, though, that it doesn’t seem to work with excel file having extension ‘xls’. I’m trying to download data from SAP related to production, sales and stock QTY and they all have one thing in common: Material number. The thing is I first need to save workbooks as ‘xlsx’ and then run query.
Keep this perspective in mind. Power Query has to be stored in the workbook. The XLS file format was invented in 1985 and does not have the ability to handle the storage of the query.
Thank you. It's really nice.
I see you have added the extra options in u r excel bars. Can you do the detailed video of it please?
Thank you 🤍
this is magic
Anything to do with Power Query, I'm curious and I'm in. Thank you Mr. Excel.
Thanks mate
Great - Thanks. But what about 3 workbooks?
Cool. I noticed you can refresh the query just by right-clicking anywhere in the green table in Excel and selecting Refresh. I don't know why that little refresh button is always hidden in the queries pane.
Great tip. Thanks!
The query did not work quite right. i know the queries were set and merged, because they refreshed when I changed the source. But the PowerQuery window did NOT show the additional rows in the David2 sheet. Is it necessary to turn the raw data into tables?
Also I did NOT have Join type. I highlighted both the product_code column and the product column, but there was only a checkbox saying Only show matching rows. What might I have done wrong?
perfect!!!
Tried several times, didn't work, noticed my "matching" column had an invisible space at the end of each entry, did a find/replace and then everything started matching. Thanks for the video, I've needed something like this for years!
Hello dear sir, Let me tell you LET ME TELL YOU - I have been scouring google for about 2 hours trying to find a feasible solution and I kept getting vlookups or copy pastes or or or and that just wasn't going to work for 455k rows!! AND it wouldn't merge the ancillary columns + i had null values... So THANK YOU! I knew there had to be some way.... and now I don't need to throw this all into a jupyter notebook. You saved me, God bless you and thanks.
Genius
Thank you! So helpful. Does anyone know if this is possible and how to on google sheets?
I can't vouch for this site, but I used the Internet to search for "Google Sheets equivalent to Power Query" and found: spin.atomicobject.com/2019/02/22/google-sheets-query/
Can you do the detailed video of with different rows
How can I do this with over 1000 rows?
This was good, but I want to take the merged content and simply have that as a new working document. Once I merged the two docs, I no longer need the ongoing functionality. Is there a way to do that? thanks
Here are the steps.
1. Make sure the results are visible
2. If you can't see the Queries and Connections pane on the right side, choose Data, Queries & Connections
3. Right-click on the query name in the pane and choose Delete. Excel will warn you that you won't be able to refresh the query any more. That's exactly what you want, so click OK.
4. Optionally, if you don't want the results stored in a table, go to the Table Design tab and choose Convert to Range. (This used to be called Table Tools Design tab in older versions of Excel.)
I think the second edition of power query book you mention will be released soon
The second edition is planned but it won't be out until the end of 2018. The authors delayed the book to include new features announced in May 2018.
thanks for the information
Someday you will be able to upgrade your tech book from version 1.0 to version 2.0
That is a hot hot hot idea! How would you envision that it would work? If you own the 1st edition of a $29 book, what would you pay for the e-book of the 2nd edition? That could be a really slick feature, if the publisher could find a way to verify that you owned the first edition.
small fixes free, upgrades could be released to people who bought the first book on your website. Not sure how I would price the book upgrade.
Anyway where I could send you my file. I'm trying to combine two tables. but I'm not finding any similar examples. I've used power query and power pivot yet it doesn't give me correct data no matter what I've tried.
Click on my channel name. Then About (middle of the page… third ? Tab). On the About tab, click to reveal my email address. Send me the file. I will check it Saturday morning if you send it today.
MrExcel. Is it possible to combine2 tables with columns that have different names? For example: Table1 [Item] ~ Table2 [Id]. As a kind of stacked rows
that's literalle the example he picked to show in the video...
@@jokarrahmit9101 That comment was when I was starting learning Power BI and I was lost, thanks for replied 😁
Isn't this the same thing as UNION ALL in SQL ?
Matt yes that's right. I wish PQ used SQL instead of M. I used to use Ms query a lot. Ironically PQ was developed by the MS SQL Server team.
Does this work with excel for mac?
Not yet. Microsoft is working towards letting Power Query tools work on the Mac, but they aren’t there yet.
Great video!!! But I have problems in the last step. Whhen I click close and load Excel tell me: [Data format error] We couldnt covert no number. Does anyone know where this error comes from and how to solve it? any assisatance would be extremely helpful
This usually means that one or more rows have data that is not numeric. For me, this always happens if one CSV file or Excel file has empty rows at the bottom.
In the Power Query Editor, you can open a query and then click on various items in the Applied Steps box at the right side. This will show you a preview of the first 1000 records.
If the problem is beyond the first 1000 rows, then back in Excel, look in the Data, Queries & Connections Pane. Does one table report an error? The "1 error" text should be clickable so you can see other data in that row.
@@MrXL Thanks for the advice! it works!!!
Your subtitles cover the bottom of the screen, so you can't see tab names.
TH-cam controls the subtitle location. On my device, they are just above the row of sheet names. One workaround is to turn of the “CC” setting for a few moments.
Thanks for that, I'd like to know how to link 4 or more workbooks over the year months (4+ in Jan, 4+ in Feb, 4+ in Marc, ... , 4+ in Dec) in one Output Report Form?
Not combination, I'm seeking for a connection method. Currently using MSO2021.
in Prev. versions there was a method to connect workboks in one master through : DATA , CONNECTION , ADD ....
In2021 I can't practice that method, any ideas. Thank You.