_TOP_ Download Ancestrydna Matches To Excel

If you have tested on Ancestry DNA and have lots of matches, you might want a way to organize all that information into a nice table or spreadsheet. Ancestry does not have a way of downloading that information for you.

After clearing the data again, enabling the Google Docs Offline extension and reloading the spreadsheet, I tried a Ctrl+Shift+V for only 35 matches. And yep, I got 25 entries showing data in columns T through V, and no data there for the last 10. Data in column M stopped at M27, which is where the T through V data stopped.

Download Ancestrydna Matches To Excel

Download 🔥 https://shurll.com/2y2R0w 🔥

Decided to try a much larger chunk of data, taking it down to all my matches showing greater than 40cM, 392 total entries. And the last 10 behaved the same way: data on 382 of them in T through V, but the data in column M stopped at M384 and the final 10 entries didn't show Category, Name, or Managed by.

I'm not going to try to monkey with the spreadsheet myself because the odds of me messing it up are at least .99. Like I said, it's a minor thing. All we have to do is include some matches beyond where we might normally stop and we're good. Just thought you'd want a report about it if it hadn't come up.

But I see this Google Sheet as a real time saver for, especially, anyone who needs to work with data from others, like our Adoption Angels do. Getting cell phone photos of a laptop display don't in no way cut it. But talking someone through copying their match list and pasting it in an email is doable. More, getting them to filter by shared matches against a target individual...and then having a quick and easy way to store and organize it all is a lot better than the manual Excel stuff I've been doing.

Most of the other companies have ways of downloading your matches already built-in somewhere. It may be hidden in an advanced menu feature, but it's there. The only other one that does not have this feature (yet anyways) is LivingDNA.com, and I have made a spreadsheet for that.

Welp. I tried this in MS Edge. On the Raw Data Tab, it pasted all the data into Column A only. About 9,000 lines of it. (I have a lot of matches.) When I tried it in Firefox, I couldn't get anywhere because Google Sheets won't allow COPY or PASTE in Firefox, or CTRL+C or CTRL+V. (This "My way or the highway" nonsense is one of the reasons I don't use Chrome.) Thanks anyway.

I figured y'all might find this interesting. I've been frustrated by the lack of features AncestryDNA provides, despite it being the DNA company with the highest quality matches (i.e. matches who know their family tree and upload it). One thing in particular I've wanted to do is create a network map of DNA matches from a test my mom had taken to help figure out some of her colonial American ancestry. I've found the tools ancestry provides, creating your own groups manually and adding matches to them, are too inaccurate and slow. I've seen these before, for example here, but most of the examples I've found either cost money, are old and thus depend on an outdated version of Ancestry's UI, or both. The idea is that every node is a match and every edge between the nodes signifies that those two matches are shared and also match each other.

To solve this over the last couple weeks I whipped up a program that saves PDFs of the pages of shared Ancestry matches for each of my mom's matches, which is tons of PDFs! I could then parse through the text of these PDFs to tell which matches were shared. This is the final result! I don't have any identifying information on there, only IDs which I've assigned to each of the matches, and colors for matches which I know come from a certain branch. If there's interest I could toss the code I made up on github, although it will need to be changed depending on things like screen resolution and preferred browser/OS.

Once I have the two files, I use the VLOOKUP tool in Excel to associate (Cousin) Range and SharedCM to the primary match, and then to the In Common With matches. The result is a combined file like that below. The combined columns are highlighted in green.

This is the first post of a series that will demonstrate, step-by-step, how to use a free Excel-based network analysis tool to explore and get more from your Ancestry DNA matches.

You can find an index to this series here.

A cousin and I want to compare all our matches (we each have 40,000+) to identify those we have in common beyond the shared matches Ancestry displays, ending at 21cMs. By doing manual searches we have identified some additional shared matches down to 7cMs.

We believe we share many more matches but a manual search through thousands of entries is prohibitive. It was the discovery of a few distant shared matches that led us to identify one more generation back of our common line. When we find additional shared matches we will be fairly confident they are related only one set of common ancestors and could get us further back in time to the country of origin. The process would be like using a sorting hat, to borrow from Harry Potter.

One cousin and I are trying to identify siblings who traveled to North America with our direct ancestors. We have discovered three of them using low level DNA matches and a lot of work tracing their trees. We believe there are at least four more ?x great aunts/uncles we might be able to identify/confirm if we could find more shared DNA matches.

We have already assigned our matches into groups as you do, but without more matches we can clearly identify as associated with our distant common ancestors, the rest are just in one big melting pot we can not assign to a group. Undoubtedly, technology will eventually develop more sophisticated tools to help us do this.

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.

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.

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.

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.).

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.

However, and this is the bad news, 82 previous leaf hint matches are now gone. Some disappeared in the adjustment done back in May 2016, but not all disappearances can be attributed to that house-cleaning. I noted the matches that disappeared at that time.

Great article Roberta,

Do I understand you correctly that you have gone one by one through your 367 shaky leaf matches and copied each bit of info to your spreadsheet? Are there any shortcuts to the process that you have found helpful?

The only time that Ancestry DNA deletes a match is when a new version of software is made available, where the algorithm and/or criteria for a match has been updated, e.g. the threshold for an acceptable match has been increased. In this case, Ancestry DNA creates a spreadsheet with the DNA matches that are being deleted, where the matches meet a certain criteria. As I remember, the criteria includes any SHA, any starred match, or any match with notes.

My count is: 68 Shared Ancestor Hints, 662 Starred Matches, and 93 4th cousins.

If one is only looking at DNA matches with a leaf hint, MANY are being missed because their computer software is not accurate in picking up matches unless the spelling and naming of the two matches is close to exact. Their computer usually will not give you a leaf match if you spell the name Philip and your match spells Phillip, for example. I have looked at all of my 20,000 trees because I am desperate to break down 4 brick walls. I then move the trees to the back of the file and keep the ones with a shared ancestor that goes back no further than 1675, and make the notation. So, I have definitely more than 500 tree matches I have personally found, and not their software.

But our DNA matches at Ancestry are just trees matches, and not dna matches, until we have done Triangulation at Gedmatch, and a one-to-one to confirm each person matches each other person in the TG. The one-to-one separates those who match on the patra vs matra side. Since, a one-to-one can only be done at Gedmatch, it is on my go-to list. Last week I messaged 3 of my matches and asked if they would upload to Gedmatch. One did so, and the other two ignored me. ff782bc1db