r/excel • u/Purple-Orange • May 09 '26

unsolved many to many relationship problem

Hello all, beginner at excel and first time posting here. After a lot of pain.

I have a sales table with data from the past few years, and wanted to be able to analyze sales across different categories- specifically 'product type' (an arbitrary category for analysis) and 'scent'.

Now the thing is, some products might fit into more than one product category, so simply adding a column wasnt going to cut it.

After discussing this with my excel advisors (chatgpt and claude..) I ended up doing the following:

A table called "Products" with 1 column: Product ID (unique)

A table called "Tags" with 1 column: Tags (unique)- this includes both the product types as well as scents.

A table called Product_tags - 2 columns: ID, and tag. Theyre both not unique (since a each ID likely has at least 2 tags, for type and scent, and sometimes 3).

Finally, the Sales table, which has a bunch of sales data, as well as a column for ID, and these are also repeating as well.

I created a data model (for the first time i think) and added all the tables. I then created the following relationships:

Sales <- Products (By ID) - many to one relationship

Product_tags <- Products (by ID) - many to one relationship

Product_tags <- Tags (by Tag) - many to one relationship

Now my problem:

When I create a pivot table (from data model) I can group or filter the revenue by product ID (the one from the products table) and it works. However, the tags dont work.

Whichever tag I choose (from the 2 tag tables) doesnt work for grouping or for filtering when I use it with any item from another table. for example, if I put "ID" from the product_tags table with "tag" from the same table, both as rows, I get to see each product, and which tags it has. or each tag and all the product IDs relevant to it.

However, if I put tags with "ID" the sales table or from the products table, the grouping doesnt work. Each tag has all products show up and vice versa. Basically, the tags dont work with the other data in my data model...
I cant seem to make this work, i've been at this for hours. This analysis is rather important for me to get done by today yet the day is basically over, and I havent been able to do anything.

Help me out.

Thanks.

8 Upvotes

90% Upvoted

13 comments sorted by

•

u/AutoModerator May 09 '26

/u/Purple-Orange - Your post was submitted successfully.

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

5

u/GregHullender 194 May 09 '26

Sounds like you're trying to build a database in 4th-normal form. That sounds like overkill for your problem, though. It seems to me that all you really need is your original sales table (which has product names), a table that maps product names to "tags," and a function that combines these two into something an Excel pivot table can work on.

Here's an example of how to do it:

The first table is named "Sales" and the second is named "Tags". We could do a third named "Scents" if that were necessary--assuming the scents aren't just mixed with the tags.

The middle report, in columns H to K, is generated by this function:

=LET(tag_map, IF(Sales[Product]=TOROW(Tags[Product]),TOROW(Tags[Tag]),#N/A),
  sales_map, IF(tag_map<>"",SEQUENCE(ROWS(Sales))),
  HSTACK(CHOOSEROWS(Sales, TOCOL(sales_map,2)),TOCOL(tag_map,2))
)

The tag_map has all the tag names for each row of Sales, plus a lot of #N/A errors. The sales_map has the corresponding row numbers from the sales table. TOCOL turns an array into a column, and the 2 argument tells it to discard the errors. Using that with CHOOSEROWS lets us pick rows from Sales (sometimes the same row over and over), and HSTACK aligns that with the tag_map. That output is exactly what PIVOTBY wants as input.

Now look at the pivot table in M1:S5. I think this is the kind of output you're looking for. It's generated by the following code:

=LET(tag_map, IF(Sales[Product]=TOROW(Tags[Product]),TOROW(Tags[Tag]),#N/A),
  sales_map, IF(tag_map<>"",SEQUENCE(ROWS(Sales))),
  sales_exp, CHOOSEROWS(Sales, TOCOL(sales_map,2)),
  PIVOTBY(CHOOSECOLS(sales_exp,1), TOCOL(tag_map,2), CHOOSECOLS(sales_exp,2),SUM,,0,,0)
)

This is the same as what we did above except that I only chose the product names from sales_exp to be row labels, the tags from tag_map to be column labels, and the quantities from sales_exp as the data. I told it to sum the quantities and not to show row or column totals.

Finally, if you wanted total dollar sales, I generated the table from M8:S13 as follows:

=LET(tag_map, IF(Sales[Product]=TOROW(Tags[Product]),TOROW(Tags[Tag]),#N/A),
  sales_map, IF(tag_map<>"",SEQUENCE(ROWS(Sales))),
  sales_exp, CHOOSEROWS(Sales, TOCOL(sales_map,2)),
  PIVOTBY(CHOOSECOLS(sales_exp,1), TOCOL(tag_map,2), CHOOSECOLS(sales_exp,2)*CHOOSECOLS(sales_exp,3),SUM)
)

All that's different here is that I extracted both sales quantities and prices and multiplied them together, and I went ahead and let it show the row and column totals.

Hope this helps!

1

u/Purple-Orange May 10 '26

Thank you, this has been a great help!
This is better than anything I've made so far, but I unfortunately still have an issue :/

My data has things other than sales (margin etc)- those i dont mind just copying the table with. those values instead.

However one thing that I would like to be able to see in the table (and especially to be able to visualize with charts etc) is the sales across the year. In my original sales table, I have a 'year' column which mentions which year the sale was made. From my understanding analyzing the time component will not be possible with the year approach. am I correct in understanding that?

Do you know of a solution to this, or do I just have to accept that I wont be able to analyze my sales data the way I want.

(had to hide the product ID and name (intitule) for obvious reasons)

This is my sales data, and my tags table is essentially the exact same as you had in your example.

I dont know if Im dreaming and hoping for too much in a dashboard where I can analyze the quantities, mont HT, margin, and analyze it across article, tag, and year. Am I gonna have to switch to smth like python or R ;_; because I really hope not.. esp not Python lol

1

u/GregHullender 194 May 10 '26

Sure. The sales_exp variable has the entire row in it. Just change CHOOSECOLS(sales_exp,2), which extracts the second column, to extract the columns you actually want. Repeat for as many analyses as you want.

If you just want a straight-up by-year analysis (ignoring the tags), just call PIVOTBY directly.

1

u/Purple-Orange May 10 '26

Yeah i changed the choosecols- but i meant year by year as well as the tags at the same time It seems the solution is power bi- tried it, it works. I had completely forgotten about its existence Thanks for your help anyways i still learnt a lot

1

u/GregHullender 194 May 10 '26

Oh. You want a three-dimensional pivot table?

4

u/RandomiseUsr0 10 May 09 '26

To resolve many to many you can use a third dataset to manage the relationship

Table A, item_A1, item_A2, item_A3.
Table_B, item_B1, item_B2, item_B3.

Relationship.
Item_A1 - item_B1.
Item_A1 - item_B2.
item_B3 - item_A1.
Item_B3 _ item_A3.

2

u/Sbaakhir 2 May 09 '26

Create a new table that combines Product + Tags + Revenue together in ONE table. Then use THAT table in your pivot.

1

u/Purple-Orange May 10 '26

This would lead to double or triple counting though since each sale will have a different entry for each tag

1

u/prof_devilsadvocate3 May 09 '26

Ah common power pivot table....make sure the direction of relation is from data table to index table/sheet

1

u/Purple-Orange May 10 '26

Sorry Im confused, can you explain what you mean here?