locked
Bill of Materials Low Level RRS feed

  • Question

  • 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 E, N and O would be purchased items

    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. I have looked at similar posts that appear to be using recursive code to determine the overall quantity for items with similar raw data layout.

    Parent   Child     Low Level

    A          D          0

    A          E           0

    B          D          0

    B          F           0

    J           M           0

    D          F            1

    D          G           1

    F          H            2

    G          I            2

    H          K           3

    K           N           4

    K           O            4


    Monday, January 29, 2018 11:20 AM

All replies

  • Hi DisturbingWorms,

    Based on your description and sample data.

    I t looks like Low Level is generated based on first column only.

    If so, then you can try to refer code below to generate Low Level.

    Sub demo()
    Dim i As Long
    Dim sht As Worksheet
    Dim LastRow As Long
    Set sht = ActiveSheet
    LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
        For i = 1 To LastRow
            If Cells(i, 1).Value = "A" Or Cells(i, 1).Value = "B" Or Cells(i, 1).Value = "J" Then
                Cells(i, 3).Value = "0"
            ElseIf (Cells(i, 1).Value = "D" Or Cells(i, 1).Value = "E") Then
                Cells(i, 3).Value = "1"
            ElseIf (Cells(i, 1).Value = "F" Or Cells(i, 1).Value = "G") Then
                Cells(i, 3).Value = "2"
            End If
        Next i
    End Sub
    

    Output:

    Further, You can try to modify the code based on your requirement.

    If this not what you are looking for then try to correct us and provide more information.

    We will again try to provide suggestions to solve the issue.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, January 30, 2018 6:33 AM
  • Hi Deepak

    The level for the items in column A is determined by assessing the relationships between parent and child. It is not appropriate to hard code letters as the script needs to be dynamic. For example you can not assume item A,B and J will always have a low level of 0. The actual list of relationships had 4000 records so it is necessary to determine level for each item. Overtime the relationships will change so it is necessary to re-calculate on a regular basis. Another way to describe the problem is to determine the lowest level an item appears in a Parent Child hierarchy.

    .

    If an item in column A does not appear in the child list then that item would attract a level 0. The item is not consumed by another item.

    If an item is only consumed by (feeds into a) level 0 items then it would have a level 1.

    If an item is consumed by a level 0 and level 1 item then its level would be 2, the lowest level

    Similarly, if an item is consumed by a level 0 and level 2 item then its level would be 3, the lowest level

    If a level 1, level 2 and level 4 item all have the same item feeding into them then that item would have a low level of 5.

    .

    The nearest post I can find with a related problem deals with finding the quantities for items based on the low level

    social.msdn.microsoft.com/Forums/office/en-US/ac6ad322-25da-4d29-a1ea-a092e528350e/bill-of-materials-explosion-using-recursive-vba-code-in-excel?forum=exceldev





    Tuesday, January 30, 2018 2:19 PM
  • Hi DisturbingWorms,

    From your description, I understand that It is much more deep concept.

    I have some confusions.

    You had mentioned that,

    -> The level for the items in column A is determined by assessing the relationships between parent and child.

    How can I access the relationship between parent and child?

    -> If an item in column A does not appear in the child list then that item would attract a level 0. The item is not consumed by another item.

    Do you mean what ever character in column A (Parent) is not available in column B (child) will be set as level 0. Correct?

    -> If an item is only consumed by (feeds into a) level 0 items then it would have a level 1.

    What is feeds? and how to determine it?

    -> If an item is consumed by a level 0 and level 1 item then its level would be 2, the lowest level

    Do you mean if any character in column A (Parent) already have the low level 0 and 1 then next time it's low level will be 2?

    If so, Then first I need to search low level of 0 and 1 and then I can move further to find level 2,3,4,5 based on low level. Correct me if i am wrong.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, January 31, 2018 7:56 AM
  • Your list is ambiguous. For example consider your 6th & 7th items F and G, each with parent D. But is that the D whose parent is A or B...?

    Try adding a unique ID for each item in the list. Duplicate items such as your D will have separate IDs. Then for each item's parent assign the relevant unique key. Thereafter how easily the rest will fall into place depends mainly on if the items appear in logical order in the list. If not, which is typically the case, it gets more complex.

    I added your data to the 'pro' version of our treeview, it can accept items in a non logical order. First though I added an extra 3 items for A, B & J which do not have parents, in treeview terminology as 'root nodes'. The IDs I gave (simply 1 to 15) and 'node' levels were added to captions only for illustration.


    Due to the ambiguity this is only one potential representation of your data (though maybe I didn't fully take your 'levels' into account). 

    I rarely refer to anything I'm involved with but you might find our pro treeview useful for your objective here, and quite a lot more for producing and editing a BOM.

    http://www.jkp-ads.com/articles/treeview.asp

    If contacting us for a demo (scroll down) mention this thread.


    Wednesday, January 31, 2018 10:06 AM
  • Hello Peter

    Sorry for being ambiguous, your representation of the relationships is correct. What I am trying to determine is what is the lowest level that each item has across all trees, For example Item F would be a level 2 item.

    Wednesday, January 31, 2018 1:33 PM
  • You'd still have to go through a process similar to what I went through to make that tree. Return a list of the items with their levels (the L numbers shown in the tree), and sort items as the first key and levels as the second.

    There are various approaches but without knowing what you're working with difficult to suggest what might be the most appropriate. Could be difficult depending on what you've got. However probably best to start with unique IDs for all items along the lines I suggested.

    FWIW this is a sorted listing from the tree I made 

    Item Level
    A 0
    B 0
    D 1
    D 1
    E 1
    F 1
    F 2
    G 2
    H 3
    I 3
    J 0
    K 4
    M 1
    N 5
    O 5

    If I've got it right looks like there are only two duplicate items D & F, both Ds are level-1, with the Fs at levels 1 and 2

    Wednesday, January 31, 2018 2:05 PM
  • The only way I have been able to automate determining the levels is using countif a repeated sort as below

    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

    Wednesday, January 31, 2018 5:02 PM
  • Hi DisturbingWorms,

    Does, code above solved your issue?

    If yes, I suggest you to mark it as an answer.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, February 2, 2018 9:37 AM