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 related table
In a previous post I described how to do lookups in a related table. In this post I am going [โฆ]
Working with three related tables
I have written a few posts about two related tables and today I am going to show you how to [โฆ]
Lookups in a related table (array formula)
The "new" excel 2010 powerpivot feature and DAX formulas lets you work with multiple tables of data. You can connect [โฆ]
Use Conditional Formatting to do lookups in related tables
It can be really hard trying to follow a lookup in related tables. I will show you how to use [โฆ]
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 [โฆ]
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.