BOM re-build macro

Krzysztof_Hyla_1986

Hi, I'd like to re-build my BOM (parent - child) table (below) in following way:

- extract the finished product # of each BOM structure into separate column and

- omit purchased parts sub-structure explosion

The workflow (or macro) should basically set the child part with no parent part (NULL) as product, and then go down the tree exploding each level.

It shouldn't explode purchased parts though (I crossed them out with red line)

Of course this is just a sample from my dataset.

BOM Tree.jpg

Here is my input and output data.

In the Output the macro should add first two columns.

BOM Tables.jpg

Any help would be appreciated

BOM Build.xlsx

Workflow

Macros

Transformation

Iterative Macro

Accepted answers

apathetichell

Something like this?

I'll be offline for a few days but the standard way would be a batch macro to filter for product type - and then an iterative macro to loop through your entries.

BOM outer workflow.yxzp

All comments

apathetichell

Something like this?

I'll be offline for a few days but the standard way would be a batch macro to filter for product type - and then an iterative macro to loop through your entries.

BOM outer workflow.yxzp

Krzysztof_Hyla_1986

This is very close to what I need, however it shows ('explodes') children for parent parts that are purchased (crossed out in the hierarchy chart), which I don't want.

For example, for BOM # 2 I don't want to see rows for child parts 8458623 and 7899571 (with parent part # 2371940).

Would it be possible to make this adjustment?

Krzysztof_Hyla_1986

Ok, I found the solution to exclude purchased parts explosion - just added a filter in the iterative macro row match input: Child Part Type = 'M'

Thanks a lot! This is really great help! - it would take me a lot of time to figure this out on my own.

apathetichell

glad it worked - tbh - didn't see that part - and didn't see how it would fit into the excel raw data.

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335