+ Reply to Thread
Results 1 to 13 of 13

Shopping list from meal plan: Summing multiple values

  1. #1
    Registered User
    Join Date
    10-12-2017
    Location
    Derby, England
    MS-Off Ver
    2016
    Posts
    17

    Shopping list from meal plan: Summing multiple values

    Hey everyone!

    Basically as part of my weight loss journey I am trying to create a bit more of a savvy meal plan so I don't have to rely on the same few meals week-in week-out. I've done all the data for this but would like to link it into a weekly shopping list so I can order this easily without having to go through each recipe. Basically as of now I have two tables, one as a meal plan (Monday > Sunday; Breakfast, Snack, Lunch, Snack, Dinner). The second tab is the ingredients for each recipe, ordered as per the below (an example).

    So using the below example as the template for how the recipes are structured, I want to auto calculate the shopping list of ingredients based on the recipes in the meal plan (of which the names will be perfectly aligned to the name in Column A (meal).

    Any advice on how this can be done? My thoughts are to list all the ingredients in a third tab and then use some formula to look up the recipes in the meal plan and then sum up the ingredients but not sure how to create this as I don't think a vlookup / sumif will work...?


    Meal Ingredient Measurement
    Hey everyone!

    Basically as part of my weight loss journey I am trying to create a bit more of a savvy meal plan so I don't have to rely on the same few meals week-in week-out. I've done all the data for this but would like to link it into a weekly shopping list so I can order this easily without having to go through each recipe. Basically as of now I have two tables, one as a meal plan (Monday > Sunday; Breakfast, Snack, Lunch, Snack, Dinner). The second tab is the ingredients for each recipe, ordered as per the below (an example).

    So using the below example as the template for how the recipes are structured, I want to auto calculate the shopping list of ingredients based on the recipes in the meal plan (of which the names will be perfectly aligned to the name in Column A (meal).

    Any advice on how this can be done? My thoughts are to list all the ingredients in a third tab and then use some formula to look up the recipes in the meal plan and then sum up the ingredients but not sure how to create this as I don't think a vlookup / sumif will work...?


    Meal Ingredient Measurement
    Spaghetti Bolognese Spaghetti 500
    Spaghetti Bolognese Mince meat 250
    Spaghetti Bolognese Onion 2
    Spaghetti Bolognese Pepper 2

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,532

    Re: Shopping list from meal plan: Summing multiple values

    Do you really need to reinvent the wheel? There are lots of meal planner templates online that have this functionality - in fact, there was a thread only yesterday or the day before about one of them.

    If you DO want to reinvent the wheel, then you'll need to share your workbook with us. There are instructions at the top of the page explaining how to attach your sample workbook.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Registered User
    Join Date
    10-12-2017
    Location
    Derby, England
    MS-Off Ver
    2016
    Posts
    17

    Re: Shopping list from meal plan: Summing multiple values

    Absolutely no intention of re-inventing the wheel, but when I had a Google I couldn't really find one that worked well for me. I'll have a search of the forum though and see if I can find one, thanks!

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,532

    Re: Shopping list from meal plan: Summing multiple values


  5. #5
    Registered User
    Join Date
    10-12-2017
    Location
    Derby, England
    MS-Off Ver
    2016
    Posts
    17

    Re: Shopping list from meal plan: Summing multiple values

    Thanks a lot - pretty much what I was looking for except is there not a way to sum up the values instead of listing them (i.e. the attachment in that threat lists the requirements as like 250g, 250g, 250g) is there no way to sum it? Also I wanted categories Breakfast, Snack AM, Lunch, Snack PM, Dinner categories - is that as easy as adding them in as additional rows in the "Meal Planner" sheet?

  6. #6
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX, 365 for windows, Windows 10 Insider
    Posts
    6,549

    Re: Shopping list from meal plan: Summing multiple values

    As AliGw mentioned
    If you DO want to reinvent the wheel, then you'll need to share your workbook with us. There are instructions at the top of the page explaining how to attach your sample workbook.
    With expected results

    Also cross posted
    https://www.mrexcel.com/board/thread...alues.1167794/
    Wayne

    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  7. #7
    Registered User
    Join Date
    10-12-2017
    Location
    Derby, England
    MS-Off Ver
    2016
    Posts
    17

    Re: Shopping list from meal plan: Summing multiple values

    Is there an issue with posting it on more than one forum?

    I don't want to reinvent the wheel but the worksheet shared by Ali wasn't exactly what I am looking for.

    Came onto an excel help forum for help and feel like I'm committing a crime.

  8. #8
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX, 365 for windows, Windows 10 Insider
    Posts
    6,549

    Re: Shopping list from meal plan: Summing multiple values

    As requested
    then you'll need to share your workbook with us. There are instructions at the top of the page explaining how to attach your sample workbook.
    Crossposting and not knowing often means a member spends hes/her free time helping out with perhaps a lot of time spent , only to find that the solution has already been provided and frequently the original poster never returns

    can you post the example spreadsheet here and what the expected results you would like to see on the new TAB
    Last edited by etaf; 04-11-2021 at 12:42 PM.

  9. #9
    Registered User
    Join Date
    10-12-2017
    Location
    Derby, England
    MS-Off Ver
    2016
    Posts
    17

    Re: Shopping list from meal plan: Summing multiple values

    Ok fair enough for Cross posting, thanks for sharing the link.

    The draft worksheet is below should give basic idea of what I am trying to do - the 'Shopping list' tab is blank as don't know what the best way is to calculate it (my initial comment is only how I thought it may work).
    Attached Files Attached Files

  10. #10
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX, 365 for windows, Windows 10 Insider
    Posts
    6,549

    Re: Shopping list from meal plan: Summing multiple values

    Are you using Office 2016 ?
    or do you have office 365 , which can use UNIQUE / FILTER functions

    I'm thinking of a way , but its getting quite complicated to create the List

  11. #11
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX, 365 for windows, Windows 10 Insider
    Posts
    6,549

    Re: Shopping list from meal plan: Summing multiple values

    At the moment on Shopping list, we need to find all the UNIQUE ingredients
    So in A2 to A20 - I pull out the ingredients for the 1st meal ( I used 20 as thats cover the recipe with the most ingredients - BUT would need to relayout if a recipe in the future has more
    Then in A21 to A40 - the 2nd meal and so
    Now this is going to get huge
    5 meals per day & 7 days = 5x7 = 35
    Then pull out the quantity for each of those in B

    Now we have a huge list which includes spaces
    In H I have then Filtered and Unique - so we get a list of unique ingredients and no spaces, thats where I used the 365 UNIQUE / FILTER - otherwise it will need to be another index type formula

    Then using SUMIF() to quantify the volume and effectively a Shopping list
    BUT things like DASH/PINCH are not going to work

    And any slight change would mean probably a lot of formula change
    perhaps a simpler way - and other members may have a better approach , which will be less CPU dependant

    All i have done here is the 1st meal for Mon/Tues/Wed

    But as i day if you only use 2016 version, would need re-writing EDIT - I have updated to work with 2016 using an ARRAY see column O
    Attached Files Attached Files
    Last edited by etaf; 04-11-2021 at 02:36 PM.

  12. #12
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    17,898

    Re: Shopping list from meal plan: Summing multiple values

    Quote Originally Posted by PhilCox12 View Post
    Is there an issue with posting it on more than one forum?

    Came onto an excel help forum for help and feel like I'm committing a crime.
    As with most forums, we have rules, and one of them is that if you cross-post to another forum you must provide the link here. MrExcel has the same rule. If it is answered somewhere else we don't want to duplicate that effort here. Please take the time to review our rules. There aren't many, and they are all important.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  13. #13
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX, 365 for windows, Windows 10 Insider
    Posts
    6,549

    Re: Shopping list from meal plan: Summing multiple values

    looks like you had a solution in the other thread here , as no reply for 24hrs
    https://www.excelforum.com/excel-for...-function.html

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Meal Plan - hyperlink vlookup result to worksheet in same workbook
    By Cleetus09 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-18-2021, 10:33 AM
  2. Replies: 1
    Last Post: 06-29-2017, 02:11 AM
  3. [SOLVED] Complie a shopping list from meal planner??
    By Reddhotpoker in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 03-24-2017, 01:04 PM
  4. Meal plan to return Fat, Protein, etc.
    By geoponic in forum Excel General
    Replies: 4
    Last Post: 10-15-2016, 02:48 AM
  5. Summing Multiple Values of List Items
    By Billy Corman in forum Excel General
    Replies: 2
    Last Post: 11-11-2015, 08:28 PM
  6. Shopping list
    By Shawn1973 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-26-2013, 07:48 PM
  7. Help with calculations in a meal plan!
    By TASan in forum Excel General
    Replies: 7
    Last Post: 06-21-2012, 09:53 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1