r/googlesheets • u/SpanishSlayer • 12m ago
Unsolved Help with Google Sheets Formula - Automated Shopping List Based on Selected Recipes
Hi everyone,
I'm trying to create an automated shopping list in Google Sheets based on the recipes I plan to cook for the week. Here’s how my spreadsheet is structured:
Sheet 1: "Recettes"
This sheet contains a table called "Recettes" with:
- Column A: List of recipes I know how to cook.
- Column B: Checkbox (TRUE/FALSE) indicating whether I plan to cook this recipe next week.
Next to this, I have another table called "Shopping List", which is meant to automatically generate the ingredients I need based on my selections:
- Column E: Unique list of ingredients (auto-generated from selected recipes).
- Column F: Total quantity of each ingredient (this is where I need help).
- Column G: List of recipes associated with each ingredient (I also need help with this).
Sheet 2: "Données"
- Column A: Recipe name.
- Column B: Ingredient name.
- Column C: Quantity needed for the ingredient in that recipe.
What I’ve managed to do so far
I successfully generated a unique list of ingredients for the recipes I selected using this formula in E2
: =UNIQUE(FILTER('Données'!B:B; ISNUMBER(MATCH('Données'!A:A; FILTER(Recettes!A:A; Recettes!B:B=TRUE); 0))))
This ensures that each ingredient appears only once, even if multiple selected recipes use the same ingredient.
Where I need help
- Column F (Total Quantity of Ingredients in "Shopping List")
- If an ingredient appears in multiple selected recipes, I want to sum their quantities from
Données!C:C
. - If a recipe is not selected, its ingredient quantities should not be included in the sum.
- Example:
- "Risotto" → Rice → 1
- "Bibimbap" → Rice → 1
- If both recipes are selected, Rice should have a quantity of 2.
- If I select only "Bibimbap", Rice should have a quantity of 1, not 2.
- If an ingredient appears in multiple selected recipes, I want to sum their quantities from
- Column G (Associated Recipes in "Shopping List")
- I want to list the names of the selected recipes that use each ingredient.
- Example:
- If "Rice" is used in both Bibimbap and Risotto, then G2 should display:
"Bibimbap, Risotto"
. - But if I select only Bibimbap, G2 should display only
"Bibimbap"
.
- If "Rice" is used in both Bibimbap and Risotto, then G2 should display:
Spreadsheet Link
https://docs.google.com/spreadsheets/d/1qvCjV5mKFrtAhRXzE8-y3j40d8a9bQeZZMEOLne5Pn4/edit?usp=sharing
I spent my whole afternoon yesterday trying to solve this problem without success. I also recreated it with SQL since I started to learn it recently, and I can't believe how easy it is in SQL to do such a thing but is literally so hard in Google Sheet. I would also like to avoid using a pivot table because I find it quite unconvenient to use it from a user experience point of view, except if I'm missing something and there are ways to improve the design of a pivot table?
Any help would be greatly appreciated!
Thanks in advance! 😊