VBA to find Hierarchy Low LEvel

[DisturbingWorms]

Hi All

In order to progress with some MRP calculations is need to find the lowest level in which a manufactured item is used within a BOM structure, column C below. The raw data below shows the relationships between the Parent and Child Items in columns A & B.

Items A, B and J return a low level of 0 as they are not child items in any of the relationships.
Items D & E are level 1 items as they are the children of level 0 items only.
Items F and G are level 2 items as they only feed into level 1 items ... etc etc.
Items N and O would be purchased items
Item K would have a low level of 4, although it feeds into E at level 2 its lowest level is where it feeds into H

I have a solution using repeated sorting to obtain the low levels for an item but I am looking for a VBA solution as the final list of relationships will be in excess of 4000. Depending on the sequence in which data is entered the actual list of relationships will not be sorted or grouped, relationships between pairs may occur anywhere in the list.

 

[DisturbingWorms]

Sorry, forgot to add the sample data

[TABLE="width: 219"]
<tbody>[TR]
[TD]Parent Item[/TD]
[TD]Child Item[/TD]
[TD]Low Level[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]D[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]E[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]D[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]F[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]J[/TD]
[TD]M[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]F[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]G[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD]H[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD]I[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]H[/TD]
[TD]K[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]K[/TD]
[TD]N[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]K[/TD]
[TD]O[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]K[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody><colgroup><col><col><col></colgroup>[/TABLE]

 

[Fluff]

Cross posted
https://stackoverflow.com/questions/48465742/excel-bom-item-low-level
https://www.excelforum.com/excel-programming-vba-macros/1217544-vba-to-find-bom-low-level.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[jbesclapez]

Cross posted
https://stackoverflow.com/questions/48465742/excel-bom-item-low-level
https://www.excelforum.com/excel-programming-vba-macros/1217544-vba-to-find-bom-low-level.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Hello all,

I am sorry to dig out this threat but after reading all those links I see that the solution is not given. However I am really interested in an answer for that one. @Fluff what about cross posting but in another language? It is ok no? As long as we post the solution in those languages obviously
Thanks for your help

 

[jbesclapez]

Hello all,

I am sorry to dig out this threat but after reading all those links I see that the solution is not given. However I am really interested in an answer for that one. @Fluff what about cross posting but in another language? It is ok no? As long as we post the solution in those languages obviously
Thanks for your help

As I have no reply so far and I can understand as it is not my post, I took the liberty to ask a solution in another forum but in french and with different examples. (Here)
I will obviously come back here to post the solution like Fluff already said. And I will translate it if need be

 

[jbesclapez]

Hi All

In order to progress with some MRP calculations is need to find the lowest level in which a manufactured item is used within a BOM structure, column C below. The raw data below shows the relationships between the Parent and Child Items in columns A & B.

Items A, B and J return a low level of 0 as they are not child items in any of the relationships.
Items D & E are level 1 items as they are the children of level 0 items only.
Items F and G are level 2 items as they only feed into level 1 items ... etc etc.
Items N and O would be purchased items
Item K would have a low level of 4, although it feeds into E at level 2 its lowest level is where it feeds into H

I have a solution using repeated sorting to obtain the low levels for an item but I am looking for a VBA solution as the final list of relationships will be in excess of 4000. Depending on the sequence in which data is entered the actual list of relationships will not be sorted or grouped, relationships between pairs may occur anywhere in the list.

DisturbingWorms, did you find a solution for that one? Please share it.

 

[jbesclapez]

Guys here is a solution that works for me.
The parent in column A
Child in column B
All child must be unique and have only one parent.
Their can be many root parents.

Option Explicit
Dim plg


Public Function papa(ByVal nom As String) As String ' Function look for parents
Dim idf As Long     ' index data table
    For idf = 1 To UBound(plg)  ' if child found in column 2 then brings back parent
        If plg(idf, 2) = nom Then papa = plg(idf, 1): Exit Function
    Next idf
End Function
 
Public Sub generation()
Dim idx As Long     ' index data table
Dim ndp As String   ' name of parent
    With ActiveSheet                        ' Set up the table
        plg = .Cells(2, 1).Resize(.Cells(Rows.Count, 2).End(xlUp).Row - 1, 2).Value
        ReDim res(1 To UBound(plg))         ' store the table results
        For idx = 1 To UBound(plg)          ' loop data table
            res(idx) = 1                    ' initialize data result
            ndp = papa(plg(idx, 2))         ' search initial parent
            While ndp <> ""                 ' if parent find go to next
                res(idx) = res(idx) + 1     ' next level
                ndp = papa(ndp)             ' look for parent next level
            Wend
        Next idx                            ' show table with results
        .Cells(2, 4).Resize(UBound(res), 1).Value = Application.Transpose(res)
    End With
End Sub