Recursive data extraction

[feaugcruz]

Hello guys.
I'm analyzing some unit price compositions and I need to extract labor hours, equipment hours, and materials.
Some compositions have only these 3 items, but most of them have auxiliary compositions, that sometimes have other auxiliary compositions and the process becomes recursive.
For example:
_ In 'column B' I have the code of the main composition;
_ In 'column C', I have a code for a labor, material, equipment, or other composition;
_ In 'column D', the quantity;
_ 'Column F", the types (A - equipment, B - labor; C - material, D or E - auxiliary composition).

My objective is to create a list where I have only the "main" code and the items A, B, and C. For this, the items D and E must be multiplied by the quantity from the main composition.
I thought of using 'lambda, filter, map, and others' because I know they support recursivity, but I'm not entirely familiar with them yet.
Can somebody help me?

Sorry for my English, it's not my main language.

 

[Rijnsent]

Hi there,
welcome to the forum. I have a hard time trying to understand what you're trying to achieve, but my first idea to put your data into a pivot table. Is that something you've tried? Next, if you look for example at line 10376: it shows column B/MAIN:0903845 , column C/AUX: E9524, column D: 1,0000 and column E: h (probably hours). What is your ideal outcome? The total of hours for E9524? Or is there another table to multiply with ("the quantity of the main composition")?
Cheers,
Koen

 

[feaugcruz]

Thanks, Koen, for reaching out to me.

I am working with "unit cost compositions"; these compositions contain inputs (which are the lowest possible level) and/or may include auxiliary compositions. These auxiliary compositions contain inputs and/or may include other auxiliary compositions, and so on.
My goal is to ensure that only inputs remain. To achieve this, I need to multiply the index of the main composition by the index of the auxiliary composition.
I have achieved this goal in the attached file, but I would like to obtain the result more quickly, without having to follow a step-by-step process.
About the attached file:
In the attached file, in the "DATA" tab, which is my data input, I have the main composition in column "A", the input or auxiliary composition in column "B", and in column "C", the index/incidence of the item in column "B" within "A".
Through the step-by-step process in the following tabs, I reached the desired result in the "END" tab, between columns "E" and "G".
I appreciate your help.

Loading Google Sheets