BOM Highes Level Item Extraction Macro

Question

Hi,

I have BOM table with Parent Item # & Component Item # columns.

I would like to add 'Finished Item #' column - the highest level for each component.

(I have highlighted finished items with different colors to distinguish them)

Optionally, it would be nice to have 'Relative Component Level #' column.

Any ideas how to approach this?

I don't have much experience with iterative macros and it seems that it is needed here.

BOM Sample.xlsx

Krzysztof_Hyla_1986 · Answer

Hi Megan,

maybe I'm looking at it the wrong way, but I think it doesn't give me what I need.

I don't need parent item, because I already have it.

I need highest level in hierarchy for each component item (column C).

This would be a column with 3 values: 62368, 90362 and 84974 (column E in my Excel sample file)

Is this achievable?

MeganBowers · Answer

Hi @Krzysztof_Hyla_1986.

I think this macro should get you most of the way there: Hierarchy generation macro. I tried it out with your data (after changing the type to string) and here's the result:

Hopefully you can parse from here into your desired format!

Megan

BOM explosion.yxzp