How to Create Relationship in Excel with Duplicate Values

On many occasions, we might be in need of creating Relationships in Excel with Duplicate values because datasets have a common column that can be used. But if somehow both the tables have Duplicate values, then the process becomes quite difficult to execute. If you are curious to know how you can create multiple worksheets from a list of cell values, then this article may come in handy for you. In this article, we discuss how you can create a relationship with duplicate cell values in Excel with an elaborate explanation.

How to Create Relationship in Excel with Duplicate Values: 2 Easy Ways

We will use the following dataset in order to create a relationship between the two tables in Excel with Duplicate values. Both of the data set has Duplicate values. At the same time, the common column is the Product Id. The Relationship will be based on this common column.

1. Appending Common Table

As we have Duplicate values in both tables, we can’t create a Relationship among them in Excel. Since this Relationship will require a one-to-one Relationship and Excel does not support it for now. We will import the tables on the power query and append them together. After then, we will remove the Duplicates from the appended table. In the final stage, we create Relationships with this new table with the original two tables. This is how we are going to create a Relationship in Excel with Duplicate values.

Steps

We have two separate tables between which we need to create a Relationship, based on the common column Product Id.
But generally, in order to create Relationships among tables, we need to have at least unique values in one of the tables.
But here we have Duplicate values in both of the table’s Product Id columns.

To demonstrate why we can’t create a Relationship among the presented table follow the below steps where we will try to make a relationship between these two tables.
For this, we need to first create a table first, select the range of cells B4:C16, and then click on the Table command from the Insert tab.

Then in the Create Table dialog box, re-confirm the table range.
Check the My table has headers checkbox.

To demonstrate why we can’t create a Relationship among the presented table follow the below steps where we will try to make a relationship between these two tables.
For this, we need to first create a table first, select the range of cells B4:C16, and then click on the Table command from the Insert tab.

Then in the Create Table dialog box, re-confirm the table range.
Check the My table has headers checkbox.

After we have done creating the table, we will create a pivot table out of the two recently created tables.
Select the first table and then go to Insert tab > PivotTable > From Table/Range.

In the next dialog box, click on the New Worksheet.
And then tick the Add this data to the Data Model checkbox.
Click OK after this.

Then select the second table and then go to Insert tab > PivotTable > From Table/Range.

In the next dialog box, click on the New Worksheet.
And then tick the Add this data to the Data Model checkbox.
Click OK after this.

In the new worksheet, you will notice that there is a PivotTable Fields menu.
The side panel is occupied by both tables. To observe both tables, you need to click on the All section.

Then we drag the Product Id field to the below Filter area.
Furthermore, drag Revenue and Profit from Table2 and Cost and Quantity from Table3.
You would notice that the Revenue and Profit are showing the values that they are supposed to show. But the Quantity and Cost column is showing the same values for each row. Which is quite abnormal.
That’s because there is not any relationship between the tables.
To resolve this issue, we need to create a Relationship among them first.

We put our cursor at any point in the table. Now enable the Power Pivot feature in Excel and then click on the Power Pivot tab.
Then click on the Add to Data Model.

In the data model, we can see that both tables are already loaded.

Switch to the Diagram view by clicking on the Diagram view in the Home tab.

Then from the Design tab, click on Create Relationship.

In the Create Relationship dialog box, select Table2 and Table3 in the dropdown box.
Below, you will notice that there is a warning sign.
The summary of that warning sign is that we have Duplicate values in both columns, and we won’t be able to create a Relationship as this will require many to many type of Relationship. Which is not supported at this moment.
Click on Cancel.

Now we are going to open a power query and from there we will append two separate tables.
Using that appended table we are going to create Relationships that will help us to obtain the desired results.
Select the Table2.
From the Data tab, go to Get Data > From Other Sources > Form Table/Range.

You will notice that there is a new window.
And in that window, we got Table2 is now loaded.

Do the same thing for Table3.

Right-click on Table2, and then from the context menu, click on the Add as New Query.

There will be a new query listed below.
Then right click on the List and from the context menu, click on To Table.

In the following window, do nothing and click OK.

Right-click on Table2, and then from the context menu, click on the Add as New Query.

There will be a new query listed below.
Then right click on the List and from the context menu, click on To Table.

In the following window, do nothing and click OK.

Then we got two separate columns that are now excluded from their table, in different queries.

Now we are going to Append the Product Id and Product Id (2).
Select table Product Id and then click on the Append Queries from the Combine group.
Then from the drop-down menu, click on the Append Queries.

Right after this, the below window will appear.
In that menu, click on the Product Id (2), from the Table to append option.
Click OK after this.

Product Id (2) table now appends with the Product Id table successfully.
In the newly appended table, right-click on the table, and select Remove Duplicates.

Now all of the Duplicates are now removed.
Close the power query table. Click on Keep on the way out.

The newly created tables will load in separate sheets.
Heed to the Product Id named worksheet.
Click on the Add to Data Model, from the Power Pivot tab.

The new table is now available at the Power pivot.
In the Design tab, click on Create Relationship.

Then in the Create Relationship window, select Table2 and Product Id.
Click OK after this.

In the diagram view, we can see that there is a Relationship created between Table2 and Product Id.

Then in the Create Relationship window, select Table3 and Product Id.
Click OK after this.

In the diagram view, we can see that there is a Relationship created between Table3 and Product Id.

Now we go back to the original pivot table report.
In that report, remove all the existing fields and enter the fields shown below.
In the beginning, add the Product Id field in the Rows.
Then drag the Revenue to the values.
You can see similar issues like before are present.
At the same time, you can notice there is a yellow notice area, which indicates the presence of a Relationship among the tables.
Click on the Auto-Detect.

After clicking Auto Detect, the below dialog box will appear.
It will take some time to link the Relationship
After that, the Relationship will be found and linked.
Click on the Close.

Now drag Revenue, and profit from Table2_2.
And drag the quantity and cost from Table3_2.
If there is any Relationship yellow warning as stated before, click Auto Detect as before.
Now you can see that the pivot table is now showing values appropriately, according to the Relationship we created earlier.

2. Deleting Duplicate Values

In this method, we will delete Duplicate cell values of both of the tables and then we will create a one-to-one relationship among them. This elimination of the Duplicate values will be achieved by using the Power query commands. With tables now free from Duplicates, we can now create the Relationship in Excel with Duplicate values.

Steps

We have two separate tables between which we need to create a Relationship, based on the common column Product Id.
But generally, in order to create Relationships among tables, we need to have at least unique values in one of the tables.
But here we have Duplicate values in both of the table’s Product Id columns.

For this, we need to first create a table first, select the range of cells B4:C16, and then click on the Table command from the Insert tab.

Then in the Create Table dialog box, re-confirm the table range.
Check the My table has headers checkbox.

For this, we need to first create a table first, select the range of cells B4:C16, and then click on the Table command from the Insert tab.

Then in the Create Table dialog box, re-confirm the table range.
Check the My table has headers checkbox.

Now we are going to open a power query and from there we will delete Duplicate values from the table.
Select the Table1.
From the Data tab, go to Get Data > From Other Sources > Form Table/Range.

You will notice that there is a new window opened.
And in that window, we got Table1 is now loaded.

Do the same thing for Table2.

In Table1, right-click on the Product Id, and select Remove Duplicates.

Now all of the Duplicates are now removed.

Do the same for Table2.
In Table2, right-click on the table, and select Remove Duplicates.

After removing Duplicates, the table will look like the one below.

Then we click on the Close & Load To from the Home tab.

The table will load in different sheets.
We are going to add them to the pivot table
Select the loaded Table1 and then go to Insert tab > PivotTable > From Table/Range.

In the next dialog box, click on the Existing Worksheet, and the location of the table.
And then tick the Add this data to the Data Model checkbox.
Click OK after this.

Select the loaded Table2 and then go to Insert tab > PivotTable > From Table/Range.

In the next dialog box, click on the Existing Worksheet, and the location of the table.
And then tick the Add this data to the Data Model checkbox.
Click OK after this.

After this, drag the Product Id from Table1_2 to the Rows.
Furthermore, drag Revenue and Profit from Table1_2 and Cost and Quantity from Table2_2 to the values.
The value of the cost and quantity columns in the pivot table are abnormal. They are showing the same values for each and every row.

To resolve this, click on CREATE on the Yellow Warning box.

In the Edit Relationship window, choose Table2_2 and Table 1_2 in the first and second dropdown menus.
Then select Product Id in both the Column (Foreign) and Related Column (Primary) dropdown menu.
Click OK after this.

After clicking OK, you will notice that the Relationship now solved the issue.
The Cost and Profit columns are now showing accurate values.

Things to Remember

While creating Relationships between two tables in the second method, the common column that would be in the Rows field should be from the Related Column (Primary) table. Otherwise, it will create wrong values in the pivot table
Ticking on the Add data to the data model is a must. Otherwise, it won’t be possible to make connections among them.

Download Practice Workbook

Download this practice workbook below.

Create Relationship with Duplicate Values.xlsx

Conclusion

To sum it up, the issue of how we can create a Relationship in Excel with Duplicate values is answered here in 2 different ways.

For this problem, a workbook is available to download where you can practice these methods. Feel free to ask any questions or feedback through the comment section.

<< Go Back to Create Relationships in Excel | Data Model in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!