Look Up BOM Hierarchy using Level field

Prozen

New Member
Joined
Aug 30, 2020
Messages
4
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
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'

LevelFittingFunctPartIsConfIDEAL
0​
SUB123YY
1​
RCISUB234YY
2​
RIAOLD123NN
3​
Group1OLD234YN
4​
1_1_1OLD345YN
4​
1_1_2OLD456YN
2​
RIANEW345YY
3​
Group1NEW234YY
4​
1_1_1NEW345YY
4​
1_1_3NEW456NN
4​
1_1_3MAS456YY

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!
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,630
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Aug 30, 2020
Messages
4
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
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'.

LevelFittingFunctPartIsConfIDEAL
0​
SUB123YY
1​
RCISUB234YY
2​
RIAOLD123N
3​
Group1OLD234YN
4​
1_1_1OLD345YN
4​
1_1_2OLD456YN

When looking at the relationships between the Parts its probably better visualized with the indents below as
LevelFittingFunctPartIsConfIDEAL
0SUB123YY
1RCISUB234YY
2RIAOLD123NN
3 Group1OLD234YN
41_1_1OLD345YN
41_1_2OLD456YN
2RIANEW345YY
3Group1NEW234YY
41_1_1NEW345YY
41_1_3NEW456NN
41_1_3MAS456YY
 

Prozen

New Member
Joined
Aug 30, 2020
Messages
4
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
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
ABCDEFG
1LevelItemIsConfIsConf (as number)FormulaConvertedIDEAL
20GarageY11YY
31CarY11YY
42FrontN00NN
53TyreY10NN
63RimY10NN
74NutY10NN
82RearY11YY
93Wheel2Y11YY
104NutY11YY
114BoltN00NN
124PadY11YY
Sheet1
Cell Formulas
RangeFormula
E2:E12E2=D2*IF(A2=0, 1, LOOKUP(2, 1/(A$1:A2=A2-1), E$1:E1))
F2:F12F2=IF(E2=1,"Y","N")
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,630
Office Version
  1. 365
Platform
  1. Windows
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
ABCDE
1LevelItemIsConfFormulaIDEAL
20GarageYYY
31CarYYY
42FrontNNN
53TyreYNN
63RimYNN
74NutYNN
82RearYYY
93Wheel2YYY
104NutYYY
114BoltNNN
124PadYYY
Sheet19
Cell Formulas
RangeFormula
D2:D12D2=IF(A2=0,"Y",IFERROR(LOOKUP(2,1/(A$1:A2=A2-1)/(C2="Y"), D$1:D1),"N"))
 
Last edited:

Prozen

New Member
Joined
Aug 30, 2020
Messages
4
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
That works perfectly, Jasonb75! Thank you very much
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,480
Messages
5,770,341
Members
425,612
Latest member
martinijr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top