How to list ingredients in a recipe when I have "single ingredients" as well as "complex ingredients"

[gagasp]

Background. A recipe is made of single ingredients as well as "complex ingredients". As an example if I make a lasagna I will use straight ingredients (i.e. parmesan, dry pasta) as well as "complex ingredients" (i.e. ragu sauce). "Complex ingredients" are made of single ingredients (i.e. tomato sauce, ground pork, ground beef). The recipe will call for certain amount of parmesan, dry pasta and ragu but I need to substitute in the ingredients required for purchase the individual ingredients that the "complex ingredient" is made of. Basically in the pivot table i need to be able to substitute the complex ingredient with the breakdown of the ingredients that are contained in the "complex ingredient". I haven't found any example of such a thing in my search. I didn't see any way around using pivot table which appears to be used for merging columns rather than doing more complex operation and I am left with trying building a macro. I am posting this message to see if anybody else came across a similar problem. Thanks in advance for any help on this matter
gg

 

[BSALV]

gagasp.xlsb
with a macro, 3 tables and a pivottable

Option Compare Text

Sub Shoppinglist()
     Dim Dict, aComplex, aList1
     Set Dict = CreateObject("scripting.dictionary")     'your shoppinglist
     Dict.comparemode = vbTextCompare     'is case insensitive

     With Sheets("blad1")     'this worksheet
          aComplex = .ListObjects("TBL_Complex").DataBodyRange.Value     'the complex ingredients
          aList1 = .ListObjects("TBL_List1").DataBodyRange.Value     'the initial shoppinglist with simple and complex ingredients
          For i = 1 To UBound(aList1)     'loop throug that list
               If aList1(i, 3) <> "x" Then     '3rd column not "x" = simple ingredient
                    Dict.Add Dict.Count, Array(aList1(i, 1), aList1(i, 2), aList1(i, 3), aList1(i, 4))     'copy then index, naam, unit and amount to the shoppinglist
               Else     'it's a complex ingredient
                    For j = 1 To UBound(aComplex)     'loop through the complex list
                         If aComplex(j, 1) = aList1(i, 2) Then     'same name
                              Dict.Add Dict.Count, Array(aList1(i, 1), aComplex(j, 2), aComplex(j, 3), aComplex(j, 4) * aList1(i, 4))     'add index, complex subname, complex subunit, complex amount multiplied by the initial amount
                         End If
                    Next
               End If
          Next

          i = Dict.Count     'number of lines
          With .ListObjects("TBL_List2")     'the extended list
               If .ListRows.Count Then .DataBodyRange.Delete     'start empty if it wasn't
               If i > 0 Then     'there are items
                    If i = 1 Then Dict.Add Dict.Count, Array("", "", "", "")     'bug when only 1 item, add an empty row with equal elements
                    arr = Application.Index(Dict.items, 0, 0)     'read items to an array
                    .ListRows.Add.Range.Range("A1").Resize(i, UBound(arr, 2)).Value = arr     ' write array to table
               End If
               .HeaderRowRange.EntireColumn.AutoFit
          End With

          ThisWorkbook.RefreshAll
          .PivotTables(1).TableRange1.EntireColumn.AutoFit

     End With


End Sub

gagasp.xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	complex name	ingredient	unit	amount		index	shoppinglist	unit	amount		index	shoppinglist	unit	amount		Shoppinglist
2	ragu sauce / 10	tomato sauce	can 500 ml	2		1	parmesan	kg	5		1	parmesan	kg	5		ingredients	unit	Totaal
3	ragu sauce / 10	ground pork	kg	1,5		2	dry pasta	500 g	10		2	dry pasta	500 g	10		dry pasta	1000 g	10
4	ragu sauce / 10	ground beef	kg	2		3	ragu sauce / 10	x	0,5		3	tomato sauce	can 500 ml	1		dry pasta	500 g	10
5	ragu sauce / 10	salt	spoon	1		4	ground beef	kg	15		3	ground pork	kg	0,75		ground beef	kg	20,6
6	ragu sauce / 10	water	liter	1		5	dry pasta	1000 g	10		3	ground beef	kg	1		ground pork	kg	4,2
7						6	ragu sauce / 10	x	2,3		3	salt	spoon	0,5		parmesan	kg	5
8											3	water	liter	0,5		salt	spoon	2,8
9											4	ground beef	kg	15		tomato sauce	can 500 ml	5,6
10											5	dry pasta	1000 g	10		water	liter	2,8
11											6	tomato sauce	can 500 ml	4,6
12											6	ground pork	kg	3,45
13											6	ground beef	kg	4,6
14											6	salt	spoon	2,3
15											6	water	liter	2,3
16
17
18
Blad1

Cell Formulas
Range	Formula
F2:F7	F2	=ROW()-1

 

[gagasp]

Thanks for the prompt reply and for the codes. I will try in my environment and report back. I am definitely happy that you understood my problem giving my rough description of it. On another note is it possible to download your spreadsheet and test it with your macro before I import it in my files?
Thanks again
gagasp

 

[gagasp]

So far it looks very good. I didn't know how to manage the xlsb and I didnt have the xl2bb add-in but now I am set. While playing around with it the pivot disappeared and I don't know what happened. I have reloaded it and I will test more to see what I did wrong and if I am able to replicate it. So far you are definitely my hero. As soon as I will migrate to my environment which is a fairly big one I will be able to declare victor and make you my HERO all capital.
Thanks again
gio

 

[gagasp]

I was able to move the table and and make modifications that could be used in my real environment however I need your last push as I need to have the "complex ingredients" in a different tab and also the pivot table. I am trying to do it myself but If you can give me a little push I would appreciate
thanks again
gio