Finding loops in parent/child relationships

[GrahamC]

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

 

[Gary''s Student]

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.

 

[GrahamC]

Thanks. Looks like I will have to learn VBA! Is there no other way using
Excel functions?

Regards,
Graham

 

[Gary''s Student]

I don't see an alternative.