Correlating data to multiple parent items

[pandadora]

I have a dataset with multiple layers where certain data points correlate to a single piece then that single piece correlates to multiple points. I am trying to flatten the data set so that all base items correlate to the final item in the total quantities.

This is how the data is currently displayed

	ITEM	WHERE USED	QTY
1	X	MOD	2
2	Y	MOD	1
3	Z	MOD	1
4	MOD	FG1	1
5	MOD	FG2	2
6	MOD	FG3	1

<tbody>
</tbody>

this is how it needs to be displayed:

	Item	Where Used	Qty
1	X	FG1	2
2	Y	FG1	1
3	Z	FG1	1
4	X	FG2	4
5	Y	FG2	2
6	Z	FG2	2
7	X	FG3	2
8	Y	FG3	1
9	Z	FG3	1

<tbody>
</tbody>

I have pulled the data that does not correlate to a finished product and did a vlookup, this works when there is more than one layer, but when the parent is linked to multiple second parents, it returns the first found value.

I've tried filling the series of second parent datapoints to the right of the spreadsheet, which works, but to clean the data up I have to insert and transpose the data and fill the base items in the blank area, and there is too much data to do that manually