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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 01:17 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com