I’m going to try a format here that will make it easier for me to explain some of my spreadsheet tools, and give you an easy way to copy the header (you can adjust the column widths to suit your self). Please let me know if this works for you, and I’ll try some more of them.
Copy the above column titled “Header Row” and paste it into your spreadsheet using the Transpose option. It should create the Header Row for the Common Ancestor Spreadsheet. [Edit: it appears this doesn’t work from the image above – so just type them in a row across a spreadsheet.]
There are several types of rows for you to input:
1. Include one row for each of your Ancestor Couples – I highlight these rows
2. There is one row for each Match with each known Common Ancestor (MRCA);
3. I add a row for my MRCA Child & birth year with a NOTE to refer to appropriate Ahnentafel for more
4. I add a row for Ancestor multiple marriages, and put marriage year in born column
This separates full cousins and half cousins.
5. If something looks fishy, or needs more investigation, I highlight it in orange/mud color.
6. If an Ancestor/Ahnentafel number and a TG are in conflict, I highlight it in red. The genealogy may be correct but the shared DNA segment did not come from the MRCA
Other NOTES:
1. The main sort for this spreadsheet is Ahnen + born+ born +born columns
NB: Highlight all columns before sorting.
2. Another sort is on Match Name to analyze multiple MRCAs – only one TG per MRCA
3. If you want to compare spreadsheets for different Test Takers, be sure to fill in the TT column first. Combine spreadsheets, sort, analyze, then sort on TT and separate the spreadsheets.
4. Sidebar: I have an Ancestor Spreadsheet – one row for each Ancestor info, including the Ahnentafel number!
5. I have typed all the data into my Common Ancestor spreadsheet – a lot of work
Idea: If you have a download of AncestryDNA Matches, start with that data for ThruLines Matches
6. If you want to be able to sort this by side (your paternal and maternal sides); add a column for P or M (or 2 or 3)
7. Do not hesitate to add any other columns (or rows) that may be useful to you. I made up this spreadsheet, feel free to change it as you like.
ADVANTAGES OF THIS COMMON ANCESTORS SPREADSHEET
1. It captures all of your Matches with Common Ancestors [some may be gone tomorrow…]
2. It arranges the Matches’ descendants from the MRCA like a Family Group Sheet
Easy to compare with your own research
Helpful in spotting many errors
Easy to see Matches who are relatively close cousins to each other – good conversation starter
Easy to highlight real and/or potential errors
Easy to spot a Match at two companies with different names
3. Shows TG threads in a family [maybe Clusters too, haven’t tried them yet)
Makes it easy to spot TG threads through a family (closer Ancestors will have more TG threads)
Here is an example from my CA Spreadsheet:
[35BC] Segment-ology: Common Ancestor Spreadsheet by Jim Bartlett 20211219
segment-ology 
Another question: How do you handle double cousins? Put them in under each set of ancestors with an indication that they are double cousins?
LikeLike
eallynm – YES. for one or the other I’d add a “SEE #” to refer to the place where I’d put all the cousin Matches (I don’t want to list the Matches twice). Jim PS: I made all this up, and have modified it along the way… If you develop an improvement, please let me/all of us know….
LikeLiked by 1 person
I think I’m going to go ahead and list the matches twice now that Ancestry is no longer showing us multiple possible relationships. Once one gets down to a few segments, the matching DNA may come from one set of CAs or another. I might highlight the SEE # if that’s the one that is the more likely source of the segment(s).
LikeLiked by 1 person
eallynm – I think your idea to highlight the most probable line for a DNA Match is a good one. Clustering (Shared Matches) may indicate the most likely line. Also, I would almost always pick the “closer” line if there is not any other hint. The best of all solutions is to get segment data (and therefore a Triangulated Group) for the shared DNA. Jim
LikeLike
Jim, what is the column to the right of cousinship that contains H Hmh Hm and HCh and what do those abbreviations mean?
LikeLike
eallynm – Good question… the H is my code for a HEADER ROW (not one of the Matches). H (plain) is an Ancestor HEADER. Hch means a child of the Ancestor (who is usually the child who is my Ancestor with Matches in a previous Family Group). Hm is a marriage (who is NOT an Ancestor – it indicates half-cousins below that Header); same for Hmh. I can sort all the Header rows out – to make sure they have a consistent pattern and/or to see all my Ancestors and any second marriages… Jim
LikeLiked by 1 person
What’s the difference between Hm and Hmh?
LikeLike
Does Hm mean that the ancestor had more than one marriage and Hmh indicate the header row for the half-cousins?
LikeLiked by 1 person
Yes, Hmh is another marriage (may be first or second) to someone who is not my Ancestor – thus the Matches below this header are Half Cousins. Note the other marriage may be a husband or a wife. It’s just a crib, that I enter once in the spreadsheet – to remind me, so I don’t have to scratch my head and review it each time it comes up. I’m also going back and adding in Y and mt Haplogroups where known. When I see someone with a long male or female line, I like to tell the the Haplogroups when I know them, and/or ask if they would be interested in testing to determine their “signature” Haplogroup… It all adds to the work of the spreadsheet, but also adds to it’s value as a tool. Suggested improvements always welcomed. Jim
LikeLike
Pingback: How Many NPEs Do You Have? | segment-ology