Return Two Separate Sets of Child Values from Parent Cell

[jtanner_]

Hi Excel Guru's,

Looking for some advice on the best way to approach what I'm trying to do... after attempting to google or search the forums I think I'm failing to accurately search/describe what I'm trying to do and have resorted to creating this thread.

1. I'm trying to first reference a parent SKU (a specific cell) and return from a list the total number of fields/child SKU's that are related to that parent SKU
2. I want to then take those child SKU's and reference an additional subset of child SKU's

Things to note;
- The first parent SKU will also be a specific cell and will be a manually entered value
- There could be anywhere from 1 to 100 values related to the parent SKU that are returned as part of the first set of child SKU's
- The second set could also feature anywhere from 1 to 100 values as part of the second set of SKU's
- I am open to any and all suggestions and am not fixed on the particular layout i've identified in my example below

ie:

Data Set 1 (Parent to Child):

Parent SKU	Child SKU1
A1	A2-1
A1	A2-2
A1	A2-3
B1	B2-1
B1	B2-2
C1	C1-1
C1	C1-2
C1	C1-3

<tbody>
</tbody>

Data Set 2 (Child to Child2):

Child 1	Child 2
A2-1	A3-1
A2-1	A3-2
A2-3	A3-3
A2-3	A3-4
A2-3	A3-5

<tbody>
</tbody>

etcetc

Then I would have the results return as...

Parent: "A1" in lets call it Cell H1

Results returned in the H2, H3, H4, H5, etcetc would be:
A2-3
A3-1
A3-2
A2-2
A2-3
A3-3
A3-4
A3-5

I'm so far able to return the first set of child SKU's from the parent row by doing a countif on "A1" to get the total of unique vales to return, and then using the indirect and match function to create the cell reference for the first SKU, and I am using the row function to continue to add one and move down the list until the row() < countif.

Hope that makes sense... appreciate any and all input. Would like to try to keep this in basic excel but will use VBA if necessary.

Thanks all!

 

[Kaper]

Assuming:
1) Parent to child table is in A1:Bx
2) Child to Child2 table is in D2:Ey
3) output for sample data shall be
A2-1
A3-1
A3-2
A2-2
A2-3
A3-3
....

try such VBA code:

Sub test()Dim parents, children, i&, j&, k&, outtable(), myparent
Range("H2:H" & Rows.Count).ClearContents
myparent = Range("H1").Value
parents = Range(Cells(2, "A"), Cells(Rows.Count, "B").End(xlUp)).Value
children = Range(Cells(2, "D"), Cells(Rows.Count, "E").End(xlUp)).Value
For i = 1 To UBound(parents)
  If parents(i, 1) = myparent Then
    k = k + 1
    ReDim Preserve outtable(1 To k)
    outtable(k) = parents(i, 2)
    For j = 1 To UBound(children)
      If children(j, 1) = parents(i, 2) Then
        k = k + 1
        ReDim Preserve outtable(1 To k)
        outtable(k) = children(j, 2)
      End If
    Next j
  End If
Next i
Range("H2").Resize(UBound(outtable), 1).Value = Application.Transpose(outtable)
End Sub

 

[jtanner_]

Thank you Kaper this worked beautifully and not nearly as resource intense as my excel formula based solution. If I were to have each set of data on a separate sheet (Parents on Sheet2, Children on Sheet3) do I simply have to update the range to specify the worksheet?

Thanks,

 

[Kaper]

Yes, but not in such form as in formulas ( MySheetName!A1) but in a similar way to:

parents = Sheets("Sheet 2").Range(Cells(2, "A"), Cells(Rows.Count, "B").End(xlUp)).Value
children = Sheets("Sheet 3").Range(Cells(2, "D"), Cells(Rows.Count, "E").End(xlUp)).Value

 

[jtanner_]

Thanks for the quick response, however I'm running into a run-time error when applying the above. Apologies as this is probably something simple than I'm doing wrong.

Sub test()
Dim parents, children, i&, j&, k&, outtable(), myparent
Sheets("Summary").Range("G4:G" & Rows.Count).ClearContents
myparent = Sheets("Summary").Range("G3").Value
parents = Sheets("Data1").Range(Cells(2, "A"), Cells(Rows.Count, "B").End(xlUp)).Value
children = Sheets("Data2").Range(Cells(2, "D"), Cells(Rows.Count, "E").End(xlUp)).Value
For i = 1 To UBound(parents)
If parents(i, 1) = myparent Then
k = k + 1
ReDim Preserve outtable(1 To k)
outtable(k) = parents(i, 2)
For j = 1 To UBound(children)
If children(j, 1) = parents(i, 2) Then
k = k + 1
ReDim Preserve outtable(1 To k)
outtable(k) = children(j, 2)
End If
Next j
End If
Next i
Sheets("Summary").Range("G4").Resize(UBound(outtable), 1).Value = Application.Transpose(outtable)
End Sub

 

[Kaper]

Are your Child data in columns D and E of Data2?

Where code stops (which line of code is highlighted yellow, if you press Debug in Error message Dialog).

 

[jtanner_]

Hi Kaper,

Yes they are in columns D and E of Data2. The code steps at the range selection for parents

parents = Sheets("Data1").Range(Cells(2, "A"), Cells(Rows.Count, "B").End(xlUp)).Value

 

[jtanner_]

If I update the range to for example A1:B1000, I get the error on the line:

For i = 1 To UBound(parents)

Run-time error '13' Type Mismatch. I'll do some googling

 

[Kaper]

Try such modification:

Sub test()
Dim parents, children, i&, j&, k&, outtable(), myparent
Sheets("Summary").Range("G4:G" & Rows.Count).ClearContents
myparent = Sheets("Summary").Range("G3").Value
With Sheets("Data1")
  parents = Range(.Cells(2, "A"), .Cells(.Rows.Count, "B").End(xlUp)).Value
End With
With Sheets("Data2")
  children = Range(.Cells(2, "D"), .Cells(.Rows.Count, "E").End(xlUp)).Value
End With
For i = 1 To UBound(parents)
If parents(i, 1) = myparent Then
k = k + 1
ReDim Preserve outtable(1 To k)
outtable(k) = parents(i, 2)
For j = 1 To UBound(children)
If children(j, 1) = parents(i, 2) Then
k = k + 1
ReDim Preserve outtable(1 To k)
outtable(k) = children(j, 2)
End If
Next j
End If
Next i
Sheets("Summary").Range("G4").Resize(UBound(outtable), 1).Value = Application.Transpose(outtable)
End Sub

 

[jtanner_]

Hi Kaper,

This worked fantastic. Thank you so much for your prompt responses and easy to understand code. After using my update template... I was wondering if it would be possible to have the second subset of values returned in the next column over. In the example above where the outtable is returned in range G4, is it possible to create a second table that preserves the children values and can then return it in column H:? It would be ideal if it could maintain the rows as well so it would return something like...

Where value in the first cell is entered as "A1", after running the VBA it returns something like the following below:

A1
A2-1
	A3-1
	A3-2
A2-2
A2-3
	A3-3
	A3-4
	A3-5

<tbody>
</tbody>

Or something along those lines... or if its easier Return A2-1 through A2-3 in the first column, then A3-1 through A3-5 in the second starting from the row below A2-3?