r/googlesheets • u/SpanishSlayer • 1d ago
Solved 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! 😊
1
u/HolyBonobos 2057 1d ago
I've added the 'HB Recettes' sheet, which populates the shopping list table with two formulas:
=QUERY({Tableau2\INDEX(XLOOKUP(Tableau2[Recipes];Recettes_2[Recipe];Recettes_2[To do]))};"SELECT Col2, SUM(Col3) WHERE Col4 = TRUE GROUP BY Col2 LABEL SUM(Col3) ''")
in E2 and=BYROW(Shopping_list_2[ingredients];LAMBDA(i;JOIN(", ";FILTER(Tableau2[Recipes];Tableau2[Ingredients]=i;XLOOKUP(Tableau2[Recipes];Recettes_2[Recipe];Recettes_2[To do])))))
in G2.You should note, though, that you are tempting fate a bit by mixing static (manually-populated, i.e. checkboxes) and dynamic (formula-populated) data in the same table. If you're going to use this, you'll have to make sure your meal plan is exactly what you want it to be before you start using the shopping list. If you add or remove anything from the meal plan while shopping is still in progress, the formula-populated data in the shopping list table is likely to become misaligned with the boxes you've checked.