Formula for determining parts in a 1.1.1.1 hierarchy.

Christian Graf

I'm looking for a formula to determine how many parts are in a single assembly based on hierarchy.

I'm using this equation which doesn't work: =IF(AND((LEN([Item Hierarchy]@row) - LEN(SUBSTITUTE([Item Hierarchy]@row, "0", ""))) / LEN("0") = 5, [Component Type]@row = "Assembly"), COUNTIFS([Item Hierarchy]:[Item Hierarchy], CONTAINS(MID([Item Hierarchy]@row, 1, 1), [Item Hierarchy]:[Item Hierarchy])))

As you can see, I have a column with the hierarchy, and one that says if it is an assembly.

So I need a formula that says if assembly, and five zeros, then count everything in the range of [Item Hierarchy]:[Item Hierarchy] that has the same value to the left of the first period.

Then if 4 zeros, count everything to the left of the second period, etc, etc. (I could probably figure this consecutive formula after the first equation works.)

I also need a formula to populate the parent assembly number. If the part is a part of the parent assembly, then put the parent assembly number in the column. So 1.1.1.0.0.0 is under 1.1.0.0.0.0 so it would have part number 3762315 in the parent assembly column.

Sorry if this is a long hard one :)

Thanks

Sameer Karkhanis

My recommendation would be to use the indentation (either for the "Item Hierarchy" column or the "Component Type" column) which will create a systematic hierarchy and you can use the built in functions like CHILDREN, PARENT, ANCESTORS, DESCENDANTS to get the count and values more effectively.