Wednesday, January 26, 2022

Rabbit Holes With Randy - Updating my DNA Match Lists

 There are too many rabbit holes - and not enough time to deal with them.  Every day another genealogy bunny pops up and I want to play with it.

This past week, I spent a lot of time updating my DNA Match spreadsheets for AncestryDNA and MyHeritageDNA.  I have charts for FamilyTreeDNA and 23andMe matches also, but I haven't updated them in over a year.  Another rabbit hole for another time.  All of them are important, but the features of each company are different. 

1)  I keep track of the number of my AncestryDNA and MyHeritage matches.  Today I have 38,671 AncestryDNA matches, of which only 1,712 share 20 cM or more with me.  I have 453 ThruLines also (but some are wrong - maybe about 10 of them).  How do I know how many ThruLines I have?  I've tried counting them one by one and lose track.  

Greg Clarke developed a way to take AncestryDNA matches and put the information in a Google Drive spreadsheet.  I wrote about that in March 2021.  So I downloaded Greg's spreadsheet again (from, and noted that he is up to version 1.09.  The first sheet looks like this, and has directions:

I followed the directions (made a copy, copied the AncestryDNA match data for "Common Ancestors," and went to the "Newest - Recognize btn with LIVE links) sheet, and followed directions there.  On the "Newest" worksheet I pasted the copied match data into cell A6 and the spreadsheet quickly filled in:

The beauty of this spreadsheet is that I can now count the number of the DNA matches, plus look for matches for which I don't have a Note.  This is a pretty neat spreadsheet.  I haven't color coded it yet for a specific grandparent's ancestral line.  The first entry was on row 3, and last entry was on Row 455, so I have 453 ThruLines denoted as "Common Ancestors."  I found no match on this list without a Note about the potential common ancestor or whether I have the DNA match person's line back to the Common Ancestor in my RootsMagic family tree.  Note that I could have done this for my 1,712 "Close matches" also, or a Tagged group, etc.  The spreadsheet has a limit of 1,400 entries, as I recall.

2)  Over the past five years, I have tried to keep a hand-created spreadsheet of my AncestryDNA matches, and updated it sporadically.  Here is the top of that list, which includes all of my top DNA matches down to about 40 cMs (87 high-value matches):

Note that I have added the known relationship (if known), the most recent common ancestors, and whether the matches have a tree, a ThruLine, are in RootsMagic, are in GEDmatch, are in FTDNA, are in 23andMe, are in MyHeritageDNA, are in DNAPainter, and I add Matching segment information if I know it.  Unfortunately, AncestryDNA does not have a chromosome browser, but the other four companies do so it's important to know which other system my AncestryDNA matches are in.

Why the green color?  Here is my spreadsheet further down the list:  

The green color is for my Richmond/White lines, the blue color is for my Seaver/Hildreth lines, the pink for my Carringer/Smith lines, and the orange for my Kemp/Auble lines.  I have several blue lines further down the list.

3)  Then it was on to the MyHeritageDNA matches spreadsheet:

Again, I've used the color coding for the four sets of great-grandparents, but don't have nearly the same number of DNA matches (10,254 today), and I know the common ancestors for only a few of them (I have 15 Theory of Family Relativity matches, which are similar to Ancestry's ThruLines).  On this spreadsheet, I'm adding the cM value, start and end points for each shared segment with me from the chromosome browser.  At this time, I go down to 36 cM on this spreadsheet, and have 37 high-value entries.

Please note that I have hidden the identifying information for my DNA matches in all of the above charts. 

4)  All in all, this took about eight hours during the past two weeks.  It's well worth my time.  If I do this more consistently, and update the FamilyTreeDNA and 23andMe matches spreadsheets, I should have a better handle on my high-value DNA matches.  

Where are my carrots to give me energy to complete all of these tasks?  Time for a snack!


Copyright (c) 2022, Randall J. Seaver

Please comment on this post on the website by clicking the URL above and then the "Comments" link at the bottom of each post.  Share it on Twitter, Facebook, or Pinterest using the icons below.  Or contact me by email at

No comments: