Creating Parent - Child Data [VBA]

[DataBlake]

Hello all,
I'd be super glad if someone could help me come up with a way to create multiple part numbers using two qualifiers from a single line
so lets say i have this product which would be the parent on Sheet1

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]partnum[/TD]
[TD]brand[/TD]
[TD]style[/TD]
[TD]color[/TD]
[TD]size1[/TD]
[TD]size2[/TD]
[TD]size3[/TD]
[TD]angle[/TD]
[TD]reverse angle[/TD]
[TD]pattern[/TD]
[/TR]
[TR]
[TD]D4563[/TD]
[TD]Trian[/TD]
[TD]Glum[/TD]
[TD]Black[/TD]
[TD]22[/TD]
[TD]39[/TD]
[TD]32[/TD]
[TD]-17[/TD]
[TD]3.55[/TD]
[TD]6x110[/TD]
[/TR]
</tbody>[/TABLE]

and then i have a on a different sheet (TitleHelper) a list of variation identifiers

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Pattern[/TD]
[TD]Angle Min[/TD]
[TD]Angle Max[/TD]
[TD]ID1[/TD]
[TD]IDShort[/TD]
[TD]ID Code[/TD]
[/TR]
[TR]
[TD]6x110[/TD]
[TD]-20[/TD]
[TD]0[/TD]
[TD]Coloris[/TD]
[TD]Col[/TD]
[TD]C1[/TD]
[/TR]
[TR]
[TD]6x110[/TD]
[TD]-15[/TD]
[TD]5[/TD]
[TD]Floris[/TD]
[TD]Flo[/TD]
[TD]F1[/TD]
[/TR]
[TR]
[TD]6x110[/TD]
[TD]-30[/TD]
[TD]-10[/TD]
[TD]Probis[/TD]
[TD]Pro[/TD]
[TD]P1[/TD]
[/TR]
[TR]
[TD]6x110[/TD]
[TD]-50[/TD]
[TD]100[/TD]
[TD]Slotis[/TD]
[TD]Slo[/TD]
[TD]S1[/TD]
[/TR]
</tbody>[/TABLE]

and here is where i run into my problem is i need to create a new partnum with "*ID Code" for every time this item matches the pattern and the pattern is within min-max. So something like this:

=IF(AND(J2=TitleHelper!A2,H2<=TitleHelper!C2,H2>=TitleHelper!B2),A2&"*"&TitleHelper!F2)

except for every instance that the pattern matches, and the angle is within the range of min-max it inserts the new part number below the parent part number so Sheet1 would look like

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]partnum[/TD]
[TD]brand[/TD]
[TD]style[/TD]
[TD]color[/TD]
[TD]size1[/TD]
[TD]size2[/TD]
[TD]size3[/TD]
[TD]angle[/TD]
[TD]reverse angle[/TD]
[TD]Pattern[/TD]
[/TR]
[TR]
[TD]D4563[/TD]
[TD]Trian[/TD]
[TD]Glum[/TD]
[TD]Black[/TD]
[TD]22[/TD]
[TD]39[/TD]
[TD]32[/TD]
[TD]-17[/TD]
[TD]3.55[/TD]
[TD]6x110[/TD]
[/TR]
[TR]
[TD]D4563*C1[/TD]
[TD]Trian[/TD]
[TD]Glum[/TD]
[TD]Black[/TD]
[TD]22[/TD]
[TD]39[/TD]
[TD]32[/TD]
[TD]-17[/TD]
[TD]3.55[/TD]
[TD]6x110[/TD]
[/TR]
[TR]
[TD]D4563*P1[/TD]
[TD]Trian[/TD]
[TD]Glum[/TD]
[TD]Black[/TD]
[TD]22[/TD]
[TD]39[/TD]
[TD]32[/TD]
[TD]-17[/TD]
[TD]3.55[/TD]
[TD]6x110[/TD]
[/TR]
[TR]
[TD]D4563*S1[/TD]
[TD]Trian[/TD]
[TD]Glum[/TD]
[TD]Black[/TD]
[TD]22[/TD]
[TD]39[/TD]
[TD]32[/TD]
[TD]-17[/TD]
[TD]3.55[/TD]
[TD]6x110[/TD]
[/TR]
</tbody>[/TABLE]

*Note that there are 19 columns of important info to copy down to child partnums not just 10
*Note that I will love you forever if you help me, and if you leave me notes to learn

 

[DataBlake]

yeah i've tried to think this out in what i know of VBA and i can't seem to get it right.
my brain turns to static whenever i get around to thinking about the multiple match part/the insert part

Sub parentCHILD()
Dim childROW As Long
Dim parentROW As Long
Dim childPATTERN As Range
Dim oMAX As Range
Dim oMIN As Range
Dim parentPATTERN As Range
Dim parentPATTERN2 As Range
Dim parentANGLE As Range
Dim i As Long

With Sheets("TitleHelper")
Set childROW = Cells(Rows.Count, 1).End(xlUp).Row
Set childPATTERN = Range("A" & childROW)
Set oMAX = Range("B" & childROW)
Set oMIN = Range("C" & childROW)
End With


With Sheets("Sheet1")
Set parentRow = Cells(Rows.Count, 1).End(xlUp).Row
Set parentPATTERN = Range("J" & parentROW)
Set parentPATTERN2 = Range("K" & parentROW)
Set parentANGLE = Range("H" & parentROW)

For i = 1 To childROW
[COLOR=#ff0000]if parentPATTERN or parentPATTERN2 = childPATTERN and parentANGLE <= oMAX and parentANGLE >= oMIN then . . .[/COLOR]
Next i
End With

End Sub

this is what i have so far
red indicates brain fart

 

[DataBlake]

i got an index array formula working to where it takes the Parent Pattern in Sheet1 & it gives me the new part number i want, but i don't know how to incorporate it into VBA to where it says autofill until error or something. Anyways here is the formula.

In sheet1 cell A3 and then autofilled down 2 more rows

{=$A$2&"*"&IFERROR(INDEX(TitleHelper!$F$2:$F$15, SMALL(IF(ISNUMBER(MATCH(TitleHelper!$A$2:$A$15, $J$1, 0)), MATCH(ROW(TitleHelper!$A$2:$A$15), ROW(TitleHelper!$A$2:$A$15)), ""), ROWS($A$1:A1))),"err")}

 

[Leith Ross]

Hello BlakeSkate,

Just so I understand, you want a VBA macro to extend the pattern matches for the current part numbers. Am I correct?

 

[DataBlake]

you want a VBA macro to extend the pattern matches for the current part numbers. Am I correct?

i think so?
its literally just if the pattern from sheet1 (column J)
matches the pattern of TitleHelper (column A)

and the angle from Sheet1 (column H)
is in between the Minimum and Maximum angle from TitleHelper (columns B & C)

it will put all of the matches' ID Codes from TitleHelper (column F)
onto the end of the partnum from Sheet1 (Column A)
and insert a row below the original partnum for each match

and then move on to the next partnum/row

its literally like if you have a shoe and the shoe came in different sizes so you wanted unique identifiers. so adding the sizes at the end of the part number for that shoe.
slightly different but i hope that illustrates what i want.

If you recreate everything in post 1 it should check out.
I appreciate the help

 

[DataBlake]

and to clarify: the first table is sheet1
the second table is TitleHelper
and the third table is the desired result in sheet1