Parent Child Relationship

kamleshvdalvi

New Member
Joined
Dec 28, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,222
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Dec 28, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,222
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Dec 28, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
 

Attachments

  • EXAMPLE.png
    EXAMPLE.png
    68.9 KB · Views: 10

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,222
Office Version
  1. 365
Platform
  1. Windows
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. :eek:
Of course you may well have to also eventually adjust the ranges where the data actually is and results to go to.

VBA Code:
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
ABCDEFGHI
1
2SR NOParent
31AParentChildSR NOParentChild
42BAB1AB
5BC1AC
6CD1AD
7EF1AH
8FG2BC
9BH2BD
102BH
11
Sheet1
 

Forum statistics

Threads
1,144,630
Messages
5,725,394
Members
422,623
Latest member
Dave52

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
Top