I am trying to show the grocery shopping list for my nutrition program app.
I have the ingredient list in different tab for each meal: breakfast, lunch, snack and dinner.
These tabs have the list of the ingredients for all recipes and the quantity that is different for each user.
I would like to create a unique tab “grocery shopping” that gathers all the ingredients for all meal tabs, remove the duplicate by summing the quantities.
Is there a way to do that?
(I would do it on excel if only the quantity were not specific to each user)
Sorry for the unclear explanation hehe
Let me try to be clearer
So, the plan is for 14 days, each day have 4 meals (breakfast, lunch, snack, dinner), each meal are made of 3 macros (Proteins, Carbs, Fats)
I had to divide the list of the ingredients in different tabs, in order to adapt the quantity based on the user (it’s a whole other story :p)
Now, I would like to create a section with the grocery shopping list: the list of all ingredients and the quantity that needs to be bought for the 14 days (I actually would like to divide the list in 2, for the first 7 days and the last 7 days, but let’s go step by step :p)
As you can see on the previous screenshot, some ingredients are present in multiple recipes.
So I don’t know how to list all the ingredients from all recipes in one tab and show the global quantity is needed for the whole program.
For instance, on the previous screenshot you see that for 2 recipes you need buckwheat flour 200g
In the grocery shopping list, I expect to see listed “Buckwheat flour” once, with the quantity 400g.
Note: the same ingredients is repeated in different recipes in the same tab (same meal - breakfast, lunch etc…) but also in different meals (different tabs)
Okay, so the usual way to do that would be to create a relation, and then do a roll up through that relation to get the sum of all quantities. But because you’ve split your ingredients across multiple tables - did I say that probably wasn’t a good idea? - then that complicates things somewhat. You’re probably going to have to create multiple relations, one for each table/ingredient pair. Do you at least have a “master” sheet that lists all ingredients in a single table? If you have that, you could create a series of relations that links each ingredient to each of the other sheets, do a sum through those relations, and then add those all up with a math column. And then you’d have to relate that back to your user profiles to get the totals for each user.
But gee, that’s a lot of work, and it really sounds like you have a lot of redundant data. I think the best advice I could give you would be to have a serious think about re-structuring your data, with a goal of just having two main tables. One that lists all your recipes, and another that lists all your ingredients. And then create proper relations between the two. Once you had that setup, then I think it would be fairly straight forward to get what you want.
So the reason why I splited the tabs it’s because in my user tab I have the amount of g of each macros the user can eat per meal.
Ex: 40g of Carbs at Breakfast / 30g of carbs at lunch / 50g of carbs at snack / 45g of carbs at dinner .
Same of each macros.
So I could have put everything in one tab, but I would have had to create multiple math column as the formula to calculate the ingredient’s quantity for the recipe for the user is different per macros and per meal.
In the case where I wouldn’t change my data structure, I do have the list of all ingredients. I put it in my “grocery list” tab, and I tried to create relation columns to report all the quantities of this ingredients from other tabs. But I couldn’t find the way to do it:
Here’s the tab showing all the ingredients for carbs for lunch:
And here’s the test I was doing trying to put all the quantities of Basmati rice listed in that tab… But I don’t know what value to select to show all the “Quant. Recipe”
- then that complicates things somewhat. You’re probably going to have to create multiple relations, one for each table/ingredient pair. Do you at least have a “master” sheet that lists all ingredients in a single table? If you have that, you could create a series of relations that links each ingredient to each of the other sheets, do a sum through those relations, and then add those all up with a math column. And then you’d have to relate that back to your user profiles to get the totals for each user.
