Downloading My AncestryDNA Match List With Google Sheets

 I have wanted to know the number of my Common Ancestors for a long time, and have asked Ancestry to provide it in their DNA page, but they have not provided it yet.  

 I watched the Family History Fanatics YouTube video titled "How To Download Your AncestryDNA Match List with Google Sheets."  In the video Greg Clarke from Ontario demonstrates how to do this task.  Here is the video:

Devon Lee passed the link to the Google Sheet file for those that requested it:

"You need to SAVE a copy of this spreadsheet to your own Google Drive account to use it. 

 "Be advised, this spreadsheet has complex formulas that won't work if you download it for use in Excel or other spreadsheet programs. 

 >>>>>>>>> Ancestry DNA Match Maker  <<<<<<<"

You do have to follow the directions exactly.  It works great.  Watch it, and come back to my post to see my results.  

You do need to have a Google Account and Access to Google Sheets (their spreadsheet program).

 When you download the spreadsheet, the "Instructions" appear on the first page of the file (the pages are at the bottom of the screen.

First - you need to go to the "File" and choose "Make a Copy" and then change the file name at the top of the screen:

So now I am in the Copy and can work with it.  The "Instructions" page has 4 Steps and several Notes that need to be understood and followed in order to make this work.

Step 1 is to go to your DNA Match list on AncestryDNA and select the matches you want to put into your Google Sheet file.  I decided to select my "Common Ancestors" list first, and I have about 400 of them.  I picked the "Common Ancestors" filter on the AncestryDNA match list and had to keep scrolling down the  list of matches to the end and then did a Ctrl + A to select all of them and then a Ctrl + C to copy them.

Step 2 is to open the second page of the Sheet file, and here you have a choice.  The 2nd page is labeled "Worksheet: View Match Btn" and the 3rd page is labeled "Worksheet: No Btn."  It all depends if you have the green "View Match" button on your list of AncestryDNA matches.  If you do, use the Worksheet: View Match Btn" page.  If not, choose the "Worksheet: No Btn" page.  the system works differently, but on the "No Btn" page you can see your Notes.

I want to see my Notes, so I have to use the "Worksheet: No Btn" page.  Here is what it looks like from the sample data:

It is probable that the Google Sheet you download will have sample data in it, so you need to "Clear Previous Data."  When you click the light blue "Clear Previous Data" button, I was asked to authorize my Google account to run the built-in macro that clears previous data.  After doing that, you will be able to tap the "Clear Previous Data" button and the existing data in the spreadsheet will disappear after running the macro script.

The next Step in the Instructions is to click in cell A6 in the Worksheet, and then do a Ctrl + Shift + V to copy the AncestryDNA match data into the A column of the spreadsheet.  A macro then runs to put that data into the remainder of the spreadsheet (Columns G to Q).  Here is the top of my spreadsheet after doing that:

I hid the columns G and H above because they have identifying info for my DNA matches.  

The spreadsheet now has the following columns:

G   Category/Name

H   Name/Manager

I    Relationship [this is the AncestryDNA estimate]

J   DNA Amount

K  CentiMorgans

L   Segments

M   Tree ? [blank if the match has a tree]

N   People in Tree

O   Found ? [Ancestry notation]

P   Groups [blank for now]

Q   Note [any note you have created for the match]

By noting the first Row in the spreadsheet with data (i.e., your highest match] is row 3, and the last Row in your spreadsheet (i.e. the last row with match data in columns G through Q), which is row N (417 for my "Common Ancestors"), you can figure out how many Common Ancestors you have (equation is N - 3 + 1) .  I have 415 right now.  

There is a limitation on how many DNA matches you can put into the spreadsheet.  At present it is 1400 AncestryDNA matches.  The "Instruction" page tells you how to add more match data after #1400 in your AncestryDNA match list.

Greg recommends copying Columns G through Q to your own spreadsheet on your computer, and naming it as you choose, in order to save it.

There is another page with "What's New" with an Updates log.  Be sure to check that every time you download a new copy of this Google Sheet file.

Thank you to Greg Clarke for permitting use of this Google Sheet, and for Devon Lee for providing the YouTube video of the process.  It works well for me, and I appreciate it.  

I have avoided making a list of my AncestryDNA matches because there are so many, so this really helps.  I will probably make lists of all of my" Close Relatives" matches and all of my "Starred" matches occasionally just to have them updated. 

                                      =============================================

The URL for this post is:  https://www.geneamusings.com/2021/03/downloading-my-ancestrydna-match-list.html

Copyright (c) 2021, 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 randy.seaver@gmail.com