Create a product tree using vba

[HSalles]

Hi,

I need to create a product tree and I would like to do this using vba, is that possible? I already have all the components so I just need to create relations between them. Please look at the exemple below.

To manufacture the "Bateria PICINW interna com conector" I need 4 components. I want to create the second table with the collum "Production capacity" which is the stock of "Bateria PICINW interna com conector" plus the minimum stock between the 4 components. 

Code	Description	Stock
4	BATERIA PICNIW INTERNA COM CONECTOR	56
4.1	ALOJAMENTO MOLEX 2P CLIKMATE PLUG	478
4.2	TERMINAL MOLEX CLIKMATE AWG 26-28	991
4.3	BATERIA INTERNA 3,7V 240 MAH	300
5	LAMINA INSTRUMENTADA PICNIW	175

Code	Description	Production capacity
4	BATERIA PICNIW INTERNA COM CONECTOR	56 + minimum(478,991,300)
5	LAMINA INSTRUMENTADA PICNIW	175

My first ideia was to create some loops and insert the values in a array so I could chose the minimum but I'm having some dificuties, could someone help me please?

[#2]

@HSalles In the attached I formatted the orig table as a table called "Products" and used this:

=MIN(FILTER(Products[Saldo/qtd],LEFT(Products[NIVEL],LEN(F2))=LEFT(F2,99),0))

basically filters the list based on having the same prefix

BTW, I realize this isn't VBA solution but anything using a cell formula can be replicated in VBA so the answer is yes.  I just prefer to avoid VBA when possible due to compatibility and security concerns.

[#3]

@mtarler
Unfortunately this isn't the solution. If you take a look at your worksheet, at the column max and row 2, the value should be 356 (56 that I have stocked plus 300 that I can manufacture). The same goes to the row 3, the value should be 175 (175 that I have stocked plus 0 athat I can manufacture).

Sorry if I didn't made my self clear, I think that it's better now.

[#4]

@HSalles updated

=XLOOKUP(F2,Products[NIVEL],Products[Saldo/qtd],0)+MIN(FILTER(Products[Saldo/qtd],(LEFT(Products[NIVEL],LEN(F2))=LEFT(F2,99))*(Products[NIVEL]<>F2),0))