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):
Parent SKUChild SKU1
A1A2-1
A1A2-2
A1A2-3
B1B2-1
B1B2-2
C1C1-1
C1C1-2
C1C1-3

<tbody>
</tbody>

Data Set 2 (Child to Child2):

Child 1Child 2
A2-1A3-1
A2-1A3-2
A2-3A3-3
A2-3A3-4
A2-3A3-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!
 
Let's try:
Code:
Sub test()
Dim parents, children, i&, j&, k&, outtable(), myparent
Sheets("Summary").Range("G4:H" & 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 2, 1 To k)
outtable(1, 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 2, 1 To k)
outtable(2, k) = children(j, 2)
End If
Next j
End If
Next i
Sheets("Summary").Range("G4").Resize(UBound(outtable, 2), 2).Value = Application.Transpose(outtable)
End Sub
 
Upvote 0

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Hi Team,

Bumping my old thread as I'm hoping to complicate things a bit more... Kaper the last I left off with your code was as follows:

Code:
Sub ReturnBOM()
Dim parents, children, i&, j&, k&, outtable(), myparent
Sheets("Summary").Range("h4:i" & Rows.Count).ClearContents
myparent = Sheets("Summary").Range("h3").Value
With Sheets("BOM")
  parents = Range(.Cells(2, "A"), .Cells(.Rows.Count, "B").End(xlUp)).Value
End With
With Sheets("BOM")
  children = Range(.Cells(2, "A"), .Cells(.Rows.Count, "B").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 2, 1 To k)
outtable(1, 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 2, 1 To k)
outtable(2, k) = children(j, 2)
End If
Next j
End If
Next i
Sheets("Summary").Range("h4").Resize(UBound(outtable, 2), 2).Value = Application.Transpose(outtable)
End Sub

A few additional things i'm trying to accomplish now is to move away from the single cell input to a larger list. For example my current range of H3 would now become a list starting in A2 to the nth potential item in the list.

I would like the results to be displayed similarly to before except I would like the parent items to be repeated. Using the same sample data as my original post that would return results similar to the following:

A1
A1A2-1
A1A2-1A3-1
A1A2-1A3-2
A1A2-2
A1A2-3
A1A2-3A3-3
A1A2-3A3-4
A1A2-3A3-5
B1
B1B2-1

<tbody>
</tbody>



etc

Not sure if this is too far of a stretch though... I just thought that this would take my single application and allow me to run it through my entire report at once.
 
Upvote 0
In the code above you try to take data for both parents and children from the same range in the same sheet.
This is obviously wrong:
Code:
With Sheets("BOM")
  parents = Range(.Cells(2, "A"), .Cells(.Rows.Count, "B").End(xlUp)).Value
End With
With Sheets("BOM")
  children = Range(.Cells(2, "A"), .Cells(.Rows.Count, "B").End(xlUp)).Value
End With

The second poit is that you before wanted to prepare a report for single parent (listed in H3). Now it seems that whole list shall be prepared.

Have a look at 2 codes below. I expect second one BOM2 is close to what you would like to get:
Code:
Sub ReturnBOM()
Dim parents, children, i&, j&, k&, outtable(), myparent
Sheets("Summary").Range("H4:J" & Rows.Count).ClearContents
myparent = Sheets("Summary").Range("h3").Value
With Sheets("BOM")
  parents = Range(.Cells(2, "A"), .Cells(.Rows.Count, "B").End(xlUp)).Value
End With
With Sheets("BOM")
  children = Range(.Cells(2, "D"), .Cells(.Rows.Count, "E").End(xlUp)).Value
End With
k = 1
For i = 1 To UBound(parents)
  If parents(i, 1) = myparent Then
    ReDim Preserve outtable(1 To 3, 1 To k)
    outtable(1, k) = myparent
    outtable(2, k) = parents(i, 2)
    k = k + 1
    For j = 1 To UBound(children)
      If children(j, 1) = parents(i, 2) Then
        ReDim Preserve outtable(1 To 3, 1 To k)
        outtable(1, k) = myparent
        outtable(2, k) = parents(i, 2)
        outtable(3, k) = children(j, 2)
        k = k + 1
      End If
    Next j
  End If
Next i
Sheets("Summary").Range("h4").Resize(UBound(outtable, 2), 3).Value = Application.Transpose(outtable)
End Sub


Sub ReturnBOM2()
Dim parents, children, i&, j&, k&, outtable(), myparent
Sheets("Summary").Range("H4:J" & Rows.Count).ClearContents
With Sheets("BOM")
  parents = Range(.Cells(2, "A"), .Cells(.Rows.Count, "B").End(xlUp)).Value
End With
With Sheets("BOM")
  children = Range(.Cells(2, "D"), .Cells(.Rows.Count, "E").End(xlUp)).Value
End With
k = 1
For i = 1 To UBound(parents)
  If parents(i, 1) <> myparent Then
    ReDim Preserve outtable(1 To 3, 1 To k)
    myparent = parents(i, 1)
    outtable(1, k) = myparent
    k = k + 1
  End If
  ReDim Preserve outtable(1 To 3, 1 To k)
  outtable(1, k) = myparent
  outtable(2, k) = parents(i, 2)
  k = k + 1
  For j = 1 To UBound(children)
    If children(j, 1) = parents(i, 2) Then
      ReDim Preserve outtable(1 To 3, 1 To k)
      outtable(1, k) = myparent
      outtable(2, k) = parents(i, 2)
      outtable(3, k) = children(j, 2)
      k = k + 1
    End If
  Next j
Next i
Sheets("Summary").Range("h4").Resize(UBound(outtable, 2), 3).Value = Application.Transpose(outtable)
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,020
Members
448,543
Latest member
MartinLarkin

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