+ Reply to Thread
Results 1 to 4 of 4

Find, search, repeat functions in excel

  1. #1
    Registered User
    Join Date
    01-19-2019
    Location
    dillsburg, PA
    MS-Off Ver
    Office 365
    Posts
    18

    Find, search, repeat functions in excel

    Hello all,

    I am working on a problem in excel that I believe involves searching, matching, repeating, and finding. Below you will find a brief explanation of what I am trying to work on, however it makes much more sense when viewing the attached excel file.

    I am working on creating a document that automatically produces the ingredients necessary for certain recipes in a designated area of my workbook. On the far left of the workbook in Columns A-M, there is a table that allows me to select when I would like to eat each recipe. There is a list of 10 Recipes in Column A. If I would like to eat Recipe 1 on Day 1, there is a number 1 beside Recipe 1. If I would like it on Days 1,2, and 3, a "123" would be found beside Recipe 1 which you can see in Cell B3. Given that there are 3 days I would like to make Recipe 1, this means I would like 3 servings which you can see as the total in Cell C3.

    I also have the recipes broken down by when an individual could "Meal Prep". For instance, If they wanted to cook for Days 1-3, and then days 4-5, and lastly for days 6-7 in a given week, there would be three meal prep days. We will call these 1.1, 1.2, andn 1.3 which you can see in Cells B2, D2, and F2.

    My first question is to figure out how to create a list of recipes that I will be using for Meal Prep days (1.1, 1.2, 1.3) without manually inputting them based on my recipe selection. I am able to input the days in which I would like to create the recipes in Column B-L, and I would like my selection here to automatically generate a list of the recipes in Columns AA to AF. You can see in Columns AA to AF, I manually input the correct answers. Is there a way to find all of the recipes in Column B that contain numbers and then input the recipe names in Column AA as shown in the excel file? I would be able to use this same pattern for columns AB to AF as well.



    The second part of the problem is more complicated. In column Q I have a list of the recipes and their corresponding ingredients. In column P, I have the original amount of each ingredient per serving. In columns R to W, the number of servings present for each meal prep day are shown.

    What I would like to do is take the first recipe found in Cell AA2, and input that in Cell AJ2. In my example, this is "Recipe 1." I then would want to automatically populate the ingredients that correspond with Recipe 1 in Cells AK3-5 which you see in my chart (eggs, ham, bacon). In cells AJ3-5 the amount is correct because the original recipe calls for 2 eggs in column P, but and because we are working with 1.1, there are 3 servings in column R. This gives us 6 eggs in cell AJ3. If I were to change my input in cell B3 and made it blank, I would expect Cell AA2 to display Recipe 2, and cell AJ2 to display Recipe 2, with the ingredients and serving amount for Recipe 2 in AK 3-5 and AJ 3-5.

    I can repeat this process in Column AJ for each recipe in column AA. Then I will have all of the 1.1 recipes complete. I can then move on to 1.2, 1.3 and so on using the same idea to automatically populate the ingredients that I will need with the correct serving sizes based on the recipes that I select in the very far left table.

    Your help is greatly appreciated!!!
    Attached Files Attached Files
    Last edited by dsteele111; 02-06-2019 at 10:37 AM.

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Find, search, repeat functions in excel

    Sorry for off-topic interjection:

    Although there is no official rule regarding this behavior, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

    I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

    Thanks again for all your hard work here!

  3. #3
    Registered User
    Join Date
    01-19-2019
    Location
    dillsburg, PA
    MS-Off Ver
    Office 365
    Posts
    18

    Re: Find, search, repeat functions in excel

    On it!

    Thanks,
    David

  4. #4
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Find, search, repeat functions in excel

    Please try at

    AA2 drag across and down
    =IFERROR(INDEX($A:$A,AGGREGATE(15,6,ROW($A$3:$A$12)/(INDEX($B$3:$M$12,,MATCH(AA$1,$B$2:$M$2,))>0),ROWS(AA$2:AA2))),"")

    AJ3 drag down
    =IFNA(INDEX($R$1:$W$99*$P$1:$P$99,AGGREGATE(15,6,ROW($X$3:$X$99)/($X$3:$X$99=LOOKUP("z",AJ$1:AJ2)),ROW()-MATCH("z",AJ$1:AJ2)),MATCH(AJ$1,$R$1:$W$1,)),"")

    AK3 drag down
    =IFNA(INDEX($Q:$Q,AGGREGATE(15,6,ROW($X$3:$X$99)/($X$3:$X$99=LOOKUP("z",AJ$1:AJ2)),ROW()-MATCH("z",AJ$1:AJ2))),"")

    FYI
    your data layout is really hard to make formula.
    Attached Files Attached Files

+ 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. Help with Find and Search Functions
    By noobsesher in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-11-2018, 07:09 PM
  2. Replies: 18
    Last Post: 05-15-2018, 05:17 AM
  3. Replies: 7
    Last Post: 09-15-2015, 09:44 AM
  4. [SOLVED] Search within a column and repeat the search until all items are found
    By RANDY LIPOSKY in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 01-09-2014, 10:23 AM
  5. General Excel Search Tool for all the functions !
    By Studlyuan2003 in forum Excel General
    Replies: 2
    Last Post: 06-30-2013, 06:41 PM
  6. Excel FIND/SEARCH Find Name Hours Worked
    By staceynix in forum Excel General
    Replies: 8
    Last Post: 02-01-2013, 01:39 AM
  7. Repeat in functions?
    By Ruatha in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-06-2006, 06:51 AM

Tags for this Thread

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