-
Jan 31st, 2018, 09:43 AM
#1
Thread Starter
New Member
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
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
-
Jan 31st, 2018, 01:41 PM
#2
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.
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...
-
Jan 31st, 2018, 02:18 PM
#3
Thread Starter
New Member
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
-
Feb 5th, 2018, 02:26 PM
#4
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.
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...
-
Feb 5th, 2018, 03:24 PM
#5
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|