Merge two relational data sets

This article demonstrates how to merge two relational data sets before creating a Pivot table. A Pivot Table is limited to one table (data source) when you are working with relational data and I want to calculate the sales figures for each salesperson.

The image above shows one Excel table to the right that contains the product, price, and the company. The Excel Table to the left shows the salespersons and the corresponding company, a salesperson may work with multiple companies.

1. Create Excel Tables

I highly recommend Excel Tables, they save you a lot of time if you need to add more data to your data set and they have other useful features as well. You don't need to update the cell references each time you add data, however, you still need to refresh the Pivot Table.

1.  Select all cells you want to convert to an Excel Table.
2. Press CTRL + T to create an Excel Table, a dialog box is displayed.
3. Enable the checkbox if the Table has headers.
4. Press with left mouse button on OK button.

Excel applies automatically cell formatting to a new Excel Table, you can change the Table style if you don't like it.

Select any cell in the Excel Table, go to tab "Table Design" on the ribbon and select a new Tables Style if you prefer something else.

Back to top

2. Add a column to an Excel Table

Excel expands the Excel defined Table automatically if you enter a value in an adjacent cell. Let's look for data in the first table [Table1].

1. Select cell G2 which is the adjacent cell in this case, see image above.
2. Type or copy/paste the following formula:
   =INDEX(Table1[Salesperson], MATCH([@Company], Table1[Company], 0))
3. Press Enter.

Another great feature is that the Excel defined Table automatically copies formulas to all cells in a column which you will see after you pressed Enter in the third step above.

2.1 Explaining formula

The formula you entered in cell G2 is actually better than a VLOOKUP formula, it allows you to search any column and the column you return values from is not hardcoded into the formula which may be a problem if you insert more columns to a data table.

Step 1 - Find relative position

The MATCH function has three arguments, the first argument is a cell reference to a cell on the same row in column Company as the cell you are currently adding the formula to. [@Company] It is called a structured reference and is special to Excel Tables.

[@Company] does not contain a reference to a Table name, the formula is located in the same Table so the Table name is not needed.

The second argument is a structured reference to all values in column Company in Table1 Table1[Company] and the third argument 0 (zero) tells Excel to perform an exact match.

MATCH(lookup_value, lookup_array, [match_type])

becomes

MATCH([@Company], Table1[Company], 0)

becomes

MATCH("MARS Industries", {"MARS Industries"; "The Lanford Lunch Box"; "Minuteman Cafe"; "Springfield Beans"; "Smith and Co."; "Sixty Second Avenue"; "Cyberdyne Systems"; "Osato Chemicals"; "Klimpys"; "Corellian"}, 0)

and returns 1. "MARS Industries" is in the first position in the array.

Step 2 - Get value

The INDEX function returns a value from a given cell range or array based on a row and column number. The column number is optional.

INDEX(array, [row_num], [column_num])

becomes

INDEX(Table1[Salesperson], MATCH([@Company], Table1[Company], 0))

becomes

INDEX(Table1[Salesperson], 1)

and returns the first value from column Salesperson in Table1 which is "Kirti Whitt".

The Excel Table has now the corresponding salesperson next to the company name.

If you are looking for more examples on merging two data lists, check this post: Merge lists with criteria

Back to top

3. Create Pivot Table

If you are new to Pivot Tables don't freak out, they are so useful that I recommend you take time to get to know them better.

A Pivot Table allows you to quickly create totals based on conditions you specify, there is no need to build complicated formulas.

The speed the Pivot Table runs tasks is incredible. It is one of the greatest built-in features in Excel, in my opinion.

1. Select any cell in Table2, the table to the right.
2. Go to tab "Insert" on the ribbon.
3. Press with left mouse button on "Pivot Table" button and a dialog box is diplayed, see image below.
   
4. Press with left mouse button on OK button. This will create an empty Pivot Table in a new worksheet.

Back to top

4. Set up Pivot Table

The image above shows a Pivot Table to the left and the corresponding task pane to the right. The task pane contains a list of fields based on headers in your Excel Table, you can drag these fields to different areas below which are:

• Report Filter
• Column Labels (horizontally)
• Row Labels (vertically)
• Values

Follow the simple instructions below see how much each salesperson has sold and to what company.

1. Drag Price to Values area.
2. Drag Company to Row Labels area.
3. Drag Salesperson to Column Labels area.

The Report Filter lets you examine the data even deeper, simply drag a field to the Report Filter and a drop down list shows up above the Pivot Table. The drop down list contains items you may want to use as a filter condition.

Back to top

Get the Excel file

Merge-two-related-tables.xlsx

Back to top

Related tables category

Extract unique distinct values from a relational table
In this post, I am going to show you how to extract unique distinct values and duplicates using a formula, […]

Working with three relational tables
I will in this article demonstrate four formulas that do lookups, extract unique distinct and duplicate values and sums numbers […]

Lookups in relational tables
Excel 2010 has a PowerPivot feature and DAX formulas that let you work with multiple tables of data. You can […]

Excel categories

Comments (7)