Better Way to Solve Part Number Rollup (Bill of Materials)

[VBE313]

In the following Excel document (File), I solved a way to have the "Part Number Roll Up" column to have the correct Part Number with the correct line. The logic behind this is:

• If Column N (Level) = 1, then Column M (Part Number Roll Up) = Column O (Part Number)
• If Column N (Level) = 2 & Column P (Task) = "MAKE", then Column M (Part Number Roll Up) = Column O (Part Number)
  • Else If Column N (Level) = 2 then Column M (Part Number Roll Up) = Level 1 Make Part Number
• If Column N (Level) = 3 & Column P (Task) = "MAKE", then Column M (Part Number Roll Up) = Column O (Part Number)
  • Else If Column N (Level) = 3 then Column M (Part Number Roll Up) = Level 2 Make Part Number
• If Column N (Level) = 4 & Column P (Task) = "MAKE", then Column M (Part Number Roll Up) = Column O (Part Number)
  • Else If Column N (Level) = 4 then Column M (Part Number Roll Up) = Level 3 Part Number

If you notice the Tasks at the bottom of each Level 1 have "WELD". This needs to happen after everything else is made, so that is why those Level 3 Welds at the bottom Roll up to the "MAKE LEVEL 2"

Is there a better way to solve this than the way I did? Perhaps a UDF in Column M (Part Number Roll Up).

 

[Fluff]

Not sure if I've understood correctly, but this gives the same results.
=IF(P2="","",IF(P2="MAKE",O2,IF(P2="weld",I2,M1)))

 

[VBE313]

Not sure if I've understood correctly, but this gives the same results.
=IF(P2="","",IF(P2="MAKE",O2,IF(P2="weld",I2,M1)))

Hi Fluff, this did calculate correctly, However I only used Weld as an example. There can be multiple other operations besides Weld. How could I solve that then?

 

[Fluff]

Depends on what should happen & what the other words are.
Can you supply some examples?

 

[VBE313]

Let me try to better explain my logic.

All Level 2's that <> "MAKE" should have the Level 1 Part Number

All Level 3's that <> "MAKE" should have the Level 2 Part Number

All Level 4's that <> "MAKE" should have the Level 3 Part Number

The only "Task" that should matter is the "MAKE" task. Anything that is not a "MAKE" needs to roll up to the next level that is a "MAKE" item.

 

[VBE313]

Maybe this hierarchical picture can help a little more.

 

[Fluff]

Is this what you mean
=IF(P2="","",IF(P2="MAKE",O2,OFFSET(G2,0,N2-1)))

 

[VBE313]

Is this what you mean
=IF(P2="","",IF(P2="MAKE",O2,OFFSET(G2,0,N2-1)))

Thank you!

 

[Fluff]

You're welcome & thanks for the feedback