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?

[TABLE="width: 246"]
<tbody>[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63, width: 65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "]A[/TD]
[TD="class: xl63, width: 89, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "]B[/TD]
[TD="class: xl63, width: 65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "]C[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]1[/TD]
[TD="class: xl64"]Parent[/TD]
[TD="class: xl64"]Component[/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]2[/TD]
[TD="class: xl64"]car[/TD]
[TD="class: xl64"]wheels[/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]3[/TD]
[TD="class: xl64"]car[/TD]
[TD="class: xl64"]cabin[/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]4[/TD]
[TD="class: xl64"]car[/TD]
[TD="class: xl64"]motor[/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]5[/TD]
[TD="class: xl64"]car[/TD]
[TD="class: xl64"]seats[/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]6[/TD]
[TD="class: xl64"]wheels[/TD]
[TD="class: xl64"]rubber[/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]7[/TD]
[TD="class: xl64"]wheels[/TD]
[TD="class: xl64"]rims[/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]8[/TD]
[TD="class: xl64"]motor[/TD]
[TD="class: xl64"]block[/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]9[/TD]
[TD="class: xl64"]motor[/TD]
[TD="class: xl64"]piston[/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]10[/TD]
[TD="class: xl64"]motor[/TD]
[TD="class: xl64"]distributor[/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]11[/TD]
[TD="class: xl64"]cabin[/TD]
[TD="class: xl64"]steering wheel[/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]12[/TD]
[TD="class: xl64"]cabin[/TD]
[TD="class: xl64"]belts[/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]13[/TD]
[TD="class: xl64"]cabin[/TD]
[TD="class: xl64"]dashboard[/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]14[/TD]
[TD="class: xl64"]shirt[/TD]
[TD="class: xl64"]fabric[/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]15[/TD]
[TD="class: xl64"]fabric[/TD]
[TD="class: xl64"]whool[/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]16[/TD]
[TD="class: xl64"]seats[/TD]
[TD="class: xl64"]leather[/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]17[/TD]
[TD="class: xl64"]seats[/TD]
[TD="class: xl64"]cusion[/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]18[/TD]
[TD="class: xl64"]piston[/TD]
[TD="class: xl64"]steel[/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]19[/TD]
[TD="class: xl64"]block[/TD]
[TD="class: xl64"]steel[/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]20[/TD]
[TD="class: xl64"]distributor[/TD]
[TD="class: xl64"]rubber[/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]21[/TD]
[TD="class: xl64"]distributor[/TD]
[TD="class: xl64"]plastic[/TD]
[TD="class: xl64"][/TD]
[/TR]
</tbody>[/TABLE]

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,




[TABLE="width: 325"]
<colgroup><col width="65" span="5" style="width: 65pt;"></colgroup><tbody>[TR]
[TD="class: xl63, width: 65"] [/TD]
[TD="class: xl63, width: 65"]A[/TD]
[TD="class: xl63, width: 65"]B[/TD]
[TD="class: xl63, width: 65"]C[/TD]
[TD="class: xl63, width: 65"]D[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63"]Parent[/TD]
[TD="class: xl63"]Child[/TD]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63"]Car[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63"]car[/TD]
[TD="class: xl63"]wheels[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63"]wheels[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63"]car[/TD]
[TD="class: xl63"]cabin[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63"]rubber[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]4[/TD]
[TD="class: xl63"]car[/TD]
[TD="class: xl63"]motor[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63"]rims[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl63"]car[/TD]
[TD="class: xl63"]seats[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63"]cabin[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]6[/TD]
[TD="class: xl63"]wheels[/TD]
[TD="class: xl63"]rubber[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63"]steering wheel[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]7[/TD]
[TD="class: xl63"]wheels[/TD]
[TD="class: xl63"]rims[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63"]belts[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]8[/TD]
[TD="class: xl63"]motor[/TD]
[TD="class: xl63"]block[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63"]dashboard[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]9[/TD]
[TD="class: xl63"]motor[/TD]
[TD="class: xl63"]piston[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63"]motor[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]10[/TD]
[TD="class: xl63"]motor[/TD]
[TD="class: xl63"]distributor[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63"]block[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]11[/TD]
[TD="class: xl63"]cabin[/TD]
[TD="class: xl63"]steering wheel[/TD]
[TD="class: xl63, align: right"]4[/TD]
[TD="class: xl63"]steel[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]12[/TD]
[TD="class: xl63"]cabin[/TD]
[TD="class: xl63"]belts[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63"]piston[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]13[/TD]
[TD="class: xl63"]cabin[/TD]
[TD="class: xl63"]dashboard[/TD]
[TD="class: xl63, align: right"]4[/TD]
[TD="class: xl63"]steel[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]14[/TD]
[TD="class: xl63"]shirt[/TD]
[TD="class: xl63"]fabric[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63"]distributor[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]15[/TD]
[TD="class: xl63"]fabric[/TD]
[TD="class: xl63"]whool[/TD]
[TD="class: xl63, align: right"]4[/TD]
[TD="class: xl63"]rubber[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]16[/TD]
[TD="class: xl63"]seats[/TD]
[TD="class: xl63"]leather[/TD]
[TD="class: xl63, align: right"]4[/TD]
[TD="class: xl63"]plastic[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]17[/TD]
[TD="class: xl63"]seats[/TD]
[TD="class: xl63"]cusion[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63"]seats[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]18[/TD]
[TD="class: xl63"]piston[/TD]
[TD="class: xl63"]steel[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63"]leather[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]19[/TD]
[TD="class: xl63"]block[/TD]
[TD="class: xl63"]steel[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63"]cusion[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]20[/TD]
[TD="class: xl63"]distributor[/TD]
[TD="class: xl63"]rubber[/TD]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63"]shirt[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]21[/TD]
[TD="class: xl63"]distributor[/TD]
[TD="class: xl63"]plastic[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63"]fabric[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 325"]
<colgroup><col width="65" span="5" style="width: 65pt;"></colgroup><tbody></tbody>[/TABLE]

 

[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