Convert Indented BoM to Parent-Component BoM format

[Beanstalk]

I need to convert many Bills of Material from an 'Indented Levels' list into a 'Parent-Component' list.
That is, I want a formula on each row that reports the Parent part for the part on that current row.

I have tried to use Index-Match, but can't get every row to return the correct answer.
The problem arises when a BoM contains several groups of parts at the same level.

For example, the Parent level is 1 less than the Current level, and is found by stepping back through the column, (upwards towards lower row numbers), and should report the first match encountered.

Indented Levels look like this:

Level Part
1 Assembly 1
2 Part1 of Assy
2 Part2 of Assy
3 Part1 of (Part2 of Assy)
4 Part1 of (Part1 of (Part2 of Assy))
2 Part3 of Assy
3 Part1 of (Part3 of Assy)

And I want it in the form:
Lvl Parent Component
2 Assy1 Part1 of Assy
2 Assy1 Part2 of Assy
2 Assy1 Part3 of Assy
3 Part2 of Assy Part1 of (Part2 of Assy)
4 Part1 of (Part2 of Assy) Part1 of (Part1 of (Part2 of Assy))
2 Part3 of Assy Part1 of (Part3 of Assy)

This above example can be solved by an Index/Match solution, but it falls down if there are multiple 'runs' of a particular level because the array in Index/Match
For example:

Row Level
1 1
2 2
3 3
4 3
5 3
6 4
7 2
8 3
9 3
10 3
11 3
12 4
13 2
14 3

In this example, a formula using Index/Match operates on the array of rows 1:14 and correctly reports the first run of 3's (rows 3 to 5) as having parent = 2, on Row 2.
But the same formula does see second group of 3's (rows 8 to 10) within the same array.

So the essence of my problem is:
How do I feed the Index/Match formula with a dynamic array Start Row, which is determined by a Match of the first Level in a run?

I am trying to use the Indirect function to feed that dynamic Row number into the Index Array Start.
For example, on Row 11, (level 3), it is evident that Row 7, (level 2) is the parent, and not Row 2. But how do I get Row 8 recognised as the start of another run of 3's?

This type of conversion is fairly common in manufacturing businesses so I'm sure it would have been solved before.
I need some advice, (preferably a formula), please.

Thanks

 

[Rijnsent]

Hi Beanstalk,

if I just take your table with the Row & level and put it in A1:B15, this formula gives the parent:
{=MAX(IF($B$2:B2=B2-1,$A$2:A2))}
->put it in C2 and press CTRL+SHIFT+ENTER as it's an array formula, then pull down.
Basically, it takes the maximum of the row (where the level is one below the level of the current row). Another function that could help in your INDEX/MATCH is OFFSET, but this MAX/IF combo works too .

Cheers,

Koen

 

[Beanstalk]

Brilliant!
Thank you Koen. Yes it works well and avoids the use of VBA.
Cheers,
Andrew