Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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.


Download Practice Workbook

Download this practice workbook below.


2 Easy Ways to Create Relationship in Excel with Duplicate Values

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.

How to Create Relationship in Excel with Duplicate Values


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.

Appending Common Table to Create Relationship in Excel with Duplicate Values

  • 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.

creating table from ranged dataset to create relationship with duplicate values in Excel

  • 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.

make table from dataset

  • 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.

add table to the pivot table

  • 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.

add table to the pivot table

  • 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.

Appending Common Table to Create Relationship in Excel with Duplicate Values

  • 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.

open data model to add table to create relationship among tables

  • 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.

creating relationships among tables

  • 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.

add data to data model to create relationship

  • 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.

 Appending Common Table to Create Relationship in Excel with Duplicate Values

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

 Appending Common Table to Create Relationship in Excel with Duplicate Values

  • 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.

Appending Common Table to Create Relationship in Excel with Duplicate Values

  • 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.

● In the following window, do nothing and click OK.

  • 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.

relationship of new table create with the table 2 and table 3 whose have duplicate values

  • 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.

Relationship in between two table created with similar values

Read More: Create Entity Relationship Diagram from Excel (with Quick Steps)


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.

Deleting Duplicate Values to Create Relationship in Excel with Duplicate Values

  • 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.

insert table from ranged dataset in order to create relationship with duplicate values in Excel

  • 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.

make table from ranged dataset

  • 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.

add table to the data model to make Relationship in Excel with same Values

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

table with duplicate values are loaded in the data model to create relationship

  • Do the same thing for Table2.

insert table to data model to create Relationship with duplicate Values in Excel

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

remove duplicate value to make relationship

  • 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.

table after their duplicate values are removed for producing relationship

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

close and load to produce Relationship in Excel with similar Values

  • 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.

add second table with has duplicate values to create relationship in excel

  • 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.

add intermediate table with duplicate Values to data model with duplicate Values

  • 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.

Relationship detected in pivot table field for duplicate values

  • 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.

Relationship in between two table created with duplicate values in Excel

Read More: How to Create Data Model Relationships in Excel (3 Ways)


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.

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. Any suggestion for the betterment of the Exceldemy community will be highly appreciable


Rubayed Razib Suprov

Rubayed Razib Suprov

Hi, I am Rubayed Razib Suprov. Graduated from the Bangladesh University of Engineering Technology in Naval Architecture and Marine Engineering department. I joined Exceldemy on March 2022. I have a keen interest in both fluid dynamics and data analysis. I try to teach people what I learned so far about the Excel tool to date, and hope I can make some positive impacts.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo