So i created a solution that ALMOST works perfectly using a third array

this pretty much effectively How to Create Parent-Child Data based on tables

essentially after declaring the arrays and sizing the 3rd array to be 4x longer and 5 cells wider than my data range;

i use the brand names from Description Helper in a dictionary object to determine if i'm use table 1 (columns A - G) or table 2 (columns H - N)

i loop through array 1 and reset my counters

I loop through my two tables (description Helper)

if the dictionary item exists from array 1 i'm using table 2:

- if BP 1 or BP 2 and the offset is between the min/max

- and the counter is < 5

then i tell it to loop through the columns of array 1 and write them to a new array (ary3) using p as a counter for the position (row) of the matched row

then i make changes to the old data within the new array

this is where im confused now:

then i loop through ary2 again (Description Helper) to try and match each new line with the "make"

so lets take "12348" as an example: this part number was matched 3 times

so the T U V X W of Condensed Sheets is reserved for the "short" followed by EACH "make" of each match so it should look like this

but instead looks like

so it works properly on line 10, but not 11 or 12

am i skipping part of a loop to include all 3 makes?

Code:Sub pParent2() Dim Dic As Object Dim ary1 As Variant, ary2 As Variant, ay3 As Variant Dim ws As Worksheet, os As Worksheet Dim i As Long, x As Long, j As Long, p As Long, k As Long, n As Long, cT As Long Dim lastRow As Long Dim destRow As Long Set ws = Sheets("CondensedSheets") Set os = Sheets("Description Helper") ary1 = ws.Range("A1").CurrentRegion.Value2 ary2 = os.Range("A13").CurrentRegion.Value2 lastRow = ws.Range("A" & Rows.Count).End(xlUp).Row destRow = lastRow + 1 ReDim ary3(1 To (lastRow * 4), 1 To (UBound(ary1, 2) + 5)) Set Dic = CreateObject("scripting.dictionary") 'assign table id as dictionary object For x = LBound(ary2) To UBound(ary2) If Not Dic.exists(ary2(x, 15)) Then Dic.Add ary2(x, 15), ary2(x, 15) Next x For i = 2 To UBound(ary1) c = 0 j = 0 For x = LBound(ary2) To UBound(ary2) If Dic.exists(ary1(i, 2)) Then ''''''''''''''''''''''tsw table '''''''''''''''''''''''''''''''''' If (ary1(i, 10) = ary2(x, 8) _ Or ary1(i, 11) = ary2(x, 8)) _ And ary1(i, 8) >= ary2(x, 9) _ And ary1(i, 8) <= ary2(x, 10) _ And j < 5 Then j = j + 1 p = p + 1 For k = 1 To UBound(ary1, 2) ary3(p, k) = ary1(i, k) If InStr(1, ary1(i, 1), "^4", 1) > 0 Then ary3(p, 1) = ary1(i, 1) & ary2(x, 13) Else ary3(p, 1) = ary1(i, 1) & "^" & ary2(x, 13) End If ary3(p, 20) = ary2(x, 12) Next k For n = LBound(ary2) To UBound(ary2) If (ary1(i, 10) = ary2(n, 8) _ Or ary1(i, 11) = ary2(n, 8)) _ And ary1(i, 8) >= ary2(n, 9) _ And ary1(i, 8) <= ary2(n, 10) _ And c < 4 Then c = c + 1 ary3(p, 20 + c) = ary2(n, 11) End If Next n End If ''''''''''''''''''''''tsw table '''''''''''''''''''''''''''''''''' Else ''''''''''''''''''''''reg table '''''''''''''''''''''''''''''''''' If (ary1(i, 10) = ary2(x, 1) _ Or ary1(i, 11) = ary2(x, 1)) _ And ary1(i, 8) >= ary2(x, 2) _ And ary1(i, 8) <= ary2(x, 3) _ And j < 5 Then j = j + 1 p = p + 1 For k = 1 To UBound(ary1, 2) ary3(p, k) = ary1(i, k) If InStr(1, ary1(i, 1), "^4", 1) > 0 Then ary3(p, 1) = ary1(i, 1) & ary2(x, 6) Else ary3(p, 1) = ary1(i, 1) & "^" & ary2(x, 6) End If ary3(p, 20) = ary2(x, 5) Next k For n = LBound(ary2) To UBound(ary2) If (ary1(i, 10) = ary2(n, 1) _ Or ary1(i, 11) = ary2(n, 1)) _ And ary1(i, 8) >= ary2(n, 2) _ And ary1(i, 8) <= ary2(n, 3) _ And c < 4 Then c = c + 1 ary3(p, 20 + c) = ary2(n, 4) End If Next n End If ''''''''''''''''''''''reg table '''''''''''''''''''''''''''''''''' End If Next x Next i ws.Range("A" & destRow).Resize(p, UBound(ary3, 2)).Value = ary3 End Sub

## Like this thread? Share it with others