If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. 


Thread Tools  Display Modes 
#1




Finding loops in parent/child relationships
Hi,
I'm hoping someone can help me with a thorny problem. I have a worksheet with 2 columns of data: 1 called Parent and the other called Child. A number represents each 'person' and each row represents a parent/child relationship e.g. Parent Child 1 2 2 3 3 4 4 5 4 2 5 8 6 7 7 8 8 1 I am trying to find the rows that cause a loop in the parent/child hierarchy i.e. a child eventually has one of its parents as its own child. I've tried using VLOOKUP to build a lineage of grandparents, great grandparents etc but that doesn't cope with the case where a child has more than one parent. Please could someone help? Have you solved this kind of problem before? Thanks in advance, Graham 
Ads 
#2




Finding loops in parent/child relationships
Easy to do in VBA. Build a lineage string one record at a time. Each time
an element is added, a check is made to insure the element has not already been added: 1>2 1>2>3 1>2>3>4 1>2>3>4>5 The next step fails...Although 4 can have many children, it can't have #2 because #2 is already on the list. Let us know if a VBA solution is O.K.  Gary''s Student  gsnu200758 "GrahamC" wrote: > Hi, > I'm hoping someone can help me with a thorny problem. > > I have a worksheet with 2 columns of data: 1 called Parent and the other > called Child. A number represents each 'person' and each row represents a > parent/child relationship e.g. > > Parent Child > 1 2 > 2 3 > 3 4 > 4 5 > 4 2 > 5 8 > 6 7 > 7 8 > 8 1 > > I am trying to find the rows that cause a loop in the parent/child hierarchy > i.e. a child eventually has one of its parents as its own child. > > I've tried using VLOOKUP to build a lineage of grandparents, great > grandparents etc but that doesn't cope with the case where a child has more > than one parent. > > Please could someone help? Have you solved this kind of problem before? > > Thanks in advance, > Graham 
#3




Finding loops in parent/child relationships
Thanks. Looks like I will have to learn VBA! Is there no other way using
Excel functions? Regards, Graham "Gary''s Student" wrote: > Easy to do in VBA. Build a lineage string one record at a time. Each time > an element is added, a check is made to insure the element has not already > been added: > 1>2 > 1>2>3 > 1>2>3>4 > 1>2>3>4>5 > > The next step fails...Although 4 can have many children, it can't have #2 > because #2 is already on the list. Let us know if a VBA solution is O.K. > >  > Gary''s Student  gsnu200758 > > > "GrahamC" wrote: > > > Hi, > > I'm hoping someone can help me with a thorny problem. > > > > I have a worksheet with 2 columns of data: 1 called Parent and the other > > called Child. A number represents each 'person' and each row represents a > > parent/child relationship e.g. > > > > Parent Child > > 1 2 > > 2 3 > > 3 4 > > 4 5 > > 4 2 > > 5 8 > > 6 7 > > 7 8 > > 8 1 > > > > I am trying to find the rows that cause a loop in the parent/child hierarchy > > i.e. a child eventually has one of its parents as its own child. > > > > I've tried using VLOOKUP to build a lineage of grandparents, great > > grandparents etc but that doesn't cope with the case where a child has more > > than one parent. > > > > Please could someone help? Have you solved this kind of problem before? > > > > Thanks in advance, > > Graham 
#4




Finding loops in parent/child relationships
I don't see an alternative.
 Gary''s Student  gsnu200758 "GrahamC" wrote: > Thanks. Looks like I will have to learn VBA! Is there no other way using > Excel functions? > > Regards, > Graham > > "Gary''s Student" wrote: > > > Easy to do in VBA. Build a lineage string one record at a time. Each time > > an element is added, a check is made to insure the element has not already > > been added: > > 1>2 > > 1>2>3 > > 1>2>3>4 > > 1>2>3>4>5 > > > > The next step fails...Although 4 can have many children, it can't have #2 > > because #2 is already on the list. Let us know if a VBA solution is O.K. > > > >  > > Gary''s Student  gsnu200758 > > > > > > "GrahamC" wrote: > > > > > Hi, > > > I'm hoping someone can help me with a thorny problem. > > > > > > I have a worksheet with 2 columns of data: 1 called Parent and the other > > > called Child. A number represents each 'person' and each row represents a > > > parent/child relationship e.g. > > > > > > Parent Child > > > 1 2 > > > 2 3 > > > 3 4 > > > 4 5 > > > 4 2 > > > 5 8 > > > 6 7 > > > 7 8 > > > 8 1 > > > > > > I am trying to find the rows that cause a loop in the parent/child hierarchy > > > i.e. a child eventually has one of its parents as its own child. > > > > > > I've tried using VLOOKUP to build a lineage of grandparents, great > > > grandparents etc but that doesn't cope with the case where a child has more > > > than one parent. > > > > > > Please could someone help? Have you solved this kind of problem before? > > > > > > Thanks in advance, > > > Graham 
Thread Tools  
Display Modes  


Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
finding multiple parentchild relationships?  Keith R  Excel Worksheet Functions  4  April 16th 07 04:13 PM 
Retrieving info from "child" to a "parent" document  create button to unhide rows  Excel Discussion (Misc queries)  0  January 21st 07 10:40 PM 
Windows browser Parent/Child structure  ERK  New Users to Excel  2  December 3rd 06 12:44 AM 
Relationships / Calculations  shone  Excel Discussion (Misc queries)  4  June 6th 06 07:51 PM 
Sorting Parent Child  kcmtnbiker  Excel Worksheet Functions  2  March 31st 06 01:54 AM 