Merge two related tables
This post demonstrates how to merge two related tables before creating a pivot table. A pivot table is limited to one table (data source) and I want to calculate the sales figures for each salesperson. The table to the right shows the product, price and the company.
The table to the left shows the salespersons and their companies. A salesperson may have multiple companies.
Add column to table
Let's look for data in the first table [Table1].
 Select cell G2

Type this formula:
=INDEX(Table1[Salesperson], MATCH([@Company], Table1[Company], 0))
 Press Enter
Now the table has 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
Create pivot table
Set up pivot table
 Drag Price to Values area
 Drag Company to Row Labels area
 Drag Salesperson to Column Labels area
Download excel file
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 [โฆ]
Excel 2010 has a PowerPivot feature and DAX formulas that let you work with multiple tables of data. You can [โฆ]
Highlight lookups in relational tables
This article demonstrates a worksheet that highlights lookups across relational tables. I am using Excel defined Tables, if you add [โฆ]
Search two related tables [VBA]
This article demonstrates a macro that automatically applies a filter to an Excel defined Table based on the result from [โฆ]
Search related table based on a date and date range
I will in this article demonstrate how to search a table for a date based on a condition and then [โฆ]
7 Responses to โMerge two related tablesโ
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form
Or you could simply use PowerPivot or the Excel 2013 DataModel, and build a pivot from two linked tables.
Edouard,
I forgot to mention that!
Hi Oscar,
I failed to find right article in your blog and therefore I want to ask you in newest post. So I have table similar like this:
A 5
B 2
C 1
D 4
Is it possible with formula to generate list like this:
A
A
A
A
A
B
B
C
D
D
D
D
Thank you in advance!
BatTodor,
Read this post: Repeat values
[...] BatTodor asks: [...]
You are genius Oscar! :)
Thanks a lot!
Best regards
Todor
Hi Oscar,
I got two pivot tables from two sets of data ie Pivot table1 from data set1 and pivot table from data set2. I would like to know whether it is possible to control these pivot tables with a single slicer. The two data sets have common fields.