Problem:
Merging/Joining/Combining data-sets in Excel has not been an easy task. There are third-party add-ins that makes it easy but out of the box, excel didn’t have an easy way to merge/join table data. But now with the Data Explorer add-in, we have an add-in that let’s us merge/join data in excel w/ few clicks.
If you haven’t downloaded and installed the data explorer add-in for Excel 2010 & 2013, you can find Information about it here: http://office.microsoft.com/en-us/excel/download-data-explorer-for-excel-FX104018616.aspx
Situation:
Input is Table 1 & Table 2. The output we need is merged Table.
Table 1:
Date | Daily New number | Month |
1/1/2012 | 0 | 1 |
1/2/2012 | 0 | 1 |
1/3/2012 | 0 | 1 |
1/4/2012 | 0 | 1 |
1/5/2012 | 0 | 1 |
1/6/2012 | 0 | 1 |
1/7/2012 | 0 | 1 |
1/8/2012 | 0 | 1 |
1/9/2012 | 0 | 1 |
………………………………..
Table 2:
Month | Month Name |
1 | January |
2 | February |
3 | March |
4 | April |
5 | May |
6 | June |
7 | July |
Merged Table:
Date | Daily New number | Month | Month Name |
1/1/2012 | 0 | 1 | January |
1/2/2012 | 0 | 1 | January |
1/3/2012 | 0 | 1 | January |
1/4/2012 | 0 | 1 | January |
1/5/2012 | 0 | 1 | January |
1/6/2012 | 0 | 1 | January |
1/7/2012 | 0 | 1 | January |
1/8/2012 | 0 | 1 | January |
1/9/2012 | 0 | 1 | January |
………………………………
Solution:
Let’s see how data explorer can help us Join/Merge Table 1 & Table 2.
1) create query that connects to Table 1 & Table 2.
2) Once you have queries that connect to the tables need to be merged, then click on Merge
3) Once you click on Merge, you’ll see a dialog:
Here you need to configure three things:
a) First Table
b) Second Table
c) Columns that will be used to merge/join data
In this case, this is how my merge dialog looks:
4) Once configured correctly, click on OK. You’ll see a dialog box where you can configure the output of the merged table. click on the new column to see the options that are available to you to configure the output of the merged table:
5) In this case, I’ve selected just one column month name that needs to be merged. You can also explore the aggregate tab in case you’ve numbers that needs merging.
6) This is how the output looks:
7) Rename the new column.
Select the new column > Right Click > Rename
8) Click Done if it looks OK.
9) The merged data is now available to you in Excel!
And one can analyze it!
Let’s see before and after. Note that instead of month numbers, we now have month names
Conclusion:
In this post, we saw how to merge/join/combine data from two different sources in Excel 2010.
Related articles
- Remove Duplicates in Excel Tables using Data Explorer Add-in: (parasdoshi.com)
Nice article. I think Data Explorer is for Excel 2013 only..
Thanks for the comment. Data Explorer is available w/ Excel 2010 too. Here are the instructions: http://office.microsoft.com/en-us/excel/download-data-explorer-for-excel-FX104018616.aspx
Well thats unfortunate if that program really is for excel 2013 only. Got kinda excited till i read that comment 🙁 At least i have an ok guide for combining data (http://www.excel-aid.com/excel-combine-data-pasting-values-formulas-formats-2.html if someone also uses excel 2010 and cant use data explorer), but its still a colossal pain to do. I might have to start looking into VBA after all.
Thanks for the comment. Data Explorer is available w/ Excel 2010 too. Here are the instructions: http://office.microsoft.com/en-us/excel/download-data-explorer-for-excel-FX104018616.aspx