How to Merge data from Two Spreadsheets based on a common attribute

This post is about generating a combined report off two different Spreadsheets. The use case is as follows  -  you have data from two different Excel spreadsheets and need to merge or combine the two spreadsheets into a single Spreadsheet based on a common attribute.

For example, say you have two Spreadsheets about Employees in one spreadsheet as below with their First Name, Last Name, Department and email.

First Name	Last Name	Department	email
Alice	King	Database	king.alice@abc.com
Bob	Martin	Networking	martin.bob@abc.com
Frank	Jones	Operations	jones.frank@abc.com
Alex	Miller	Engineering	miller.alex@abc.com
Simmons	Young	Database	young.simmons@abc.com
Greg	Baker	Software Dev	baker.greg@abc.com
Harry	Walker	ESD	walker.harry@abc.com
Edward	Johnson	Config Management	johnson.edward@abc.com
Phil	Evans	Operations	evans.phil@abc.com
Adam	Morgan	Infrastructure	morgan.adam@abc.com

And a second spreadsheet which has data about the employees Telephone and Office Location

First Name	Last Name	Contractor	Telephone	email
Greg	Baker	Yes	123	baker.greg@abc.com
Alex	Miller	Yes	125	miller.alex@abc.com
Frank	Jones	Yes	120	jones.frank@abc.com
Simmons	Young	Yes	121	young.simmons@abc.com
Harry	Walker	Yes	126	walker.harry@abc.com
Adam	Morgan	Yes	129	morgan.adam@abc.com
Edward	Johnson	Yes	122	johnson.edward@abc.com
Alice	King	Yes	124	king.alice@abc.com
Phil	Evans	Yes	127	evans.phil@abc.com
Bob	Martin	Yes	128	martin.bob@abc.com

Now in order to combine the two spreadsheets you need to use a common attribute which uniquely identifies the employee. In this case, email is the unique attribute. Using this email attribute, both the spreadsheets can be combined or merged to give a unified spreadsheet with complete information about the employee.

Below are the steps to generate a combined report from two different Spreadsheets. This example was done with Excel 2016.

Open a new Spreadsheet which will be used to combine the data from the two above spreadsheets.

Go to Developer Tab and click Record Macro

Go to Data Tab and select Get Data from source
Note: If you cannot see the Developer Tab in the Excel upper ribbon then go to Options and enable the Developer Tab.

From below Choose Data Source, select Excel Files and click ok

You will get new window for Select Workbook. Choose where your Excel workbook directory/location.

The selected workbook here is Sources.xlsx

After the Excel spreadsheet containing data has been connected as a data source, the wizard provides the worksheets identified within the spreadsheet as shown below - There are two sheets - Sheet1 and Sheet2. These sheets have the same data as the the above two tables (as shown in this post above as Employee Table - first table with First Name, Last Name, Department and the second table with First Name, Last Name, Telephone). Here is actual spreadsheet containing the two sheets, Sheet1 and Sheet2

 The above screenshot shows the Available tables and columns for the two sheets

The above two show the details of the columns inside the two sheets. You can pick which columns are of interest that you want to combine for the merged New spreadsheet that will contain data from both the sheets.

Important: make sure the Options for System Tables is checked or else you will not be able to see the columns within the selected sheets.

From the above Wizard for Choose Columns, make use of the  >   and  <   buttons to bring the column of interest to the right pane for "Columns in your query"

See below, the columns that were selected. We did not select email from Sheet2 as it would be a repeat of info, but selecting it would not have mattered either (just to make the display compact so you don't repeat same column info again)

Once done selecting, click Next.

After you select Next, the wizard gives you a warning since we have not yet specified which attribute is the common attribute.

Above shows the combined data from the two sheets. However, as the wizard had warned earlier this combined data is of not of much use. We need to select the common attribute between the two sheets This can be done via the upper left side, where the two sheets are shown as Sheet1$ and Sheet2$. We are choosing email column or attribute as the criteria or merging the two sheets. In other words, we are joining the data based on a common attribute, which in our case is email. This will link the two email attributes from the two sheets.

Above screenshot shows email column for Sheet1 has been linked with email column for Sheet2. (Click on email and you will get a pointer to link to any of the columns on right sheet, and link to the email on second sheet - Actual Step is as below
Click "email" under Sheet1$ and take your mouse (or drag it) to the Sheet2$ and when over the email attribute, release the mouse). Immediately, the data is merged as see above. 10 rows are shown with the merged data and all the columns that were selected.

Once data has been merged between the two spreadsheets, return to the Excel sheet - Go to File ----> Return Data to Microsoft Excel. You should now be able to see the actual data in the Excel. Inspect the data to make sure the results are correct. Now go to Developer Tab, and stop the Recording.

File --- Return Data to Microsoft Excel

Stop Recording 

Click Yes to clear all contents and then go back to view the Macro

Click Debug

Put an Apostrophe before the   CommandType = 0 line which was pointed out by the Debug command

And then click on Reset code

Go back to Excel spreadsheet and from Home tab clear all Contents so that new data will be populated when you again Run the Macro (because you just now commented out code and need to rerun the code)

Click Yes because you want to retrieve new data to the worksheet

You will be returned to Spreadsheet. Click anywhere on the Spreadsheet open and again click on Developer Tab and then Visual Basic and click on Run again.

After clicking on Run, go to the Worksheet from upper Left icon, you should see data populated again.

----------------------------------------------------------------------------

Add a Button to retrieve data

Again select all from the above spreadsheet and clear All contents.

Go to Developer and add Button Control. Click on Insert and select Button

Select your workbook as required

The Button is ready. When you click on it, Data will be retrieved again