1
I think this is a many-to-many relationship and Excel currently doesn't support this using Pivot Tables. Nonetheless, I wanted to see if there could be an alternative that could be achieved while using Excel 2016.
What I'm trying to do is to show a recipe name, and then calculate the total calorie, protein, carb, and fat count from the ingredients in the Nutrition Facts table.
This is the Nutrition Facts Table:
This is the Recipe Table:
This is the RecipeIngredientTable:
The RecipeIngredientTable takes a Recipe Name and finds the related ingredients.
What I want to do is use a Pivot Table, group by Recipe Name and show the carbs, protein, fat, and calorie count.
This is the closest that I've been able to make it:
As you can see the problem is that is adding all the data from the nutrition facts table.
These are the fields:
These are the relationships that I created:
I ended up using the RecipeIngredient Table as the main point of entry, in which I started mapping the values. How should I proceed in this case? In the worst case scenario I could create a manual table by using VLOOKUPs (defeating the purpose of a Pivot Table), and then create a Pivot Table that would group the Recipe Names (I'd also lose dynamism in the table – e.g., can't automatically reflect data change in the VLOOKUP table).
Edit: Thanks G-Man. Here's a link for my current Excel. Check the "Recipes", "Nutrition Facts" and "Meal Plan" sheets.
The RecipesIngredient table holds the foreign ids of each one of the other tables. This table (RecipesIngredient), is the one in charge of creating the relationships between the Nutrition Facts Table and the Recipes Table.
The amount column that you can see in the RecipeIngredient table is the number of times that item is required for the recipe (For example, 2 potatoes, 3 tomatoes, 1 tbsp of salt, etc.)
EDIT X2 I have moved the answer to its proper place.
Jose A
Posted 2017-09-08T15:24:39.913
Reputation: 111
(1) Pictures are nice (don’t delete the ones that you have already posted), but we prefer that you (a) post some sample data as text, so we can copy-and-paste it to our systems to experiment with it, and (b) explain it. I’m seeing a lot of undocumented “Id” fields flying around. Does the “IngredientId” field in the “RecipesIngredient” table relate to the “Id” field in the Nutrition Facts Table? If so, it might be better if you *called* it the “Ingredients” table and then explained that it contains the nutrition numbers for the ingredients. … (Cont’d) – G-Man Says 'Reinstate Monica' – 2017-09-08T19:07:28.507
(Cont’d) … (2) How does the “Amount” column in the “RecipesIngredient” table relate to the “Amount Per Serving” column in the first table? (3) Why is there a row in the “RecipesIngredient” table with blank “IngredientId” and “Amount” values? (4) Thanks for showing us what you’ve done so far (don’t delete that either), but it’s equally important to show us exactly what result you want. (Since this appears to be a computation-heavy job, … (Cont’d) – G-Man Says 'Reinstate Monica' – 2017-09-08T19:07:30.957
(Cont’d) … you had probably better also walk us through the calculations that lead to the numbers in your example result, even though they may be obvious to you.) (5) I’m a big fan of Excel, but even I am wondering whether it is the right tool for this job. You’re dealing with multiple linked tables; you might be better off with a database tool. At the very least, you should consider using VBA. – G-Man Says 'Reinstate Monica' – 2017-09-08T19:07:34.027
(6) Thanks for responding, and for updating your question. (7) My point about the “Amount” column was that a row in the “RecipesIngredient” table could have an “IngredientId” of 15 and an “Amount” of 3, and that would (I guess) equate to 27 pieces of Cheese Ravioli, which I believe is a little confusing (but I’m not a cook or a dietician). … (Cont’d) – G-Man Says 'Reinstate Monica' – 2017-09-09T13:53:31.767
(Cont’d) … (8) [SU] is a Question and Answer site. We prefer that answers be posted as answers (in the area below the question and these comments). It would be better if you would post an answer to your question (including, perhaps, details on your
VLOOKUPsolution) and remove that from the question itself. (But leave your recent clarifying explanations in.) – G-Man Says 'Reinstate Monica' – 2017-09-09T13:53:34.173@G-Man: Thanks!!! That's what I'm going to do!! I'm away from my machine, so I'll do what I can using my phone!! – Jose A – 2017-09-09T15:31:04.560