Common Ancestor Spreadsheet
By popular demand, here is a portion of my Common Ancestor Spreadsheet:
I’ve added letters at the top of each column for ease in describing them:
A – Initials for the testing company – if a Match tests at multiple companies, I list them for each one.
B – This indicates if the Match also has a ThruLines (TL) or UnListed Tree (ULT) or Theory of Family Relativity (ToFR), etc. Perhaps a quirk of mine in data collection, but this lets me sort the spreadsheet to select all the TLs, for instance, and compare with the AncestryDNA TL list for completeness.
C. The name of the Match – may also include the Admin or Point of Contact
D. Total cMs
E. Number of Segments
F. Ahnentafel number for the MRCA
H. A code for Headers (C is a Green separation for each generation; H are my Ancestor Couples; Hch are my Ancestors who are a child of the Ancestor couple – also highlighted in yellow; Also Hm is a row for a 2nd marriage of an Ancestor – usually with a note that Matches who descend from subsequent children would be half-cousins (use the marriage year in the K column)
I. Ancestor Couple
J. Children of Ancestors – these are the children the Matches descend from – daughters are noted with the surname they married (important to identify grandchildren, etc.) Each Ahnentafel should have a row for my Ancestor (and birth year).
K. Birth year of children
L – O – like J and K
P. This is just one column to add the rest of the descendants down to the Match, if you want to do that (I’ve deleted my entries here as it gets too close to living people)
Q. Triangulated Group IDs – I add a “c” when the TG is implied from a Cluster or consensus among Shared Matches.
R. Add any notes you want – some of mine includes notes when a Y-DNA or mtDNA (testing) path is in the line of descent; or if the line of descent is “iffy”, IMO; or TL was wrong, and this is the fixed/correct version; etc. (whatever you want)
S. Tree URL – this is often very handy to review and/or to quick get back to the Match.
I cannot emphasize enough that spreadsheets are very personal, and you should exercise your own judgment to adopt this one to your own preferences, and to add any other columns you want (realizing that each additional column is more work to maintain).
Here are some additional columns from my spreadsheet which I occasionally use:
Hg – Haplogroup (Y or mt) if it applies to the Match and is important to me
Gm – GEDmatch ID# for the Match
Related – I often note when two (or more) Matches are closely related to each other (this cautions me to “dilute” the value of, say, two children or siblings, as they do not add to any consensus.
MyT – I note in this column (y) if I’ve added this line of descent to my main Tree at Ancestry – it helps me get more ThruLines…
Other – I note in this column any other MRCA Ahnentafel this Match is related to me on – the Match is in my spreadsheet for each such additional relationship (it remains a task to figure out which MRCA goes with the Shared DNA Segment).
Email – email of the Match
The main purposes (for me) of this Common Ancestor Spreadsheet include:
1. Document (in one handy place) all of my Matches with MRCA
2. Use the Family Group Sheet format to check the accuracy of Match descendants against my own Family Group Sheets and/or genealogy research.
3. Determine closely related Matches (this sometimes results in a good communication thread that shares info, draws in other research. and maybe more interest in DNA testing.)
4. Tracking the TGs vs MRCA; particularly with respect to the “Rules”.
Feel free to post a comment about any additional use you see for this spreadsheet; and/or any improvements or drawbacks…
[15K] Segment-ology: Distribution of TGs – Part 4 by Jim Bartlett 20211013