F&B Industry - How to convert 1 Menu into its components with Excel?

Question

I work in F&B industry and I have a problem that my company software could not handle, so I try to manage it in excel.

I have a menu that has many components within. So I try to convert the menu to its components (attached image)

I only have data : Chicken Soup 3 pcs

My database:

              Column A     | Column B       | Column C
              Chicken Soup | Water          | 100 ml
              Chicken Soup | Chicken Broth  |  10 ml
              Chicken Soup | Chicken Meat   |  50 gr

And I want the result to be :

              Water         300 ml

              Chicken Broth  30 ml

              Chicken Meat   150 gr

What I have tried:

I put all my database in 1 sheet, and use SUMIFS * (Qty of Components). The SUMIFS only to summarize all data matched with column A, and I multiply it with its components

The problem with my work:

1. I have so many recipes (of about 14k rows) and the formula that I tried takes too much time to be calculated (of about 10s per data entered).

2. If I put my database to be my worksheet, there will be so much rows resulted zero (because basically the daily data have only 5-10 items in average), which is, I have to sort the result again to have the actual data.

I also have tried with the Pivot Table, but Pivot Table will show all the data included the ZERO amount (but I only need data that only has value).

Answer 1

First set up a column that pulls the ingredients from the recipe list. This looks through the whole menu and finds all ingredients associated with that item.

=IFERROR(INDEX($B$1:$B$9,SMALL(IF($A$1:$A$9=F$1,ROW($A$1:$A$9),9E+99),ROW($B1)-ROW($B$1)+1)),"")

(make sure to press ctr + Shift + Enter since this in array formula

Then use an index match to find the quantity:

=INDEX($C$1:$C$6,MATCH(F2,$B$1:$B$6,0))*G$1&INDEX($B$1:$D$6,MATCH(F2,$B$1:$B$6,0),3)

You can adjust the recipe and/or the quantity and the list should update itself.