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):
[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!

 

[Kaper]

Let's try:

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

 

[jtanner_]

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:

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:

[TABLE="class: grid, width: 300, align: left"]
<tbody>[TR]
[TD]A1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A1[/TD]
[TD]A2-1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A1[/TD]
[TD]A2-1[/TD]
[TD]A3-1[/TD]
[/TR]
[TR]
[TD]A1[/TD]
[TD]A2-1[/TD]
[TD]A3-2[/TD]
[/TR]
[TR]
[TD]A1[/TD]
[TD]A2-2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A1[/TD]
[TD]A2-3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A1[/TD]
[TD]A2-3[/TD]
[TD]A3-3[/TD]
[/TR]
[TR]
[TD]A1[/TD]
[TD]A2-3[/TD]
[TD]A3-4[/TD]
[/TR]
[TR]
[TD]A1[/TD]
[TD]A2-3[/TD]
[TD]A3-5[/TD]
[/TR]
[TR]
[TD]B1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B1[/TD]
[TD]B2-1[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



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.

 

[jtanner_]

Still hoping to get some insight on this... Thanks in advance.

 

[Kaper]

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:

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:

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