M-M - Relationship

[suby]

Hello All,

I have a excel workbook where  I have three worksheets PRODUCT , ORDER, DATA.

PROODUCT TABLE - Unique PROD ID

ORDER TABLE   - Unique ORDER ID

DATA TABLE - 

The relationship is one PROD ID can have multiple ORD and  One ORD can have multiple PROD ID.

How to solve this many to relationship join.

Attached the sample workflow.

[AmanSharma2205]

Hello Suby, 

To correctly understand the workflow, you want the details from all the tables to be in one table with unique values in each row. 

I have prepared something for you. 

Please let me know if it works fine for you.

[danilang]

Hi @suby 

When you have a M-M relationship, you need to apply joins sequentially to get to your final table. 

I've added in a bit more data to your input tables.  The Product now has a cost column, the Data table has a QTY column and Order has a customer field.  After the two joins are completed, you can start to perform calculations on a line, order or customer basis.  In my example I simply determine the line cost for each item on every order, but you can include total by OrderID, Customer, etc

Dan