Hello again fellow Excel-ites. So, as the title indicates I've run into a problem. I'm going to ask this question in 3 parts because the answer to each part will be useful on its own even if a later part requires a "complete rework" of the solution -- (might be best to attempt your help from the end and work back?)
First - I'm trying to put together a formula for calculating the maximum number of total assemblies that can be made based on an available allocation of stock of the components and the QTY that is required to build each assembly
For instances - i.e. I have 3 dozen eggs, 4 cups of flour, 2 cups of sugar, and 1 cup of milk – in order to make 1 cake, I’ll need 4 eggs 1 cup of flour, ½ cup of sugar, and ½ cup of milk – how many cakes can I make max with the resources on hand? (Answer would be 2, but I need it to calculate 2 for all the lines)
(see Question#1 Tab for example)
Second - Now I’ve also got some things to make applesauce mixed in my inventory list, so I will need a formula that can differentiate based on the final product among the many components scattered throughout a list/array
(see Question#2 Tab for example)
*Also, my real data would not be sortable by "Product" as it is already pre-sorted by other criteria that I've established and is in a fixed format that cannot be moved about, so any solutions would not be able to include reorganizing them as part of the answer (though i wouldn't think that'd be the first thing offered here anyway)
Third - After this solution, I’ll also need to solve for a similar problem that I'm anticipating
Let’s say “Eggs” can be used for more than just “cake”, they can also be used for “brownies” and “pumpkin pie” and more future products we offer. How can I look up a table that has all the components listed followed by a “product” (so eggs would actually be listed several times, and would need to account for all possible instances)? (See "Sheet2" and "Question#3" tabs)
The results in yellow after 'eggs' in B2 is ideally what I'd like to see -- some sort of result like that concatenated in similar fashion if possible.
The final issue that I'm not sure I'm ready to tackle theoretically yet (need to re-envision exactly what I want to use this data for) is whether I can run a formula that would calculate the different maximums in the same cell (E2 on Question#3 tab) and in the same order as product (B2). So for instance, the cell would calculate and show "2, 3, 0" (as limited by the other items in the respective products, though I realize it'd be "9, 18, 12" if it was just eggs).
I know this is quite the handful, but I figure if someone can help with any portion of it, it'll help to give me the learning/step process to understand where I'm going wrong with the myriad of different formulas I've been trying all day. Thanks in advance!