Combine 2 Excel Sheets

 admin

Users of UW-Madison's institutional Tableau workbooks may need to pull data from one Microsoft Excel spreadsheet into another spreadsheet. This KB article explains how, by using an Excel formula called vLookup.

Follow the steps below to combine multiple Excel files into 1 file: If you want to merge all the existing files into a new Excel workbook, create the new Excel workbook and open it. However, if you want to merge all into one of the existing workbooks, open the workbook you want to merge all others into. Consolidate Multiple Worksheets using the Pivot Table Wizard. First press Alt+D, then press P. Excel displays the The Pivot Table Wizard dialog box. A short summary of data tables before we consolidate the worksheets: Sames ranges, same shapes and same labels are required to combine datasets into a pivot table. I would like to merge these two spreadsheets, updating the values in sheet 1 with values from sheet 2 This thread is locked. You can follow the question or vote as helpful, but you cannot reply to this thread.

How does the vLookup formula work?

Here are the steps to combine multiple worksheets with Excel Tables using Power Query: Go to the Data tab. In the Get & Transform Data group, click on the ‘Get Data’ option. Go the ‘From Other Sources’ option. Create a new worksheet labeled Combined to house the data from the other worksheets, as illustrated in Figure 1. Click on the Data menu. Click on FromOther Sources and then select From Microsoft Query from the drop-down.


Excel's vLookup formula pulls data from one spreadsheet into another by matching on a unique identifier located in both spreadsheets. For example, we want to add a column for email address but that data exists on a separate spreadsheet. vLookup can pull email addresses from Spreadsheet 2 into Spreadsheet 1 by matching CampusID 555123123 in both spreadsheets.

  1. Locate where you want the data to go. Click that cell only once.

  2. At the top, go to the Formulas tab and click Lookup & Reference.

  3. Select vLookup

  4. Excel’s vLookup wizard will pop up. We’ll walk through each part of the formula.


  5. Lookup_value
    Find the Unique Identifier (lookup value). It is usually in the same row as the empty cell you selected.
    Click once on the Unique Identifier so that the cell position will automatically fill in. In this example it is cell B2.


  6. Go to the next field, Table_array (click in it once). In Spreadsheet 2 highlight the table containing the info you want, starting with the Unique ID.


    In this example, Excel looks up Campus ID 555123123 in the first highlighted column of Spreadsheet 2.
    Note: Make sure each Unique ID is listed only once in the table_array (on the second spreadsheet) so that vLookup retrieves the correct value. For example, if 555123123 is duplicated in the table_array, where Student [email protected] is the email in one row and Student [email protected] in the other, Excel will choose one of the emails for you.
  7. Go to Col_index_num (click in it once). This identifies which column contains the information you want from Spreadsheet 2.
    Type the number of columns your field is from the Unique ID, where the Unique ID is 1. Here, the Email field is the third column.
  8. Go to Range_lookup (click in it once). Type FALSE to search for exact matches. The result will look something like this:


  9. Finally, copy and paste the formula to pull emails for the rest of the column.
    (Note: if your table array is in the same Excel workbook, put $ signs around the cell values, similar to the example below. This ensures that you reference the correct cells in the table array, meaning that the table array does not shift down when you paste the formula down. See Advanced Tip below for more details.)
vLookup Shortcut
If you feel comfortable with the vLookup tool instructions above, you can type the formula directly in the cell instead of using the wizard.
  1. Type the beginning of the formula: =VLOOKUP(
    The formula guide will appear below.
    (Note: You may notice Excel displays the formula in 2 places: the formula bar above and directly in the cell. You can edit the formula in either place.)


  2. Follow the guide and enter each value. Remember to insert a comma between each value.
  3. Insert a closed parenthesis ) and hit Enter. The end result will look like something like this:
    =VLOOKUP(B2,'[Spreadsheet Name.xlsx]SheetName'!$B$1:$E$11,3,FALSE)
  4. Finally, copy and paste the formula to pull emails for the rest of the column. Keep relative references in mind and use $ signs where necessary. (See Advanced Tip below for more details.)
Combine 2 excel spreadsheetsAdvanced Tip on Relative References
The position of the lookup value (Unique ID) in relation to the vLookup formula is maintained when you copy and paste. If you paste the formula one cell down (to E3), it looks up the Unique ID that is also one cell down (B3). The same is true when copying right, left or up.
In other words, the formula will stay x number of columns and y number of rows away from the lookup value – no matter where you paste the formula. In our example, the formula is the fourth column from the CampusID and in the same row. No matter where you paste the formula (in this example), it will always look up the cell that is the fourth cell to the left in the same row.
However, it is possible to lock cells in place by inserting 1 or more $ signs. This means, no matter where you paste the formula, it will always reference the same cell.When copying and pasting the formula, use the $ sign to lock in cells.
  • To lock in the lookup value in cell B1, insert $ signs before the column and the row:
    =VLOOKUP($B$1,’[Spreadsheet2.xlsx]SheetName’!$B$1:$E:$11,3,FALSE)
  • To lock in the column only, insert a $ before B only.
  • To lock in the row only, insert a $ before 1 only.


Need More Information or Help?

If you have questions about this Tableau document, please contact Melissa Chan, Office of Data Management and Analytics Services (ODMAS) at [email protected].
Keywords:Tableau Workbook Dashboard Excel 2 Two Combine Pull Data IDESuggest keywordsDoc ID:90851
Owner:Steven T.Group:Office of Data Management & Analytics Services KB
Created:2019-04-04 11:15 CDTUpdated:2020-06-20 04:08 CDT
Sites:Office of Data Management & Analytics Services KB
Feedback:243CommentSuggest a new document

Tableau Combine 2 Excel Sheets

June 14, 2018 - by Bill Jelen

David from Florida asks today's question:

I have two workbooks. Both have the same data in column A, but the remaining columns are different. How can I merge those two workbooks?

I asked David if it is possible that one workbook has more records than the other. And the answer is Yes. I asked David if the key field only appears once in each file. The answer is also yes. Today, I will solve this with Power Query. The Power Query tools are found in Windows versions of Excel 2016+ in the Get & Transform section of the Data tab. If you have Windows versions of Excel 2010 or Excel 2013, you can download the Power Query add-in for those versions.

Here is David's workbook 1. It has Product and then three columns of data.

Here is David's workbook 2. It has Product Code and then other columns. In this example, there are extra products in workbook2, but the solutions will work if either workbook has extra columns.

Here are the steps:

Excel macro combine multiple sheets
  1. Select Data, Get Data, From File, From Workbook:

  2. Browse to the first workbook and click OK
  3. In the Navigator dialog, choose the worksheet on the left. (Even if there is only one worksheet, you have to select it.) You will see the data on the right.
  4. In the Navigator dialog, open the Load dropdown and choose Load To...
  5. Choose Only Create a Connection and press OK.
  6. Repeat steps 1-5 for the second workbook.

    If you've done both workbooks, you should see two connections on the Queries & Connections Panel on the right of your Excel screen.

    Continue with the steps to merge the workbooks:

  7. Data, Get Data, Combine Queries, Merge.

  8. From the top drop down in the Merge dialog, choose the first query.
  9. From the second drop down in the Merge dialog, choose the second query.
  10. Click on the Product heading in the top preview (this is the key field. Note you can multi-select two or more key fields by Ctrl + Clicking)
  11. Click on the Product Code heading in the second preview.
  12. Open the Join Type and choose Full Outer (All Rows From Both)

  13. Click OK. The data preview does not show the extra rows and only shows 'Table' repeatedly in the last column.

  14. Notice there is an 'Expand' icon in the heading for DavidTwo. Click that icon.
  15. Optional, but I always unselect 'Use Original Column Name As Prefix'. Click OK.

    The results are shown in this preview:

  16. In Power Query, use Home, Close & Load.

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.

Note

Combine

Combine Excel Pages Into One

Combine 2 Excel Sheets

The icon for Refresh is usually hidden. Drag the left edge of the Queries & Connections pane to the left to reveal the icon.

Watch Video

Video Transcript

Learn Excel from MrExcel Podcast, Episode 2216: Combine Two Workbooks Based on a Common Column.

Hey, welcome back to MrExcel netcast, I'm Bill Jelen. Today's question's from David, who was in my seminar in Melbourne, Florida, for the Space Coast Chapter of the IIA.

David has two different workbooks where Column A is in common between both of them. So, here's Workbook 1, here's Workbook 2-- both have product code. This one has items that the first one doesn't have, or vice versa, and David wants to combine all the columns. So, we have three columns here and four columns here. I put both of these in the same workbook, in case you're downloading the workbook to work along. Take each one of these, move it out to its own workbook and save it.

Alright, to combine these files, we're going to use Power Query. Power Query's built into Excel 2016. If you're in the Windows version of 10 or 13, you can go out to Microsoft and download Power Query. You can start from a new blank workbook with a blank worksheet. You're going to save this file-- Save as, you know, maybe Workbook, to show the results of combined files .xlsx. Alright? And what we're going to do is, we're going to do two queries. We're going to go to Data, Get Data, From File, From Workbook, and then we'll choose the first file. In a preview, select the sheet that has your data, and we don't have to do anything to this data. So just open the load box and choose Load To, Only Create Connection, click OK. Perfect. Now, we're going to repeat that for the second item-- Data, From File, From a Workbook, choose DavidTwo, choose the sheet name, and then open the load, Load To, Only Create a Connection. You'll see over here in this panel, we have both connections present. Alright.

Now the actual work-- Data, Get Data, Combine Queries, Merge, and then in the Merge dialog, choose DavidOne, DavidTwo, and this next step is completely unintuitive. You have to do this. Choose the column or columns in common-- so Product and Product. Alright. And then, be very careful here with the join type. I want all rows from both because one might have an extra row and I need to see that, and then we click OK. Alright. And here's the initial result. It doesn't look like it worked; it doesn't look like it added the extra items that were in file 2. And we have this column 5-- it's null now. I'm going to right click column 5 and say, Remove that column. So open this expand icon and uncheck this box for Use original column name as prefix, and BAM! it works. So the extra items that were in File 2, that aren't in File 1, do appear.

Alright. Now in today's file, it looks like this Product Code column is better than this Product column, because it has extra rows. But there might be a day in the future where Workbook 1 has things that Workbook 2 doesn't have. So I'm going to leave both of them there, and I'm not going to get rid of any nulls because, like, even though this row at the bottom appears to be completely null, there might be in the future a situation where we have a few nulls in here because something's missing. Alright? So, finally, Close & Load, and we have our sixteen rows.

Now, in the future, let's say that something changes. Alright, so we'll go back to one of those two files and I'll change the class for Apple to 99, and let's even insert something new and save this workbook. Alright. And then, if we want our merge file to update, come over here-- now, watch out, when you do this the first time, you can't see the Refresh icon-- you have to grab this bar and drag it over. And we will do Refresh, and 17 rows loaded, the watermelon appears, the Apple changes to 99-- it's a beautiful thing. Now, hey, do you wanna learn about Power Query? Buy this book by Ken Puls and Miguel Escobar, M is for (DATA) MONKEY. I'll get you up to speed.

Wrap-up today: David from Florida has two workbooks that he wants to combine; they both have the same fields in Column A, but the other columns are all different; one workbook might have extra items that are not in the other and David wants those; there's no duplicates in either file; we're going to use power query to solve this, so start in a new blank workbook on a blank worksheet; you're going to do three queries, first one-- Data, From File, Workbook, and then Load to only Created Connection; the same thing for the second workbook, and then Data, Get Data, Merge, select the two connections, select the column that's common in both--in my case, Product-- and then from the Join Type, you want to full join all from the File 1, all from File 2. And then the beautiful thing is if the underlying data changes, you can just refresh the query.

Sheets

To download the workbook from today's video, visit the URL in the YouTube description.

Well, hey, I want like David for showing up for my seminar, I want to thank you for stopping by. I'll see you next time for another netcast from MrExcel.

Download Excel File

To download the excel file: combine-based-on-common-column.xlsx

Power Query is an amazing tool in Excel.

Excel Thought Of the Day

I've asked my Excel Master friends for their advice about Excel. Today's thought to ponder:

Power Bi Combine 2 Excel Sheets

'Always press F4 when you read range or matrix in a function'