Parent child relationships(working out parent item) for each item

[grphillips]

I have added the attachment to make it easier to understand the standard output i am trying to achieve in column C using the data in column A and column B.

What i am trying to achieve is identify the parent item automatically in column C via an excel formula or vba excel 2010 script. Surely an excel formula can do but I failed to make it work if formula in excel. i need to do this for multiple levels.

Imagine a scenario where you have list of parts to make/manufacture a car or something and you want to understand the parent item assembly for each item. The parent child relationships are worked out by the levels in column A.

I have manually populated the correct parent value in column c so you can see the standard pattern to work out the parent item. However need a forumula to do this automatically so column C values are populated automatically.

Many thanks

[Andy Pope]

array formula, comit with CTRL+SHIFT+ENTER

=IF(A4=0,"",TRIM(INDEX($B$4:$B$39,MAX(IF($A$4:A4=A4-1,ROW($A$4:A4)))-3)))

Example also include the formula in 2 columns so it's easier to understand

[grphillips]

Sheer class thank you, seemed complicated but you have made it as 1 formula which did think was possible. This allows you work out the parent item in tree structure something quite standard in manufacturuing part assemblies awesome. i have been puzzled for years in how to do this?

Which formula do i use? Can i just have 1 formula in 1 column or do i need 3 formulas in 3 columns to make it work and output?


I'll admit I dont understand how the formula works and don't understand array formulas so if you don't mind explaining in lay man terms.

Thank you,

[Andy Pope]

Array formula in column D determines the largest row number for all those rows above current cell that match the current level -1

normal formula in E uses that row position to pick out the description from column B.

Array formula in F combines the 2 formula in one.

If you only need the description then use the combined formula. If you think you might want to use the position information to locate something else then use the separate formulas.

[grphillips]

Hey cheers that amazing, build on from the formulas before i.e capturing the parent item for each part in sheet 1.

If you don’t mind or anyone else , greatly appreciate:

What I also need to do is:

Working out what’s changed between old and new parent child structure and populate the results in the new structure sheet so there is a record of what has changed.

Imagine these commands:


Add (Blue Font) – means that the item is newly added to the new/current parent child relationship tree structure for its relevant parent i.e its not found in the old structure.

Updated (Blue font): the meta data of item has been updated

Delete (Red font strike through): The item parent child part relationship is not found in new structure and has been deleted from new structure when comparing to old structure.

See column A new changed sheet as an example of an output.
See your 907696.zip sheet attached with with my new additions.

Again imagine a scenario where you have old and new model of cars and you want to work out the part changes for the applicable parent child structures. And what item has had meta data changes. Basically you want a good record of whats change between the old and new models and populate the results in new bill of material structure. This is essentially a bill of material structure of items if you get me.

Hopefully the solution you provide will have flexibility in columns and rows etc and number of columns to compare meta data for so if new columns are added etc it will still work whats updated and be under my control what is outputed.

Thank you again

[Andy Pope]

You can do this by concatenating elements in both the new and old sheet. So Level and Item and then use the MATCH formula to locate additions and deletions.
You can do the same for description and category and if different check the addition/deletion columns, if they are not applied then it would be a change.

[Andy Pope]

Double post due to forum error

You can do this by concatenating elements in both the new and old sheet. So Level and Item and then use the MATCH formula to locate additions and deletions.
You can do the same for description and category and if different check the addition/deletion columns, if they are not applied then it would be a change.

[grphillips]

Thanks once again, cant believe power of formula still

(1)Do you mean contactenating the parent item column (i.e your formula) and item column to work out additions and deletions. Why the level? the deletion seems awkward because you would want to show the deleted line in the new structure.

(2)There would be a number of extra change columns i guess for each 1 i.e if you wanted to work out what changed in description column you would have to have a changed column just for the description column i guess rather than 1 change colums checking all concatenations?

(3)Why use the match formula rather than vlookup ?

(4)Is this the most flexible way and easiest way? are you saying writing a script is not worth the effort on balance?

Will mark this as 5 stars soon, thank you its great
that you understood the pattern first time without a question and got formula right first time.

[Andy Pope]

I included the level because I assume it may be possible to have a component with the same ref at different levels. perhaps this assumption is wrong.

Yes you would need to test category and description columns separately. How else will you be able to report the description only has changed without including explanation of what the text is that has changed?

VLOOKUP matches on the left most column and returns information to the right for the matching row. You would need to change the layout of your data and formula to do this.

I would expect a VBA solution to be the most flexible and complete solution, especially when it comes to reporting a combination of new and old in a single report. Your formulas would get messy very quickly if you were trying to rebuild a list with insertions and deletions.
However writing a BOM reporting code set is not simple.

[grphillips]

Hi , thanks again, your right the same part could be use in multiple places and even different levels do you have a pratical way of reporting changes between BOMs? Its great to find someone who understand BOMs an engineer and programmer combined.

Maybe I am being cumbersome in my approach, boms are boms and parts and parts after all.

But all I am doing is presenting changes between BOMs.

BOM Change Actions could be:

Move: Parent child relationship may be moved in same structure to lower levels etc.
Delete
Add
Updated (.e updated meta data)
Roll ( A part my have a functional change resulting in a new part number - though this can be see also as an add - its form/fit/function different therefore a new part a roll from previous parts.




Thanks,

[Andy Pope]

You will need to use VBA coding to do the things you want. There maybe 3rd products available, I haven't looked.

[grphillips]

Hi,

Do you know the vba solution by any chance?

[Andy Pope]

It's not a case of knowing more one of writing.
Somebody would need to write the VBA solution. That task is way beyond the remit of a free help forum.