Results 1 to 5 of 5

Thread: Excel Recursive VBA - Hierarchical BOM Low Level

  1. #1

    Thread Starter
    New Member
    Join Date
    Jan 2018
    Posts
    2

    Post Excel Recursive VBA - Hierarchical BOM Low Level

    Hello All

    With reference to the screenshot below I have a situation where I need to determine the lowest level that an item appears in all hierarchical structures. The relationships between the items is presented as shown in columns A and B. To help clarify the interpretation of the relationships I have added a treeview for item A.

    What I am looking to find is the lowest level each item in Column A has taking into consideration all the relationships. I have manually added the low levels in column C. In the example below items A, B and J will have a low level of 0 as they do not appear as the children of any other items. Similarly D and E have a low level of 1 as they only appear as children of level 0 items. K will have a low level of 4, although it appears as a child of item E and would have a level of 2 it also appears as a child of H where it has a level of 4. Therefore K's low level is 4.

    I have found similar problems relating to summing up the quantities in nested boms based on the low level or determining the outline but not on determining the low levels. All of these approaches use recursive vba which alas I am not familiar. The actual list of relationships is around 4000, the items will not be plain strings but alphanumeric values and not sorted, although they could be sorted as part of the routine. The low levels will need to be recalculated on a regular basis as relationships will / may change over time. For this reason I was looking for a VBA solution in Excel.

    Any ideas on how the low levels may be determined / scripted in Excel would be greatly appreciated. I have a work around using countif and repeated sorting but its not brilliant


    Name:  Capture.JPG
Views: 2413
Size:  28.7 KB

    Code:
    Sub setlowlevel()
    
    Dim lowlevel As Integer
    Dim StartRow As Long
    Dim LastRow As Long
    
    'Find lowlevels
    lowlevel = 0
    StartRow = 2
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    Do
    
    Sheet4.Range("C" & StartRow).Select
    ActiveCell.FormulaR1C1 = "=IF(COUNTIF(R" & StartRow & "C2:R" & LastRow & "C2,RC[-2])=0," & lowlevel & "," & lowlevel + 1 & ")"
    Selection.AutoFill Destination:=Range("C" & StartRow & ":C" & LastRow)
    
           
        Range("A" & StartRow & ":C" & LastRow).Select
        ActiveWorkbook.Worksheets("Sheet4").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Sheet4").Sort.SortFields.Add Key:=Range("C" & StartRow & ":C" & LastRow) _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("Sheet4").Sort
            .SetRange Range("A" & StartRow & ":C" & LastRow)
            .Header = xlGuess
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    
    lowlevel = lowlevel + 1
    StartRow = Sheet4.Range("C:C").Find(what:=lowlevel, after:=Sheet4.Range("C1"), LookIn:=xlValues).Row
    
    Loop Until Sheet4.Range("C" & StartRow).Value = lowlevel - 1
    
    
    End Sub
    Last edited by DisturbingWorms; Jan 31st, 2018 at 12:09 PM. Reason: code added

  2. #2
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Amsterdam...
    Posts
    5,343

    Re: Excel Recursive VBA - Hierarchical BOM Low Level

    You are going to need a recursive function utilising static (I think it was) and an escape condition (ie no child).

    headache at least.


    Would i be right in stating that each branch can have the same child as another
    eg.. a-> d b-> d b->f d->f
    So each branch can be different?

    How are the connections processed, on arrival or first reached.

    BOFH Now, BOFH Past, Information on duplicates

    Feeling like a fly on the inside of a closed window (Thunk!)
    If I post a lot, it is because I am bored at work! ;D Or stuck...
    * Anything I post can be only my opinion. Advice etc is up to you to persue...

  3. #3

    Thread Starter
    New Member
    Join Date
    Jan 2018
    Posts
    2

    Re: Excel Recursive VBA - Hierarchical BOM Low Level

    Yes is it possible that different branches could have the same child in it. In the tree example for item A both the D and E branches contain K or you could have
    A>D>F
    B>D>F
    B>F

    If F was a bolt it could be used to construct D and then the same type of bolt used to secure D onto B. In this scenario if B was a level 0 item then F would have a low level of 2, its lowest level in either the A branch or B branch. In the scenario below F would have a low level of 3

    A>D>F
    B>E>D>F
    B>F

    You wold not have a recursive loop in the relationships, so F>D or F>A would not be allowed

  4. #4
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Amsterdam...
    Posts
    5,343

    Re: Excel Recursive VBA - Hierarchical BOM Low Level

    ms help on recusion

    Also similar to this thread which I still havent solved.
    My last thoughts on it were to get all possible routes and map them into one sheet... then either filter or find each start and end point.

    But the same mapping might work for you listing all possible branches of your tree from starting points.

    BOFH Now, BOFH Past, Information on duplicates

    Feeling like a fly on the inside of a closed window (Thunk!)
    If I post a lot, it is because I am bored at work! ;D Or stuck...
    * Anything I post can be only my opinion. Advice etc is up to you to persue...

  5. #5
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Excel Recursive VBA - Hierarchical BOM Low Level

    To help clarify the interpretation of the relationships I have added a treeview for item A.
    i can see the result in a worksheet,
    but how is the original structure stored? do you have a sample of a few items that you can post for testing?
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width