Part 3: The Generations Table
Recap
In part 1 of this project, I introduced the timeline concept, and detailed the process to download and prepare the data. In part 2, I detailed the timeline structure, controls, formatting and navigation. In this part I present a "byproduct" output, a table to trace and list multiple generations of ancestors for any name.
Purpose
This concept came to me as I was searching for a way to trace lineal trunk lines. As noted in part 1, the FTM program I use to maintain the family tree has color codes to do this, and I continue to look for an Excel solution.
One step in that process is to look up the tree (or down to the roots, if you prefer) to identify ancestors of a selected individual, based on a flat table in which the link is one generation at a time by naming father and mother. The table must therefore link records together based on parentage.
Family tree software like FTM specializes in connecting generations, with reports and charts to show both ancestors and descendants. This table is not a substitute for that. Any family history enthusiast should invest in the software to manage it. The value of doing this in Excel is the potential to support dynamic identification of lines for the timeline or other types of report that go beyond the special software output options.
Further Data Preparation
Clearly a key element of linking is to have unique names to start with. The process of suffixing duplicate names to make them unique is detailed in part 1. That was not essential for the timeline, but here we need not only the name of each individual to be unique but also the names of their father and mother.
I added two columns to the download/processing table, shown here:
FTM[FatherUnique]
If the father's name is already unique, take it; if not, then if both father and mother are known, look them up as a pair ([@Father] & [@Mother]) against the spouse column pairs ([Name] & [Spouse]), and return the unique name, i.e. the husband of that couple.
FTM[MotherUnique]
If the mother's name is already unique, take it; if not, then if both father and mother are known, look them up as a pair ([@Mother] & [@Father]) against the spouse column pairs ([Name] & [Spouse]), and return the unique name, i.e. the wife of that couple.
If the father or mother are not unique and both father and mother are not known, there could be unreliable results, so they are excluded.
Generations Table Structure
The table is quite simple. There are six columns that are essential to the purpose. The table has a fixed number of rows, which I set at 510. This is the total count of eight generations of ancestors, or "the sum of the powers of two from one to eight". You might start to see that the binary system is the key to this table.
The spine of the table is the [Name] column, where each ancestor, if found, is listed. Above this column's header is an input cell, with a Data Validation pick list of the [NameUnique] column in the data table. Since Data Validation like Conditional Formatting does not accept table references, I use a proxy name in Name Manager.
FTMNameUnique =FTM[NameUnique]
Data Validation for the input cell is then set as shown:
The six columns:
[index] is an ordinal record number, 1 to 510
[Gen] is the generation number, parents = 1, grandparents = 2, etc.
[Num] is an index number of the ancestor within their generation
[Relation] gives relationship, FF = father's father, etc.
[Off] is the offset of the name whose parent this is
[Name] is the ancestor's unique name
Formula Detail
index
In Name Manager, with active cell A2, Above = A1. Then [index] =N(Above)+1 returns the table row number.
Gen
If [@index] + 1 is a power of 2, log base 2 is a whole number, and mod is 0. If mod is not 0, then copy the above cell, else increment by 1.
Num
If [@Gen] is the same as the row above, increment by 1, else start over at 1.
Relation
Convert [@Num] - 1 to binary with [@Gen] digits, then substitute "F" for "0" and "M" for "1".
Off
If [@Gen] = 1 then 0, else if [@Relation] ends with "M", decrement by 1, else if above not 0, copy it, else if above = 0 then -2. Offset example:
index 1,2 = parents of reference name (F,M), offset = 0
index 3,4 = parents (FF,FM) of index 1 (F), which is offset -2,-3
index 5,6 = parents (MF,MM) of index 2 (M), which is offset -3,-4
Name
If [@Relation] ends with "F", index in FTM[FatherUnique], else index in FTM[MotherUnique] for the match number of: the reference name if [@Gen] = 1, else of the offset [@Name], in FTM[NameUnique]; if no match then blank. Name example (see table image above):
index = 12; Relation = MFM; Off = -7 (points to index 5)
INDEX array = FTM[MotherUnique]
MATCH value = OFFSET([@Name],[@Off],0) = name of index 5 = MF name
index 12 Name = the mother's unique name of the mother's father's unique name
I added some columns to show lines by generation: parent lines, grandparent lines, etc. This was easy to do by matching the "left generation-number of characters" of [@Relation]. For example, to get the four grandparent lines (generation 2), I would match the left 2 characters.
[GPLine]
Looking at index row 50, [@Relation] is "MFFMM": mother's father's father's mother's mother. The grandparent line is defined by the left 2 characters, "MF", mother's father.
Where this becomes unwieldy is when I try to link all descendants of all ancestors to the same grandparent line. This is where I stopped and reverted to manual entry of the color code from FTM. However, the game's not done, and I hope to present a solution in the near future.
Conclusion
I always advocate matching the tool to the purpose. For family history that is specialized software. As in so many cases, Excel comes in as a way to customize and enhance data from another source.
In this project I undertook to present a graphical view of the life and times of my forbears. As I drove towards the objective, I kept one eye on the rear-view mirror, wary of creating an unsustainable process, and one eye ahead, keen to offer something of value to others.
It may be obvious that the process is centered on an individual whose parentage is being traced. This table allows for that with a drop-down to select the reference individual. The chart is not that flexible. Ancestral lines and their colors on the chart are based on the four grandparents of one individual, but at this stage need manual maintenance if the individual is changed.
The challenge to make the whole project easily updatable and adaptable to different reference individuals is a good one. On the other hand, anyone using such a tool is mostly interested in using their own name as the reference and will set it up once. I would like to offer this project, but must consider these points first so that I can write proper instructions.
Stay tuned, and give me your feedback please!
Comments