Return Two Separate Sets of Child Values from Parent Cell

jtanner_

New Member
Joined
Sep 15, 2016
Messages
9
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):
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Parent SKU[/TD]
[TD]Child SKU1[/TD]
[/TR]
[TR]
[TD]A1[/TD]
[TD]A2-1[/TD]
[/TR]
[TR]
[TD]A1[/TD]
[TD]A2-2[/TD]
[/TR]
[TR]
[TD]A1[/TD]
[TD]A2-3[/TD]
[/TR]
[TR]
[TD]B1[/TD]
[TD]B2-1[/TD]
[/TR]
[TR]
[TD]B1[/TD]
[TD]B2-2[/TD]
[/TR]
[TR]
[TD]C1[/TD]
[TD]C1-1[/TD]
[/TR]
[TR]
[TD]C1[/TD]
[TD]C1-2[/TD]
[/TR]
[TR]
[TD]C1[/TD]
[TD]C1-3[/TD]
[/TR]
</tbody>[/TABLE]

Data Set 2 (Child to Child2):

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Child 1[/TD]
[TD]Child 2[/TD]
[/TR]
[TR]
[TD]A2-1[/TD]
[TD]A3-1[/TD]
[/TR]
[TR]
[TD]A2-1[/TD]
[TD]A3-2[/TD]
[/TR]
[TR]
[TD]A2-3[/TD]
[TD]A3-3[/TD]
[/TR]
[TR]
[TD]A2-3[/TD]
[TD]A3-4[/TD]
[/TR]
[TR]
[TD]A2-3[/TD]
[TD]A3-5[/TD]
[/TR]
</tbody>[/TABLE]
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!
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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:
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


 
Upvote 0
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,
 
Upvote 0
Yes, but not in such form as in formulas ( MySheetName!A1) but in a similar way to:
Code:
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
 
Last edited:
Upvote 0
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
 
Upvote 0
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).
 
Upvote 0
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
 
Upvote 0
Try such modification:
Code:
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
 
Upvote 0
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:

[TABLE="width: 500"]
<tbody>[TR]
[TD]A1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A2-1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]A3-1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]A3-2[/TD]
[/TR]
[TR]
[TD]A2-2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A2-3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]A3-3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]A3-4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]A3-5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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?
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top