another bill of material question

[padil110]

so i got some great help on a bill of material question a month ago. now that i have been running the new system, the bills of materials are not set up traditionally. Usually the whole bill is exploded with all levels. If i have a bunch of bills in a query that are not exploded in levels, how can i flag all necessary materials for a specific bill if they are not linked together in a bill master? Im trying to put together a single column that flags all components of "car" when i look up the bill for "car", in column C. you can see that Car (level1) has 4 components (level2 - wheels, cabin, etc..) while cabin has its own bill starting in A11 (level 3 - steering wheel, belts, etc....). Is it possible to flag all components of "car" for all parts in column C?

	A	B	C
1	Parent	Component
2	car	wheels
3	car	cabin
4	car	motor
5	car	seats
6	wheels	rubber
7	wheels	rims
8	motor	block
9	motor	piston
10	motor	distributor
11	cabin	steering wheel
12	cabin	belts
13	cabin	dashboard
14	shirt	fabric
15	fabric	whool
16	seats	leather
17	seats	cusion
18	piston	steel
19	block	steel
20	distributor	rubber
21	distributor	plastic

<tbody>
</tbody>

Any help would be much appreciated. I know it must be an array formula, but i have trouble writing formulas with row and column, (I'm trying to learn)

 

[DanteAmor]

What is the result you expect?

 

[padil110]

Thanks for your reply Dante, Here is the expected result (in column C and D). [FONT=&quot]I have created a query to extract bills of material form our ERP system. The bills for the full assembly are massive, up to 500 items. The problem is that they are not all exploded or linked in a parent/ child fashion. for instance, the bill for the top item will have the first level parent/ child, but then the child has its own bill which isn't indented in the first bill. Im trying to make a simple, refreshable, planning program to tell us what materials we need per the total bill of material (all levels). Here is what I'm talking about. column A and B are the way the query generates the data, all parent/ child bills are organized in 1-2 levels. I want to display C and D, which is a full linked bill of material. Its really tough, and i am struggling. Any help would be much [/FONT]appreciated,

	A	B	C	D
1	Parent	Child	1	Car
2	car	wheels	2	wheels
3	car	cabin	3	rubber
4	car	motor	3	rims
5	car	seats	2	cabin
6	wheels	rubber	3	steering wheel
7	wheels	rims	3	belts
8	motor	block	3	dashboard
9	motor	piston	2	motor
10	motor	distributor	3	block
11	cabin	steering wheel	4	steel
12	cabin	belts	3	piston
13	cabin	dashboard	4	steel
14	shirt	fabric	3	distributor
15	fabric	whool	4	rubber
16	seats	leather	4	plastic
17	seats	cusion	2	seats
18	piston	steel	3	leather
19	block	steel	3	cusion
20	distributor	rubber	1	shirt
21	distributor	plastic	2	fabric

<colgroup><col width="65" span="5" style="width: 65pt;"></colgroup><tbody>
</tbody>

<colgroup><col width="65" span="5" style="width: 65pt;"></colgroup><tbody></tbody>

 

[MickG]

Try this for results starting "C1":-

NB:- You appear to have a link like "car-shirt" missing from your data !!!

[COLOR="Navy"]Sub[/COLOR] MG14Apr45
[COLOR="Navy"]Dim[/COLOR] rParents [COLOR="Navy"]As[/COLOR] Range, rNode [COLOR="Navy"]As[/COLOR] Range, rOut [COLOR="Navy"]As[/COLOR] Range, mRng [COLOR="Navy"]As[/COLOR] Range
    [COLOR="Navy"]Dim[/COLOR] lRow [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Dic [COLOR="Navy"]As[/COLOR] Object, oMax, oRw
[COLOR="Navy"]Dim[/COLOR] Dn [COLOR="Navy"]As[/COLOR] Range, Q [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Set[/COLOR] rOut = Range("D1")
  rOut(1, 0) = 1
   
   [COLOR="Navy"]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
   Dic.CompareMode = vbTextCompare
      [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Range("A2", Range("A" & Rows.Count).End(xlUp))
         [COLOR="Navy"]If[/COLOR] Not Dic.exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
              Dic.Add Dn.Value, CreateObject("scripting.dictionary")
              Dic(Dn.Value).Add Dn.Offset(, 1).Value, Array(Dn, Dn.Offset(, 1))
         [COLOR="Navy"]ElseIf[/COLOR] Not Dic(Dn.Value).exists(Dn.Offset(, 1).Value) [COLOR="Navy"]Then[/COLOR]
                Dic(Dn.Value).Add Dn.Offset(, 1).Value, Array(Dn, Dn.Offset(, 1))
         [COLOR="Navy"]End[/COLOR] If
      [COLOR="Navy"]Next[/COLOR] Dn
  Call DisplayTree([a2], Dic, rOut, lRow, 1)
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
[COLOR="Navy"]Sub[/COLOR] DisplayTree(ByVal sParent [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] Dic, rOut [COLOR="Navy"]As[/COLOR] Range, ByRef lRow [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] ByVal lLevel [COLOR="Navy"]As[/COLOR] Long)

    [COLOR="Navy"]Dim[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Dn [COLOR="Navy"]As[/COLOR] Range, rr [COLOR="Navy"]As[/COLOR] Range
    [COLOR="Navy"]Dim[/COLOR] vChild

     [COLOR="Navy"]If[/COLOR] lRow = 0 [COLOR="Navy"]Then[/COLOR] rOut = sParent: lRow = 1

        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] vChild [COLOR="Navy"]In[/COLOR] Dic(sParent)
            lRow = lRow + 1
            rOut(lRow, 1) = vChild
            rOut(lRow, 0) = lLevel + 1
          [COLOR="Navy"]If[/COLOR] Dic.exists(vChild) [COLOR="Navy"]Then[/COLOR]
            Call DisplayTree(vChild, Dic, rOut, lRow, lLevel + 1)
         [COLOR="Navy"]End[/COLOR] If

[COLOR="Navy"]Next[/COLOR] vChild
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick