Parent Child Relationship

[kamleshvdalvi]

Hi

Need a tweak here , I have a list of 100000+ Parents child relation in my data base ,
1. I need the code to extract the data for selected parents code stored in separate column , say 20 Codes
2. I need to identify the parent code with a Sr no and this should get copied to all the respective Parent Child relationship stored as run in step 1

Looking forward for the feedback

 

[Peter_SSs]

Welcome to the MrExcel board!

Best to start your own thread so I have split it from this one (readers can refer back via the link if they want to)

We have no idea what you data looks like or how it is laid out so could you post a small set of sample dummy data and the required results with XL2BB ?
That way we can see what has to be dealt with and can copy the sample data for testing.

 

[kamleshvdalvi]

Thanks for your response ,
Pl follow the Link to earlier thread
List all Children for a Parent

The code is working fine but do not want to run the entire data base every time , I have a list of 100000+ Parents child relation in my data base ,

1. I need the code to extract the data for selected parents code stored in separate column , say 20 Codes
2. I need to identify the parent code with a Sr no and this should get copied to all the respective Parent Child relationship stored as run in step 1 for indexing

Looking forward for the feedback

 

[Peter_SSs]

I had already provided a link back to the earlier one.

However, this is still the case

We have no idea what you data looks like or how it is laid out so could you post a small set of sample dummy data and the required results with XL2BB ?
That way we can see what has to be dealt with and can copy the sample data for testing.

 

[kamleshvdalvi]

Sorry for the confusion

Parent / Child - Data is in Column D & E

The VBA code needs to look in the Column A & B

Out Put is required for all the identified Parents items in Column B in Column G , H , I.

 

[Peter_SSs]

Try this with your sample data from above first.
I have not tested with any more complex Parent/Child relationships so you will need to check carefully.
I would definitely work up slowly to larger data sets as the code may well bog down with them.
Of course you may well have to also eventually adjust the ranges where the data actually is and results to go to.

Option Explicit
Private d As Object
Private Family As String

Sub ParentChildList()
  Dim a As Variant, b As Variant, FamilyMembers As Variant
  Dim i As Long, j As Long, k As Long
  
  Set d = CreateObject("Scripting.Dictionary")
  d.CompareMode = 1
  a = Range("D4", Range("E3").End(xlDown)).Value
  For i = 1 To UBound(a)
    If d.Exists(a(i, 1)) Then
      d(a(i, 1)) = d(a(i, 1)) & "|" & a(i, 2)
    Else
      d(a(i, 1)) = a(i, 2)
    End If
  Next i
  a = Range("A3", Range("B2").End(xlDown)).Value
  ReDim b(1 To Rows.Count, 1 To 3)
  For i = 1 To UBound(a)
      Family = vbNullString
      If d.Exists(a(i, 2)) Then
        FamilyMembers = Split(BuildFamily(CStr(a(i, 2))), "|")
        For j = 2 To UBound(FamilyMembers)
          k = k + 1
          b(k, 1) = a(i, 1)
          b(k, 2) = a(i, 2)
          b(k, 3) = FamilyMembers(j)
        Next j
      End If
  Next i
  With Range("G4:I4").Resize(k)
    .Value = b
    .Rows(0).Value = Array("SR NO", "Parent", "Child")
  End With
End Sub

Function BuildFamily(sParent As String) As String
  Dim itm As Variant
  
  For Each itm In Split(sParent, "|")
    Family = Family & "|" & itm
    If d.Exists(itm) Then BuildFamily (d(itm))
  Next itm
  BuildFamily = Family
End Function

My sample data and results:

kamleshvdalvi.xlsm
	A	B	C	D	E	F	G	H	I
1
2	SR NO	Parent
3	1	A		Parent	Child		SR NO	Parent	Child
4	2	B		A	B		1	A	B
5				B	C		1	A	C
6				C	D		1	A	D
7				E	F		1	A	H
8				F	G		2	B	C
9				B	H		2	B	D
10							2	B	H
11
Sheet1

 

[kamleshvdalvi]

Thanks' that has really helped

 

[Peter_SSs]

Thanks' that has really helped

You're welcome. Thanks for the follow-up.