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

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

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

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