How to Download Your AncestryDNA Match List | Export Ancestry DNA Matches

[@uptergroveGreg]

I have just updated the Ancestry spreadsheet - it now recognizes Both sides / Maternal / Paternal / Parent 1 / Parent 2 - whatever Ancestry or the user has pegged a match (if one has been pegged). You will have to re-download the spreadsheet - but - you've probably got some new matches in there by now anyways - right ? Enjoy!

[@roberthopgood1894]

as of 2024-07 it does NOT paste the same i get totally different & it is garbage

[@DrKazAstro]

@@roberthopgood1894 yes me too. It's not working for me in Firefox - I don't think it is supposed to - BUT works great in Chrome. Chrome gets all of the data and then some - the names of people don't go into column A properly. The rest pastes correctly

[@YetAnotherLoser]

thanks for all of your work to try to keep up with the ever changing ancestry shuffle. programmatic data extraction tools really can't win, because all it takes is an inexpensive reformatting to invalidate your tool. these changes bring no benefit to ancestry customers, but force you to rewrite your data extraction tool over and over, leading to programmer fatigue. you've been a saint, but it takes more than sainthood to overcome a constant and deliberate policy of rolling obfuscation. i have to re-program my cluster spreadsheet virtually every time i want to access my match data.

[@YetAnotherLoser]

just in case you're interested, here's my autohotkey v2 script that helps make things a little easier. although, as i say, it'll probably be invalid in a week because things change. also, it has one non-portable feature: mouse positions are hard-coded for my needs, with display settings of screen size at 1920x1080 and text size at 150%. that may be a show stopper for other people. i'm not trying to provide a generic tool; i'm just showing how i've approached this problem. (in next message)

[@janekirkwood5077]

@@roberthopgood1894 Are you using chrome, I am and it is just pasting all mine into A6- fed-up of trying to redo it, just does not work and there is no one to ask for advice

[@lj_wolfe]

Confirming that the sheet doesn't play well with Firefox, and I think I can see why -- when you Select All on FF, it doesn't select the "xth - xth cousin" and "x cM | x% shared DNA" parts of the page. Just a bit of important data there! On Chrome, Select All actually selects _all._ :-)

[@garrywright9291]

@UptegroveGreg: Can you tell me where to find the latest version of the Ancestry DNA Match Maker spreadsheet and answer a question? I have watched the how to video several times to try to copy my Ancestry DNA matches into the spreadsheet. I always end up with all the matching people, their data and even profile photo, copied into Column A from Cell A6 down for the entire list of matches. The data never spreads to the right across the Cells from Column T(Category) and continuing to the right. What am I doing wrong? Garry Wright

[@ruththompson7131]

Garry, I was having same issue. Didn’t have a problem using it before. Finally tried using Google Sheets, which I only ever use if I have. That worked, copied my final results into Excel for my future use. I think Excel was removing some or all of the macro.

[@garrywright9291]

@@ruththompson7131 Thanks Ruth: I will download a copy pf Google Sheets and give it try.

[@mickyc77]

I had the same issue last week when I tried to save it to Excel@@ruththompson7131

[@brianschuck4832]

I used a process like this many times, but really appreciate that this shared file fixes the formatting! Used to take me a long time to get that all straightened out.

[@YourDNAGuide]

So glad to have saved you some time!

[@carolweldon1414]

It isn't pasting into the format in the spread sheet but pasting everything into column A. I'm using Chrome per previous comments as I usually use Firefox. I'm making sure I'm in the 'yellow box'. I've even re-copied and saved a new master.

[@uptergroveGreg]

HI there Carol. When you PASTE into the google spreadsheet, it will go into column A - that's what is supposed to happen. What you should see is all the text and gobbledy-gook from the Ancestry DNA Matches page will be in column A and flow all the way down the page. If it is ONLY in the one yellow cell, then you must have double-clicked on that cell. You need to only single-click so that the first piece of text goes there, and the rest of the text flows down in the cells below it. .... all down the page - almost without end (it will be very long!!) HOWEVER ... once that text is in place, the table should build to the right of it. You mention you're using Google Chrome - that's good - you need to use Google Chrome for both the Ancestry DNA Matches page AND for the Google Spreadsheet to copy the data into. Also - use the keyboard shortcuts to SELECT ALL (CTRL + A or CMD + A) and then COPY (CTRL + C or CMD + C) - sometimes just using the mouse can miss text that the formulas need to do their magic.

[@chriscampbell3695]

Having the same problem. It only pastes the 1st 2 matches across into the spreadsheet but they are all there in column A. So frustrating but too tired now, I'll have another go tomorrow.

[@uptergroveGreg]

Hi folks! Thanks to a fellow user, Tammy, we discovered that it turns out - when you turn on DARK MODE on your tree in Ancestry (now), somehow that also affects what you see on your DNA Matches page - AND - there is extra text added in Dark Mode that is not there in Light Mode - and that trips up the formulas. Also - to add more confusion - if you SORT your matches, that also can add extra text that messes up the formulas. SO ... do NOT use Dark Mode - and do NOT sort your matches before you do the copy & paste to the spreadsheet. If you've suddenly been having problems since the introduction of the Dark Mode feature - see if turning it off fixes the problem.

[@simondodge9733]

Where is the Dark Mode setting ?

[@chericoussan9608]

@@simondodge9733 Profile > settings > trees > at the bottom you will see it is defaulted to Background = Dark, change to = Light

[@chericoussan9608]

Dark Mode Off. Due to number of matches to download, I have it filtered by cM range, and it's not working. No 'sort' functionality in use. Chrome browser. Any other thought to troubleshoot?

[@s_c_r_e_w_b_a_l_l]

Hello and thank you for sharing your expertise with spreadsheets and Ancestry. I'd commented above something may have changed. Is there perhaps a simpler way to accomplish this?

[@dannywade4010]

Hi Is there an easy way to extract in one shot all the DNA match names (and not page by page)? I wish to do it to compare my names wiith an other user and see the names we do have in commons

[@thislittlemissis]

I have tried this in Firefox and Chrome and I am seeing absolutely no matches in the spreadsheet. 🤷🏻‍♀️

[@YourDNAGuide]

The spreadsheet was created by Greg Clarke, he should be able to help you. You can reach him here, [e-mail]

[@MidlandTexan]

Thank you! I have been frustrated regarding this issue. You have helped me out!

[@YourDNAGuide]

Great!!

[@SamsungGalaxy-vw9gy]

Where is the link to the spreadsheet? You said there is a link "below"? I don't see the link. Please provide the address to the link in a clear way

[@YourDNAGuide]

It is in the video's description. Here is the link. tinyurl.com/AncestryDNAmatchlist

[@SamsungGalaxy-vw9gy]

@@YourDNAGuide got it. Thanks. Your lecture is clear and entertaining

[@janekirkwood5077]

Hi I've tried all these steps and the text is just lumping all the matches info into the A6 column and not moving it across and I've tried several times from the start, someone please help!

[@beeurd]

To anybody else stuggling in Firefox like I was - switch to Chrome to copy and paste the information. I don't think the macro that parses the data likes Firefox.

[@YourDNAGuide]

Thanks for the tip!

[@YetAnotherLoser]

agreed. i don't understand why copy all on firefox does not copy all...

[@pinwheelgrl9304]

Thank you for the more up to date version!

[@julieburnett8283]

Can you tell me where I download the spreadsheet

[@YourDNAGuide]

I am not sure what you are asking. This video explains how to get the spreadsheet.

[@LifeStirredUp]

Thank you so much! This is just what I was searching for. Subscribed!

[@YourDNAGuide]

Thank you! So glad to hear we have been helpful.

[@richardwalker6061]

How can I use this Downloaded “Your AncestryDNA Match List” to benefit me?

[@YourDNAGuide]

Downloading your DNA match list offers two key advantages. Firstly, it allows you to analyze your matches for signs of close family relationships (endogamy) in your family history. Secondly, it provides a way to consolidate your DNA matches from various testing services into a single, organized list.

[@richardwalker6061]

@@YourDNAGuide thank you

[@YetAnotherLoser]

a critical benefit is, you can use the data to create a cluster map. if you wonder what a cluster map is, you can see one using your own data by downloading your dna data from ancestry and uploading it to myheritage. give it a few days to generate matches, then use their menus to select cluster map. it's very powerful because it clusters closely related matches together in a group. so, if you know you match john doe, but john doe doesn't have any tree, but you want to try to build one (in your tree) for him, you know who his closest relatives are within your match list. this approach has led to a few breakthroughs for us.

[@YetAnotherLoser]

ps i have no relationship with (other than as a user) and gain no benefit from other users going to myheritage. i just get a lot more benefit from their tools, such as cluster map and also dna segment data which allows you to assign a most recent common ancester to particular snippets of dna, then, when you get another match on that snippet, you know _how_ you match, so you know which part of your tree they must fall under. gosh i really wish ancestry would provide matching segment data. it's a killer app, and combined with ancestry's database, it would be just amazing.

[@robdav3655]

Unfortunately, I get an error when attempting this process. Something about a bad address.

[@sonyalazanya7]

this is brilliant!!!!!!!!! thank you so much. tremendous time saver.

[@joanduvall1200]

Anything for Google Sheets?

[@brogdontsypko]

Is it possible to download the list of trees as well? Or would I have to do that one by one after clicking on each one?

[@YourDNAGuide]

Nope, can't download other people's trees.

[@jerrisamuel1788]

I am having a problem with the pasting because all my notes are off centering everything. Is there a secret to correcting that?

[@chericoussan9608]

Happening to me as well, and I do not think I'm in dark mode. :(

[@clytiealaimo2790]

This saves so much time! Thank you!!!

[@darter475]

i have tried this and it dosnt work i get had to be repaired and some features had to be Removed Records: Formula from /xl/worksheets/sheet2.xml part Removed Records: Formula from /xl/worksheets/sheet3.xml part

[@jrcgjhughes]

How do you add more rows? I tried to do this but noting happens.

[@uptergroveGreg]

Hi there JRCGJ Hughes! (One of those initials must be right). Adding more rows can be a bit tricky - that's why the latest version of the spreadsheet has 4000 to start with .. I thought it unlikely most people would have more than that many DNA matches. BUT ... if you're taking the endogamy course - well - of COURSE you do! (and maybe even if you aren't). SO ... Step 0 - before you do anything else - make sure you have the most recent version of the spreadsheet. (It's the same link - I keep it updated - last update was within the last month - a couple tweaks suggested by one of YourDNAguide course members). (ALSO - make sure you're using Google Chrome - that seems to have solved lots of problems others have been having ) IF the latest version still isn't big enough for you, then the instructions on how to add more rows are (briefly) described at the very bottom of the sheet. Scroll down to row 4007 - or thereabouts. You should see a coloured row with these instructions in it: 1. Add more rows 2. Copy the last row that HAS formulas in columns T through to AE 3. Paste the formulas DOWNWARDS to the bottom of the spreadsheet Those three instructions are basically the steps you need to follow. Here are some additional suggestions to accompany each of those steps: 1. I'd recommend you DO the SELECT / COPY / PASTE into the spreadsheet first - THEN - see if you actually do need to add any more rows. I suspect either (a) you actually have enough rows already - or (b) after doing the paste - the spreadsheet automatically added a whole pile of rows FOR you ... so you can just skip ahead to step 2 BUT ... if you are shy of rows - then - yes - add as many new rows to the BOTTOM of the spreadsheet as you need. 2. If you did the SELECT / COPY / PASTE and the spreadsheet auto-inserted a bunch of new rows at the bottom - then you may end up with a pile of yellow coloured rows that do NOT have the proper formulas in them. Regardless of what colour the extra rows are that you added, or were auto-added for you, you're going to want to go to the LAST ROW that HAD the proper formulas in them. If you're working from your own copy of the latest spreadsheet, that should be around row 4006 - the one RIGHT ABOVE the coloured row with the 3 instructions. SELECT that entire row of formulas - starting in column T all the way to the right to column AE. (Pro Tip: Click on the cell at the bottom of column T with the formula in it - you'll see the formula up in the formula bar, it start with IF (ISERR .... blah blah blah ...) --> THEN ... hold the SHIFT key down with one finger - and while still holding that down, press the RIGHT ARROW key ... and you will be highlighting the whole row across (I find that much easier and more reliable than having to fiddle with my mouse!) --> once that row from T to AE is highlighted, then do CTRL + C or COMMAND + C to COPY those formulas 3. Paste the formulas downwards by selecting all of the cells below (whether they are coloured or not - don't let that bother/distract you) - then choosing CTRL + V or CMD + V (Pro Tip # 2: IF you have the row of formulas still highlighted - then do the same sort of thing to highlight the cells below * Hold the SHIFT key down with one finger - and while holding that down - press the DOWN ARROW key, and you will be highlighting whole rows at a time. SUPER Pro Tip: You can JUMP to the very bottom if you hold the SHIFT key down and then press CTRL + DOWN ARROW or CMD + DOWN ARROW !!!) * NOW ... once you've got the rows all highlighted - NOW you can click CTRL + V or CMD + V to paste the formulas in all of those rows below. Hope that play by play description helps you out !

[@earlenenakachi7882]

Thank you for sharing!

[@minikitz5642]

Please can someone help? I have tried this numerous times on my own copy and no matter what I ONLY get 3 columns populated: "Tree?", "# in Tree", and ""found? (common ancestor). NOTHING else populates in the sheet. No names, no cMs, etc. Everything in A6 is lumped in there in a huge font and many colors of backgrounds. How can I get the names, etc. to populate in the sheet? I'm using the most current version of Firefox on a PC to grab only about 218 matches from Ancestry with Ctl-C/Ctl-V. Thanks for any help! -- Debbie J

[@uptergroveGreg]

Hi there Debbie! Sorry you're having troubles and getting frustrated. At first, when I read your comment, I was wondering if all the text you copied was going into one cell, A6, and filling up that one cell to overflowing. Is that the case ? What the formulas need to work is for you to single click on A6, then do the EDIT PASTE (with menus or the CTRL V key combo like you're using), and then the text should start in A6 and flow all the way down the column to the bottom of the page (beyond what you can see, but if you scroll down, you'll see lots more below). If that isn't the problem, the next suggestion I have is to try and use a different browser. Specifically, if you can, use Google Chrome, since that browser works best with Google Spreadsheets (not surprisingly - both being Google products). I've been able to use the formulas on my Mac when testing in all my browsers, but have reports from other users that indicate that some just don't work consistently. Try Google Chrome and see if that works for you.

[@minikitz5642]

@@uptergroveGreg Hi Greg, Thanks for the response. It is filling A6 to row 1364 and there are blank rows in the sheet after that. I only grabbed 210 matches last time, thinking I may have hit some upper limit in prior attempts. I will try it in Chrome. That's probably the issue. Thanks for this great tool! -- Debbie

[@minikitz5642]

@@uptergroveGreg Thanks! Using Chrome worked like a champ! 🙂

[@skdrafts]

Same happened to me. Did you get an answer?

[@uptergroveGreg]

Hi @@skdrafts - I think most people who had this issue fixed it by switching to using the Google Chrome browser. If you use Safari, FireFox or Internet Explorer (or Edge) - sometimes those browsers don't capture all of the text, which is needed for the formulas to parse and fill out the columns properly. Try that and see if that fixes things for you.

[@maxkennedy1314]

Having problems since the latest Ancestry update. The Category, Name and managed by do not populate into the sheet. The data starts with the Relationship. Any ideas?

[@uptergroveGreg]

HI there Max ... I was wondering when the latest Ancestry re-decorating was going to hit the DNA matches page. It hasn't hit MY version of it here (in Canada) yet - one of those features that gets rolled out slowly I suspect. I will keep checking, and once I can see what it does to my data, I will revise the formulas in the spreadsheet.

[@maxkennedy1314]

@@uptergroveGreg Thank you

[@s_c_r_e_w_b_a_l_l]

I'm unsure if Ancestry had recently changed their format or not but this isn't quite working as described. Video was great but you lost me at the end

[@litigious]

OK, but there is still no option to download Shared Matches anymore. Seems it would have to be done manually which may take a lifetime.

[@YourDNAGuide]

Paul, there isn't an option on the website to download the Shared Matches, you would just have to follow this same procedure with the shared matches list that we outline here in the video for your main match list.

[@sonyalazanya7]

Is there a way to keep the hyperlink to your match?

[@uptergroveGreg]

HI there Sonya, When you do the COPY & PASTE to the Google Spreadsheet - that initial table SHOULD have the hyperlinks still embedded to your matches. (Remember - you have to use it in Google Spreadsheets - you can't make a copy of it in Excel - the formulas are very specific and don't translate to Excel). BUT ... when the spreadsheet does its "magic" and organizes your matches in a table ... I suspect you lose the hyperlinks when you then copy THAT table into somewhere else. (AM I interpreting your question correctly?) IF SO ... there is a solution, but it's a bit geeky. I'll give you the steps here, but perhaps a quick video would be helpful too. (1) For this to work, it's easiest if you copy the table into a new tab IN THE SAME Google Spreadsheet that did the original magic (not Excel, not a different Google spreadsheet). (2) SO ... in the new tab that has the copy of all that data - including the list of names WITHOUT the hyperlinks - add a NEW column, and label it NAMES WITH LINKS. (You don't have to use all caps - I'm just doing it here for effect) (3) In the first cell below the label - same row as the first match - click in that cell and type an EQUALS SIGN ( = ) (4) THEN immediately switch to the Raw Data tab and click on the Name (with the hyperlink) in the row of the first match - and then hit the ENTER key. (5) What you SHOULD see if you followed those steps exactly was that the first match name would appear in your new tab after a second or two WITH a hyperlink. (What you've done is basically created a formula to copy what was on the previous page in this location) (6) Final step - COPY that formula all the way down the column so that ALL the names with hyperlinks appear. (You can use COPY & PASTE, or just drag the little square in the bottom right of the cell) I hope that helps !

[@sonyalazanya7]

@@uptergroveGreg Thanks for taking the time to leave such great instructions. So this appears to work as long as I keep it in the same spreadsheet. I now have all the data on a clean tab where I run less of a risk of accidentally clicking the 'clear previous data' which was one of my main concerns.

[@shelley-d9840]

Not seeing the link to the spread sheet.

[@YourDNAGuide]

If you click on the "show more" button above, in the description, you will see the link.

[@shelley-d9840]

@@YourDNAGuide Thank you so much!

[@deesheets9926]

Too confusing. Can't make out what it says on the screen

[@YourDNAGuide]

I say don't worry about it, Dee. This is not an essential thing you need to do to make progress in your family history.

[@reneeluciani5042]

Thanks so much :)

[@KennethFunk]

Doesn't work anymore!

[@YourDNAGuide]

Thanks for the heads up! We will look into it.

[@janekirkwood5077]

@@YourDNAGuide Hi Can you advise as all data is just copying into A6

[@richardcrosbie7838]

Please turn off the music background.

[@YourDNAGuide]

Thanks for the feedback, we will keep this in mind as we create new content.

[@marshamartin825]

Terrible, doesn’t explain what she is doing

[@AncestorSalvage]

Are you sure?