Find Ancestors.

[harzer]

Hello everyone,
Despite my attempts to find a solution to my problem, I confess that I can't.

My database has 3 columns:
Column "A" contains young people.
Column "B" contains the fathers.
Column "C" contains the mothers.

Classeur2
	A	B	C
1	Youth	Fathers	Mothers
2	J1	P1	M1
3	J2	P22	M22
4	P1	P2	M2
5	J3	P24	M10
6	M1	P3	M3
7	J5	P25	M35
8	P2	P4	M4
9	J4	P27	M40
10	J8	P26	M50
11	M2	P5	M5
12	P3	P6	M6
13	J6	P37	M51
14	M3	P7	M7
15	P4	P8	M8
16	J7	P26	M52
17	M4	P9	M9
18	J9	P28	M35
19	P5	P10	M10
20	M5	P11	M11
21	J10	P55	M56
22	J11	P21	M87
23	P8	P12	M12
24	M6	P13	M13
Parents

In the cell "N2" I have the young J1, I want to retrieve all the ancestors of this young from the columns: "A", "B" and "C" and place them in the range "U2:W" .

Unless I am mistaken, the result is in the range: "U2:W"

Classeur2
	N	O	P	Q	R	S	T	U	V	W
1	J1							Youth	Fathers	Mothers
2								P1	P2	M2
3								P2	P4	M4
4								P4	P8	M8
5								P8	P12	M12
6								M2	P5	M5
7								P5	P10	M10
8								M5	P11	M11
9								M1	P3	M3
10								P3	P6	M6
11								M3	P7	M7
Parents

I allow myself to ask you for a solution in vba with a fairly quick solution because my database in columns: "A", "B" and "C" is very large 32000 lines.

I remain at your disposal for any additional information.
Thank you in advance for your suggestions.
Cheers.

 

[duggie33]

Hi harzer,

How would one know who the ancestors of J1 are?

Doug

 

[harzer]

Good morning,
Thank you for your reply.
We are therefore looking for the ancestors of the young (J1); we will look for the information in column "A", we can notice that (J1) is in cell "A2", his father is (P1) who is in cell "B1", his mother is (M1) who is in cell "C2".
Now we're going to look for the grandparents of "J1".
(P1) is in cell "A4", his father is (P2) and is in cell "B4", his mother is (M2) and is in "C4"
(M1) is in cell "A6", his father is (P3) and is in cell "B6", his mother is (M3) and is in "C6"
So on until the end
I wish you good programming and I look forward to reading you
Good for you.

 

[fjns]

Hi, see the linked file (with 16 auxiliary columns, without VBA) for a possible solution...

The formulas used in the table:
tech!A1: =Parents!N1
tech!A2: =IFERROR(INDEX(J$1:O$1,MATCH(ROW()-1,B$1:G$1,0)),"")
tech!A3: =IFERROR(IF(IFERROR(MATCH(ROW()-1,IF(J2=1,B$1:G$1,INDIRECT("D$"&J2&":G$"&J2)),0),0)=0,INDEX(INDIRECT("L$"&K3&":O$"&K3),IFERROR(MATCH(ROW()-1,INDIRECT("D$"&K3&":G$"&K3),0),0)),INDEX(IF(J2=1,J$1:O$1,INDIRECT("L$"&J2&":O$"&J2)),IFERROR(MATCH(ROW()-1,IF(J2=1,B$1:G$1,INDIRECT("D$"&J2&":G$"&J2)),0),0))),"")
tech!B1: =IF(J1="",0,IF(IFERROR(MATCH(J1,Parents!$A:$A,0),0)>0,MAX($A1:A1)+1,0))
tech!B2: =IFERROR(INDEX(Parents!B:B,MATCH(A2,Parents!A:A,0)),"")
tech!C1: =IF(K1="",0,IF(IFERROR(MATCH(K1,Parents!$A:$A,0),0)>0,MAX($A1:B1)+1,0))
tech!C2: =IFERROR(INDEX(Parents!C:C,MATCH(A2,Parents!A:A,0)),"")
tech!D1: =IF(L1="",0,IF(IFERROR(MATCH(L1,Parents!$A:$A,0),0)>0,MAX($A1:C1)+1,0))
tech!D2: =IF($A2="","",IF(L2="",0,IF(IFERROR(MATCH(L2,Parents!$A:$A,0),0)>0,MAX($H1,$C2:C2)+1,0)))
tech!E1: =IF(M1="",0,IF(IFERROR(MATCH(M1,Parents!$A:$A,0),0)>0,MAX($A1:D1)+1,0))
tech!E2: =IF($A2="","",IF(M2="",0,IF(IFERROR(MATCH(M2,Parents!$A:$A,0),0)>0,MAX($H1,$C2:D2)+1,0)))
tech!F1: =IF(N1="",0,IF(IFERROR(MATCH(N1,Parents!$A:$A,0),0)>0,MAX($A1:E1)+1,0))
tech!F2: =IF($A2="","",IF(N2="",0,IF(IFERROR(MATCH(N2,Parents!$A:$A,0),0)>0,MAX($H1,$C2:E2)+1,0)))
tech!G1: =IF(O1="",0,IF(IFERROR(MATCH(O1,Parents!$A:$A,0),0)>0,MAX($A1:F1)+1,0))
tech!G2: =IF($A2="","",IF(O2="",0,IF(IFERROR(MATCH(O2,Parents!$A:$A,0),0)>0,MAX($H1,$C2:F2)+1,0)))
tech!H1: =MAX(B1:G1)
tech!H2: =IF(A2="","",MAX(H1,D2:G2))
tech!I1: =MIN(B1:G1)
tech!I2: =IF(A2="","",MINIFS(D2:G2,D2:G2,">0"))
tech!J1: =IFERROR(INDEX(Parents!B:B,MATCH(A1,Parents!A:A,0)),"")
tech!J2: =IF(A2="","",1)
tech!J3: =IF(A3="","",IF(IFERROR(MATCH(ROW()-1,IF(J2=1,B$1:G$1,INDIRECT("D$"&J2&":G$"&J2)),0),0)=0,K3,J2))
tech!K1: =IFERROR(INDEX(Parents!C:C,MATCH(A1,Parents!A:A,0)),"")
tech!K2: =IF(A2="","",1)
tech!K3: =IFERROR(MATCH(ROW()-1,INDIRECT("I$1:I"&ROW()-1),0),"")
tech!L1: =IF(J1="","",IFERROR(INDEX(Parents!B:B,MATCH(J1,Parents!A:A,0)),""))
tech!L2: =IFERROR(INDEX(Parents!B:B,MATCH(B2,Parents!A:A,0)),"")
tech!M1: =IF(J1="","",IFERROR(INDEX(Parents!C:C,MATCH(J1,Parents!A:A,0)),""))
tech!M2: =IFERROR(INDEX(Parents!C:C,MATCH(B2,Parents!A:A,0)),"")
tech!N1: =IF(K1="","",IFERROR(INDEX(Parents!B:B,MATCH(K1,Parents!A:A,0)),""))
tech!N2: =IFERROR(INDEX(Parents!B:B,MATCH(C2,Parents!A:A,0)),"")
tech!O1: =IF(K1="","",IFERROR(INDEX(Parents!C:C,MATCH(K1,Parents!A:A,0)),""))
tech!O2: =IFERROR(INDEX(Parents!C:C,MATCH(C2,Parents!A:A,0)),"")
tech!P1: =MAX(H:H)+1
Parents!U2: =IF(ROW()>tech!$P$1,"",tech!A2)
Parents!V2: =IF(ROW()>tech!$P$1,"",tech!B2)
Parents!W2: =IF(ROW()>tech!$P$1,"",tech!C2)

Ancestors.xlsx

 

[fjns]

Sorry, the MINIFS function does not exists in Office 2016. I created a new table for Office 2016.

The new formulas used in the new table:
tech!B1: =IF(J1="","",IF(IFERROR(MATCH(J1,Parents!$A:$A,0),0)>0,MAX($A1:A1)+1,""))
tech!C1: =IF(K1="","",IF(IFERROR(MATCH(K1,Parents!$A:$A,0),0)>0,MAX($A1:B1)+1,""))
tech!D1: =IF(L1="","",IF(IFERROR(MATCH(L1,Parents!$A:$A,0),0)>0,MAX($A1:C1)+1,""))
tech!E1: =IF(M1="","",IF(IFERROR(MATCH(M1,Parents!$A:$A,0),0)>0,MAX($A1:D1)+1,""))
tech!F1: =IF(N1="","",IF(IFERROR(MATCH(N1,Parents!$A:$A,0),0)>0,MAX($A1:E1)+1,""))
tech!G1: =IF(O1="","",IF(IFERROR(MATCH(O1,Parents!$A:$A,0),0)>0,MAX($A1:F1)+1,""))
tech!D2: =IF($A2="","",IF(L2="","",IF(IFERROR(MATCH(L2,Parents!$A:$A,0),0)>0,MAX($H1,$C2:C2)+1,"")))
tech!E2: =IF($A2="","",IF(M2="","",IF(IFERROR(MATCH(M2,Parents!$A:$A,0),0)>0,MAX($H1,$C2:D2)+1,"")))
tech!F2: =IF($A2="","",IF(N2="","",IF(IFERROR(MATCH(N2,Parents!$A:$A,0),0)>0,MAX($H1,$C2:E2)+1,"")))
tech!G2: =IF($A2="","",IF(O2="","",IF(IFERROR(MATCH(O2,Parents!$A:$A,0),0)>0,MAX($H1,$C2:F2)+1,"")))
tech!I2: =IF(A2="","",MIN(D2:G2))

Ancestors2016.xlsx

 

[harzer]

Hello fjns,
Thank you for your feedback, as well as the patience to offer me part of the solution with formulas for office 2016.
When I say part of the solution I mean that I tested your solution on my database of 32000 rows, the result does not correspond to reality since my database has 32000 rows and the formulas do not process the 32000 lines.
I was hoping for a solution with Macro in vba, if I had asked for a solution with vba, it was because I was planning to use the code in vba that you offered me to use it on other sheets of the same workbook, see also in other workbooks.
Since you have understood the principle, would you (please) offer me a solution in vba.
While waiting to read you, I wish you a good evening.
See you soon.