I have a file with hierarchical structures of product assemblies. I need to change this from a tree structure into the individual operations needed to manufacture the part.

in column A I have the levels.

1 is top level. 1.1 is first part of the assembly. 1.2 is second part of the assembly. 1.2.1 is first part required to make part 1.2 and so on.

Column B has the part number

Column C the quantity of the pieces required in the assembly

Column D has the name of the manufacturing process.

what i need is to create a new file with following columns:

A: operation. this is a concatenation of column B and D

B: item produced or consumed by the operation

operation "Welding A-024961" produces part A-024961 at the end of the operation

operation "Welding A-023961" consumes the lower level parts at the beginning of the operation

C: type. this is either start or end

end is used when a piece is produced

start is used when a piece is consumed

D: quantity. how many pieces are produced or consumed by an operation

Below is an example of the input data (column A-D) and the output data (column I-L)

1yybpvo30iz61.png
Does anyone know how I could do this (semi)automatic? Can be with formulas or VBA.

I have hundreds of files and it would take too much effort to do it by hand.