Look Up BOM Hierarchy using Level field

[Prozen]

Hi all,

I am trying to come up with a method of looking up the BOM structure below, using the level column, to replicate the manual output i have created in the column 'IDEAL'.

The logic here is
1) If 'ISCONF' = 'N' then it remains 'N'
2) If 'IsConf' is 'Y' then look up at its parent, which would be its 'Level' - 1 to see if 'IsConf' is also 'Y'. This needs to cycle all the way up to its related Level 2. If its parent/grandparent/great-grandparent etc, all the way up Level 2 has IsConf = 'N' then the output for that row should be 'N'

Level	Fitting	Funct	Part	IsConf	IDEAL
0​			SUB123	Y	Y
1​	RCI		SUB234	Y	Y
2​		RIA	OLD123	N	N
3​		Group1	OLD234	Y	N
4​	1_1_1		OLD345	Y	N
4​	1_1_2		OLD456	Y	N
2​		RIA	NEW345	Y	Y
3​		Group1	NEW234	Y	Y
4​	1_1_1		NEW345	Y	Y
4​	1_1_3		NEW456	N	N
4​	1_1_3		MAS456	Y	Y

Previously I've used something like the below to look up what the 'IsConf' is for its parent but i have no idea how expand on this to cycle up multiple levels to get the result without creating numerous columns to repeat the same formula

=IF(A2=0,"Y",LOOKUP(2,1/($A$2:$A2=A2-1),$E$2:$E2))

Any assistance is much appreciated!

 

[jasonb75]

Your post is more than a bit confusing this part,

This needs to cycle all the way up to its related Level 2.

implies that the formula doesn't need to look above level 2, if that is the case then why are some level 3 and level 4 rows N when IsConf is Y?

How do you identify which level 2 item is related to which level 1 item, etc? There doesn't appear to be any consistency in your example.

Please bear in mind that many people on this forum will understand your task but few will understand your data.

 

[Prozen]

So in the snippet below IsConf = Y for Level 3 and Level 4 rows, however when you work your way up from either of these level's when you get to Level 2 'IsConf' = N. This then makes them 'N' rather than a 'Y'.

Level	Fitting	Funct	Part	IsConf	IDEAL
0​			SUB123	Y	Y
1​	RCI		SUB234	Y	Y
2​		RIA	OLD123		N
3​		Group1	OLD234	Y	N
4​	1_1_1		OLD345	Y	N
4​	1_1_2		OLD456	Y	N

When looking at the relationships between the Parts its probably better visualized with the indents below as

Level	Fitting	Funct	Part	IsConf	IDEAL
0			SUB123	Y	Y
1	RCI		SUB234	Y	Y
2		RIA	OLD123	N	N
3		Group1	OLD234	Y	N
4	1_1_1		OLD345	Y	N
4	1_1_2		OLD456	Y	N
2		RIA	NEW345	Y	Y
3		Group1	NEW234	Y	Y
4	1_1_1		NEW345	Y	Y
4	1_1_3		NEW456	N	N
4	1_1_3		MAS456	Y	Y

 

[Prozen]

I have managed to get there in a slightly long winded way by
1. In column D i converted the 'Y' to 1 and 'N' to 0
2. In column E i use =D2*IF(A2=0, 1, LOOKUP(2, 1/(A$1:A2=A2-1), E$1:E1)) to apply the logic in the first post (took this from Google where someone was calculating quantities of parts)
3. Column F to convert it back from 1 and 0 to 'Y' and 'N'

Column G was just to show it matched the output i was looking for. If there are any thoughts on how i can clean this processes up and save not having to create 3 columns would be greatly appreciated.

Sample.xlsm
	A	B	C	D	E	F	G
1	Level	Item	IsConf	IsConf (as number)	Formula	Converted	IDEAL
2	0	Garage	Y	1	1	Y	Y
3	1	Car	Y	1	1	Y	Y
4	2	Front	N	0	0	N	N
5	3	Tyre	Y	1	0	N	N
6	3	Rim	Y	1	0	N	N
7	4	Nut	Y	1	0	N	N
8	2	Rear	Y	1	1	Y	Y
9	3	Wheel2	Y	1	1	Y	Y
10	4	Nut	Y	1	1	Y	Y
11	4	Bolt	N	0	0	N	N
12	4	Pad	Y	1	1	Y	Y
Sheet1

Cell Formulas
Range	Formula
E2:E12	E2	=D2*IF(A2=0, 1, LOOKUP(2, 1/(A$1:A2=A2-1), E$1:E1))
F2:F12	F2	=IF(E2=1,"Y","N")

 

[jasonb75]

I couldn't see what was needed from your first reply because the indents are not showing, however I can see it from the 'long winded way' that you posted.

I have it down to a single column but row 7 is coming up with Y instead of N, hopefully it won't take me too long to figure out why.

edit:- this looks good

Book2
	A	B	C	D	E
1	Level	Item	IsConf	Formula	IDEAL
2	0	Garage	Y	Y	Y
3	1	Car	Y	Y	Y
4	2	Front	N	N	N
5	3	Tyre	Y	N	N
6	3	Rim	Y	N	N
7	4	Nut	Y	N	N
8	2	Rear	Y	Y	Y
9	3	Wheel2	Y	Y	Y
10	4	Nut	Y	Y	Y
11	4	Bolt	N	N	N
12	4	Pad	Y	Y	Y
Sheet19

Cell Formulas
Range	Formula
D2:D12	D2	=IF(A2=0,"Y",IFERROR(LOOKUP(2,1/(A$1:A2=A2-1)/(C2="Y"), D$1:D1),"N"))

 

[Prozen]

That works perfectly, Jasonb75! Thank you very much