I last wrote about Greg Clarke's "AncestryDNA Match Table Maker List" tool on 22 October 2022 - in Rabbit Holes With Randy - Updating My AncestryDNA Match Table List. When I tried this again several months ago, I was unable to make it work and it fell off my "attention screen." I don't know if it is worknig again through the Family Tree Fanatic's process.
1) I watched the San Diego Genealogical Society DNA Special Interest Group meeting last Saturday. The meeting featured presentations of "DNA AutoClustering" methods by Colin Whitney and "A New Tool for Analyzing Ancestry DNA Data" by David Nowotnik. David shared a link with the group for the Greg Clarke tool on WikiTree that is simpler than the earlier tool.
2) A user can access the WikiTree version at https://www.wikitree.com/g2g/1362851/ancestry-dna-match-table-maker-spreadsheet.
This tool enables the user to select and copy their AncestryDNA Match list (there may be a line limit - I don't know) and paste it into the Google Sheets spreadsheet and see a list of their DNA matches, and manipulate the list as desired as a spreadsheet. It works best on the Google Chrome browser, according to Greg Clarke.
3) By clicking the "Google Spreadsheet" link on the screen above, the Google Sheets spreadsheet opens and the user can read the directions to select, copy and paste the AncestryDNA information:
There are three sheets on this spreadsheet - "Instructions," "Raw Data" and "WhatsNew." The user needs to make a copy of the spreadsheet and store it in their own Google Drive account. You click on the "File" menu and then "Make A Copy."
By clicking "Make a copy" I now have the spreadsheet in my own Google Drive account and can work with it.
4) For this exercise, I used the "Common Ancestors" selection on my AncestryDNA Match list. Ancestry presents only some of the matches, starting with the highest cM values. The user can add more matches by going to the "End" of the list on the page, and more will matches appear. I have over 500 "Common Ancestor" matches, so this takes some time.
Having read the instructions, the user can go to the "Raw Data" sheet of the spreadsheet and note that after selecting and copying the AncestryDNA Match list from their AncestryDNA account, the user can past all of that information into Cell A6 of the "Raw Data" sheet. With a lot of lines, it may take several seconds to populate the spreadsheet with the macros (this is how the "magic" occurs) embedded into the spreadsheet.
Columns A through S on the spreadsheet are used to populate the rest of the columns. The useful columns are T through AE. The screen above shows column T which is where the results of all of the above starts.
5) Here is the top of my AncestryDNA Match list in the spreadsheet showing columns T through AE (note that I reduced the width of some columns for privacy purposes):
The columns shown are:
* T - Category* U - Name
* V - Manager
* W - Relationship
* X - Which side?
* Y - DNA Amount
* Z - cM
* AA - % shared
* AB - Tree
* AC - People in Rree
* AD - Found?
* AE - Note
6) I use this AncestryDNA Match Table Make spreadsheet for several reasons:
a) To see which matches I have that I don't have a Note for. I can click on the link for "Common Ancestors" for a specific match line and go to the AncestryDNA match on AncestryDNA and write a Note.
b) To see which matches I don't have a "Relationship" for. I can click on the link for "Common Ancestors" for a specific match line and go to the AncestryDNA match on AncestryDNA and identify a known relationship.
c) To see how many matches I have for a category - e.g., for "Common Ancestors" or "Matches above 15 cM."
d) To see how many matches I have that are Wrong. I add "XX" or "XXX" or "Wrong" to the Notes for those Matches that I think have a faulty ThruLine (usually because the Common Ancestor is wrong, or when a relationship in a ThruLine obviously has a wrong date, place, etc.).
e) To document my AncestryDNA matches at a specific time, since AncestryDNA constantly adds or eliminates DNA matches.
f) Other reasons - what do you use it for?
As an example of c) above, I can see that on the screen above, my matches start on line 3 of the spreadsheet. By going to the end of the data on the spreadsheet, I can see that my last match is on line 585. So there are 583 "Common Ancestors" matches on my spreadsheet saved on 5 December 2023. Here is a view of the last lines of the spreadsheet:
Note that there are four extra lines at the bottom of the screen above. For some reason, there are four lines on this spreadsheet that have the words "Filter shared matches" in the AE - Notes column. The Notes for all successive matches are displaced one line down. If the "Filter shared matches" are in the Notes at Row 200, the Note for the Row 200 Match are shown on Row 201, and so on. So the Note shown on Row 589 is for Row 585. I will try to correct this in the spreadsheet so the Note for a Row is in the correct Row.
6) I also search for "XX" and "wrong" in my Notes using the Windows "Find" feature. I found 37 Matches have been judged "Wrong" by me over the years. I should go back and evaluate those in case the DNA Match, or the Ancestry BIG Tree has changed the faulty relationship.
7) The user can Rename this DNA Match Table file using "File > Rename."
8) Lastly, the user can save this DNA Match Table to their desktop or laptop computer files so as to perform additional work with it. The user can save the file using "File > Download" in *.xlsx, *.ods, *.pdf, *.html, *.csv, or *.tsv format.
My thanks to Greg Clarke for creating this Google Sheets method to track AncestryDNA Matches and to David Nowotnik for "finding" the WikiTree page to use it. This is a relatively simple tool to perform a very useful procedure. I can make my AncestryDNA Match table within 10 minutes of starting to do it.
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 randy.seaver@gmail.com.
