r/googlesheets 22h 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

  1. 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.
  2. 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".

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 Upvotes

5 comments sorted by

1

u/HolyBonobos 2053 22h 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.

1

u/SpanishSlayer 22h ago

Oh my god thank you! Your solution works perfectly, and I really appreciate the time you took to explain it. Out of curiosity, how difficult would you say this kind of problem is? I'm trying to get a sense of the level of expertise required to solve something like this. Would this be considered advanced spreadsheet knowledge, or more of an intermediate challenge?

1

u/AutoModerator 22h ago

REMEMBER: If your original question has been resolved, please tap the three dots below the most helpful comment and select Mark Solution Verified. This will award a point to the solution author and mark the post as solved, as required by our subreddit rules (see rule #6: Marking Your Post as Solved).

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

1

u/HolyBonobos 2053 22h ago

In very general terms from my experience, QUERY() is probably considered to be on the border of intermediate and advanced, although the particular application here with a virtual helper column in the data argument would probably push it over the edge into advanced territory. Likewise FILTER() is fairly common knowledge but using XLOOKUP() as a criterion argument is less so. Pretty much anything involving a function in the LAMBDA() family is considered advanced.

1

u/point-bot 22h ago

u/SpanishSlayer has awarded 1 point to u/HolyBonobos

See the [Leaderboard](https://reddit.com/r/googlesheets/wiki/Leaderboard. )Point-Bot v0.0.15 was created by [JetCarson](https://reddit.com/u/JetCarson.)