lophour.blogg.se

Hey, welcome back to MrExcel netcast, I'm Bill Learn Excel from MrExcel Podcast, EpisodeĢ216: Combine Two Workbooks Based on a Common Column.

Re-do Steps 1-16 by clicking this Refresh icon. Here is the beautiful feature: if the underlying data in either workbook changes, you can click the Refresh icon to pull new data in to the results workbook. Optional, but I always unselect "Use Original Column Name As Prefix". Notice there is an "Expand" icon in the heading for DavidTwo. The data preview does not show the extra rows and only shows "Table" repeatedly in the last column. Open the Join Type and choose Full Outer (All Rows From Both)Ĭlick OK. Note you can multi-select two or more key fields by Ctrl + Clicking)Ĭlick on the Product Code heading in the second preview. If you've done both workbooks, you should see two connections on the Queries & Connections Panel on the right of your Excel screen.Ĭontinue with the steps to merge the workbooks:įrom the top drop down in the Merge dialog, choose the first query.įrom the second drop down in the Merge dialog, choose the second query.Ĭlick on the Product heading in the top preview (this is the key field. Repeat steps 1-5 for the second workbook. In the Navigator dialog, open the Load dropdown and choose Load To.Ĭhoose Only Create a Connection and press OK. (Even if there is only one worksheet, you have to select it.) You will see the data on the right. In the Navigator dialog, choose the worksheet on the left. Select Data, Get Data, From File, From Workbook:īrowse to the first workbook and click OK In this example, there are extra products in workbook2, but the solutions will work if either workbook has extra columns. It has Product Code and then other columns. It has Product and then three columns of data. If you have Windows versions of Excel 2010 or Excel 2013, you can download the Power Query add-in for those versions. The Power Query tools are found in Windows versions of Excel 2016+ in the Get & Transform section of the Data tab. Today, I will solve this with Power Query. I asked David if the key field only appears once in each file. I asked David if it is possible that one workbook has more records than the other. Both have the same data in column A, but the remaining columns are different. David from Florida asks today's question: