Calculate maximum "assemblies" that can be made from available "components"

[Knight]

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!

[ManxXL]

Is this a cross post? Looking at the filename of the attachment I suspect it will be.

[Knight]

Whoops! That explains why I couldn't find my topic anywhere when I looked on that site yesterday! That's what I get for doing it at 1am or thereabouts. No, this isn't a cross post, I just got mixed up which site I was on! Not sure if I actually have a user over there or not as this was the place I used to post. Thanks for the help and sorry for the confusion!

[ManxXL]

Thanks for clearing up the issue with the filename.

In Question 2,you mention being unable to change the lay out of the data. That is the very first thing I would do ! as it will be so much easier to handle in a table with all the components listed vs all the Products OK there will be a few gaps where an ingredient isn't required in a product, but compared to your sheet 2 it will make all the data easier to handle. But if this isn't an option I'm afraid I'll have to pass on this one. Sorry.