Bomparent

[Silverwolph]

Back in 2016 someone asked about finding the bom parent and tonyyy answered with this awesome code

Sub BOMParent3()

Dim LastRow As Long
Dim r As Range, r1 As Range, r2 As Range
Dim Lvl As Long, Parent As Long
Dim Level As String, Level2 As String

LastRow = Cells(Rows.Count, "D").End(xlUp).Row
For Each r In Range("D3:D" & LastRow)
    Set r1 = r.Offset(0, -3)
    Level = Replace(r1.Value, " ", "")
    Level = Replace(Level, ".", "")
    Lvl = Level
    Parent = Lvl - 1
    For Each r2 In Range(r1.Address, "A2")
        Level2 = Replace(r2.Value, " ", "")
        Level2 = Replace(Level2, ".", "")
        Lvl = Level2
        If Parent = Lvl Then r.Offset(0, -1) = r2.Offset(0, 3)
    Next r2
Next r

End Sub

My question is, could you expand this to show the entire parent level all the way to the end
So you have "8" and it has 4 or more parents "7, 6, 5, 4" which also has 2 parents 3 and 2, all ending up in 1
and you will end up with the entire string

A snip from my sheet

>>>>>>>>>>	Pos.	Item Number
1​	10​	7659347​
.2	1​	7658274​	7659347​
..3	1​	7657890​	7658274​	7659347​
...4	1​	7658287​	7657890​	7658274​	7659347​
....5	1​	7658205​	7658287​	7657890​	7658274​	7659347​
.....6	1​	4882118​	7658205​	7658287​	7657890​	7658274​	7659347​
......7	1​	4882117​	4882118​	7658205​	7658287​	7657890​	7658274​	7659347​
.......8	1​	4882115​	4882117​	4882118​	7658205​	7658287​	7657890​	7658274​	7659347​
........9	1​	92500772​	4882115​	4882117​	4882118​	7658205​	7658287​	7657890​	7658274​	7659347​
.......8	2​	4956389​	4882117​	4882118​	7658205​	7658287​	7657890​	7658274​	7659347​
........9	1​	4882113​	4956389​	4882117​	4882118​	7658205​	7658287​	7657890​	7658274​	7659347​
.........10	1​	92500772​	4882113​	4956389​	4882117​	4882118​	7658205​	7658287​	7657890​	7658274​	7659347​
........9	2​	9511515​	4956389​	4882117​	4882118​	7658205​	7658287​	7657890​	7658274​	7659347​
.......8	3​	4882102​	4882117​	4882118​	7658205​	7658287​	7657890​	7658274​	7659347​

And so on, would this be possible
I´m working with a bom structure with 11 levels and if i look up a raw material it can have over 100 dif. parents
All again have new parents, but they all end up at the top number, 7 to 11 levels up.

And side question, is there a way of optimizing the code? with 6000+ lines it takes about 8 hours to run through it all

 

[Silverwolph]

A mate of mine said, if we can put the VBA code into an array with would be faster
But i´m unsure on how to handle this, and also if it should lookup the parent of one level before
starting the next

Any Idears?

 

[Peter_SSs]

A snip from my sheet

What range have you shown? For example what cell is the text "Item Number" in?

I suggest that you investigate XL2BB for providing sample data as it is much easier to see that sort of thing.

 

[Silverwolph]

What range have you shown? For example what cell is the text "Item Number" in?

I suggest that you investigate XL2BB for providing sample data as it is much easier to see that sort of thing.

It starts in A1, so Item number is in C1
The VBA code will with abit of tweeking input data in D2:D, and i need this data to input new parent in E2:E and then E´s parent into F2:F

 

[Peter_SSs]

It starts in A1, so Item number is in C1

Thanks for clarifying that.

However, I still don't really understand exactly
- What data you are starting with, and
- What you want the code to do

 

[Silverwolph]

Sorry, but i´m not allowed to install XL2BB on the PC i´m working on right now.
The data i start out with looks like this

Only a little snip

>>>>>>>>>>	Pos.	Item Number	Rev.	Subt.	Quant.	Status
1	1	4535326	C		1	240
.2	1	3416279	A		1	230
..3	1	8364148	A	NRM	1	230
.2	2	3417872	C		1	230
..3	1	8364146	A	NRM	1	230
.2	3	3417694	B		1	240
..3	1	3418757			1	230
...4	1	3419804	A		1	230
....5	1	400006499	C	NRM	1	230
...4	2	4319406			1	230
..3	2	4319405	A	NRM	1	230
..3	3	3419223	A		1	230

But i wanted to have it provide all the parentes to see the string of a part/item with all the parentes
like this snip here, I´m able to create it but its very slow, i´m just looking for a faster/better way of doing it.
Some times the level of boms can be 11 levels deep and there can be 5000 or more lines

>>>>>>>>>>	Pos.	Item Number
1	1	4535326
.2	1	3416279	4535326
..3	1	8364148	3416279	4535326
.2	2	3417872	4535326
..3	1	8364146	3417872	4535326
.2	3	3417694	4535326
..3	1	3418757	3417694	4535326
...4	1	3419804	3418757	3417694	4535326
....5	1	400006499	3419804	3418757	3417694	4535326
...4	2	4319406	3418757	3417694	4535326
..3	2	4319405	3417694	4535326

 

[Peter_SSs]

I´m able to create it but its very slow,

How? The code from post #1 does not produce that result.

 

[Silverwolph]

How? The code from post #1 does not produce that result.

The Code posted is the original code form here, and to honor the guy who wrote it, i pasted the original, and i also wrote i tweeked it
like so

Sub BOMParent3()

Dim LastRow As Long
Dim r As Range, r1 As Range, r2 As Range
Dim Lvl As Long, Parent As Long
Dim Level As String, Level2 As String

LastRow = Cells(Rows.Count, "C").End(xlUp).Row ' set the row
For Each r In Range("C3:C" & LastRow) 'set´s the row from C3 to C
    Set r1 = r.Offset(0, -2) ' the -2 is offset to find the info about what level things are
    Level = Replace(r1.Value, " ", "")
    Level = Replace(Level, ".", "")
    Lvl = Level
    Parent = Lvl - 1
    For Each r2 In Range(r1.Address, "A2")
        Level2 = Replace(r2.Value, " ", "")
        Level2 = Replace(Level2, ".", "")
        Lvl = Level2
        If Parent = Lvl Then r.Offset(0, 1) = r2.Offset(0, 2) 'the first offset were -1 changed to 1 which moves the output in D and the 2 the the offset from A
    Next r2
Next r

End Sub

Thsi will run the code 1 time, i can multiply it to run the same code for D, E F and so on but with 11 levels and 5000+ lines it will run for a good 8 hours

 

[RoryA]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

Getting the string "bom parent" for an item [SOLVED]

Please delete, as i´ve also posted question on Mr. Excel and i was not aware of cross posting, sorry Mod Edit: No need to delete (see my post below). Here's your original post: Hey I found this code by a guy called to Sub BOMParent3() Dim LastRow As Long Dim r As Range, r1 As Range...

www.excelforum.com

If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Silverwolph]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

Getting the string "bom parent" for an item [SOLVED]

Please delete, as i´ve also posted question on Mr. Excel and i was not aware of cross posting, sorry Mod Edit: No need to delete (see my post below). Here's your original post: Hey I found this code by a guy called to Sub BOMParent3() Dim LastRow As Long Dim r As Range, r1 As Range...

www.excelforum.com

If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Other post deleted