• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Creating a food menu based on customer preferences and selected delivery days.

S

snakes

New Member
Hello, my name is Gary and I have just recently started using Excel regularly for work. I am a chef for a company that prepares packaged meals for home delivery.


Currently, this is my method of assembling the customized menus:

1. Look over customer delivery calendar to see who is signed up for the week.

(Some people get food every day, some only get it during the week days, and some will get just breakfast and lunch or lunch and dinner, etc.)

2. Open my menu template and enter the menu for the day by using a combination of Replace and Copy/Paste next to each individuals name who is signed up.

3.Check over customer dislikes and make changes individually to each dish for each person with a conflict.


With only 15 people on our menu, this is already quite a cumbersome task. This usually takes me about 2 hours total and leaves quite a bit of room for human error. I can't begin to imagine what it will be like with 50, 100, etc customers to sort through.


So basically this is what I'm looking for:


1. The ability to create a sheet/workbook that will compile a list of only the people who have selected meals for the day/week. I do not want people who are not on the menu for the day/week to be displayed.


2. Take this list of customers and then automatically fill in the menu for the day based on a sheet/workbook that has the dates and what the food is for each meal of the day (breakfast, lunch, dinner, morning snack, afternoon snack, evening snack)


3. Cross-reference this with a workbook that has each menu item and the ingredients each contains.


4. Then, if anyone has any of these ingredients of their dislikes list, the conflicting ingredients would show up in a cell next to the person/meal.


I'm not sure how to tackle this problem and I've had a hard time looking for answers online because I was unsure of what to search for. Is excel even the best option for this? Is there a different program better suited to this? Thank you to anyone who can help point me in the right direction.
 
Hi Gary ,


The best place to start is to upload your workbook , if you already have one ; it will give others an idea of your data layout ; a lot of times , if you start with the wrong data layout , it can make things more difficult ; getting the right data layout is half the job done.


You basically need a calendar schedule , a master list of customers , a master menu list , a master list of ingredients , a relationship table showing the ingredients for any menu , a relationship table of each customer's choice of menus ,....


I think this is a full-blown application , and Excel may or may not be the best option keeping in mind that you are talking of scaling it up to 100 or more customers.


The only point in favour of Excel is probably your familiarity with the software , and its ease of use , compared to Access.


Secondly , if you do things right , whatever you implement in Excel , can at a later stage be moved over to Access without too much of a bother.


Narayan
 
Narayan,


Thank you for such a quick response. I am currently making dummy copies of my files to upload and should have them available shortly. I will definitely look into Access.
 
Here are my dummy files.


http://www6.zippyshare.com/v/19278350/file.html (Dislikes List)

http://www6.zippyshare.com/v/12693592/file.html (Customer Calendar)

http://www6.zippyshare.com/v/40006665/file.html (Menu)

http://www6.zippyshare.com/v/85000706/file.html (Recipe Ingredients)
 
Hi ,


Thanks for the upload ; please give others some time to digest what you have already done ; hopefully you should get responses after an hour or two.


Narayan
 
Hi ,


I have downloaded your files , and would think that a lot of explanations will be required !


1. The RecipeIngredients.xlsx file has one sheet tab for each recipe , and a list of ingredients in column A on each tab. What is the maximum number of recipes that is possible ?


2. How do the various files viz. RecipeIngredients.xlsx , Dislikeslist.xlsx , KitchenInstructionTemplate.xlsx and the CustomerCalendarTemplate.xlsx tie up with each other ? Which data item from which file is used in which other place(s) ?


3. We should list down all the limits viz.


a. Maximum number of ingredients

b. Maximum number of recipes

c. Maximum number of ingredients per recipe

d. Maximum number of customers

e. Maximum number of dislikes

f. Maximum number of Kitchen Instructions

g. Maximum number of dates on the calendar i.e. will you want the calendar for just one week or can it go for more than 1 week ?


Narayan
 
Narayan,


1/3. The maximum number of recipes is unknown to me, we are continuously adding/removing recipes.


2. Currently none of them are tied together, and this is probably the part I'm having the most difficulty with. I want to be able to take KitchenInstructionTemplate.xlsx and replace "Day 1" and "Day 2" with the actual dates. Then, replace "Breakfast 1", "Breakfast 2", "Dinner 1", etc. with the names of the recipes from RecipeIngredients.xlsx Then, somehow have it check the info in Dislikeslist.xlsx against the selected recipes from RecipeIngredients.xlsx Any ingredients that match from the two lists show up in the "Special Instructions" column in KitchenInstructionTemplate.xlsx


3. a. I am unsure of the maximum number of ingredients, we are constantly adding and removing based on the season/availability.

b.The customer calendar would ideally be one month long.

c. The maximum number of ingredients per recipe is 20.

d. The maximum number of customers is unknown. We are currently at 15 but are gradually adding more and more.

e. I do not know the maximum number of dislikes, for every ingredient we use there is a chance someone is not going to like one of them.

f. Kitchen Instructions are currently done for two days at a time. I would like to be able to do them for the whole month but keep them in the two-day format.

g. The actual CustomerCalendarTemplate.xlsx would be one month long.


Thank you again the help!


-Gary
 
http://www41.zippyshare.com/v/24700021/file.html


This is an example of how I want the finished Kitchen Instructions to look.


1. "Customer Name" would have the names of people who are signed up for the meals for that day, that information would be taken from the CustomerCalendarTemplate.xlsx, which would actually be one month long instead of one week.


2. Rows in bold are ones that contain information in the "Special Instructions" column. Currently I am inputting the data in this field by hand, but I would like it to come from a comparison of the ingredients for each "Food Item" and "Protein" (which would both come from RecipeIngredients.xlsx) against the ingredients listed for each "Customer Name" on Dislikeslist.xlsx
 
Hi ,


Thanks for the latest upload. My comments are :


1. Since the dates are available only in two of the uploaded workbooks , I assume that the KitchenInstruction workbook will get its data from the CustomerCalendarTemplate workbook.


2. From what I can see , the KitchenInstruction data is ordered date-wise , Meal-wise , Customer-wise ; is this order fixed ?


3. For each day , you have provided for the following meals : Breakfast , Lunch , Dinner , Morning Snack , Afternoon Snack and Evening Snack. Will this be fixed , or can this change from day to day , from customer to customer ?


4. The example KitchenInstruction is for just one customer ; if you were to do this for even 15 customers , then the 150 rows will probably be multiplied 15 times , except for the changes in the recipes from one customer to another.


5. Can you explain what the CalendarTemplate does ? What are the cells marked "x" ? What about the cells which are unmarked ? Does "x" mean the customer will have the meal on that date ?


6. As far as I can understand , what you need from this application is the KitchenInstruction for any given period , for which references will have to be made to the CustomerCalendarTemplate , the DislikesList and the RecipeIngredients files. Is this correct ?


Narayan
 
Narayan,


1. Yes, the workbook KitchenInstructions would get it's dates from CustomerCalendarTemplate.


2. If you mean the order in which the data is sorted, then yes. If you mean the order of the coloums, then no. I need to group all of the same "Meal"s together then "Food Item"s together so they can be prepared together by the kitchen staff. Columns can be moved left or right to help the functionality of the VLOOKUP formula.


3. The meals: Breakfast, Lunch,Dinner, etc are fixed. But, some people do not get all of these meals every day, which is represented in the CustomerCalendarTemplate workbook.


4. Actually, the KitchenInstructions2-25example workbook is for 13 customers, sorry for the confusion.


5. Yes, "X" means that person is getting that meal on that date. Cells with no data means they are not getting that meal.

"B"= Breakfast "M"= Morning Snack "L"=Lunch "A"= Afternoon Snack "D"= Dinner "E"= Evening Snack


6. Yes.


-Gary
 
Hi Gary ,


I think so ; most of it may be possible using formulae , but some amount of VBA might be required. Give me some time to work on it.


Narayan
 
You must log in or register to reply here.
Back
Top