Hello again ... you guessed it ... Ancestry DNA matches page changed its format, and I have now been able to adjust the Spreadsheet to use the new format. This time, I've created TWO versions of the worksheet, one that works with the SHIFT + CTRL + V when you paste just the values, and the other worksheet that works with the regular CTRL + V which will paste the full comments, including the Live Links back to each DNA Match - and - this worksheet preserves those live links too! SO, you can use whichever version you prefer. A couple notes: * Both worksheets come with 4000 rows with the formulas filled in. There are more formulas in these new versions, so, the sheet takes a bit longer to process the data and go through each of the formulas after you do the copy & paste. Be patient. Of course this effect is magnified on older machines. * If you go BEYOND 4000 rows (yikes), you will have to manually copy the formulas from row 4000 (or so) downwards to the end of the sheet so that it continues to work properly. * The formulas are setup assuming that the DNA Match page is in its "natural state", sorted by RELATIONSHIP. If you sort it by DATE, the Category formula doesn't work right, and you won't get the results you want for names etc.. * I was able to properly separate Category / Name / Managed by - I think - finally, and, MOST of the Notes section will behave themselves now! * I've left a row for Groups - but - I can't grab that info for you (since it's not in text) , but if you want a place to manually add it yourself - it's there for you. Until the next time Ancestry decides to help us out with a new feature and format ... Enjoy! - Greg :-)

I have been looking for a good way to do this! And behold the good people at Family History Fanatics has done it!

Excellent! Needed this for a while will save so much time! Many Thanks

Brilliant tool. I use formulas a lot but those are amazing. Well done.

Great update to the Spreadsheet. I love putting all my info into Spreadsheets but am new to Google Sheets, and don't know many functions other than basic Excel ones. Both of Greg's versions are working fine for me, but quick question: Can you sort the Column "People in Tree" numerically? I can't, because it has mixed numbers and words, but there might be a trick out there. Other observations: On the original version, I could only display approx. 550 shared matches. I thought therefore to break up my searches, by Surname of Interest, or Place of Interest, using the Duplicate function on the lower Tabs. It limits me to 5,000,000 cells (I think), so i can only create 3 tabs. I have therefore created MANY new Files, with 3 tabs each, to sort my data... Loving the work you're doing in this Group!

This is fabulous! Thank you! Thank you!

Thanks for the information! I’ll give it a try!

omg, this is AWESOME!! Thank you!

Great! Can’t wait to try it

First off Greg Clarke, THANK YOU!!!! Now that I have the spreadsheet I'm excited, but that excitement dissipated when I was scrolling down and copying all of my matches. As I got closer to the 25 cM pages it began to go slower and slower I was in about 2 hours at this point. Next thing, 30 minutes later my page started crashing and then crashed! I lost all of my copy/highlights. Is there a way to copy a few hundred at a time paste it on the spreadsheet and go back for more and keep adding a few at a time or must they be all copied at the same time? Second question, what are y'all doing with the info once you have it in the spreadsheet? How are you grouping them to know you are related and how? In ancestry, then going back and finding those matches? I'm not sure how to separate, group, or color-code them on the sheet. I don't even know which are my mom's side and my father's side without constantly going back to my ancestry. That would be very time-consuming. Please let me know if you have a video on this. Thank you again this is awesome!

Fantastic! I've been manually doing this using a few filters, but this is great, however, i have notes on a lot of my matches and this seems to mess up the sheet a little, happy to explain further

Thank you, thank you, thank you!

So excited that this is coming. Worked on this with my hubby for 3 hours now and can't seem to get it to work. Found this comments list and it seems I am having the same hiccup with a different view. I'm in Canada. Tried Chrome, Edge and Excel and all browsers sort the Ancestry data so the grouping dots are at the end of the rows of matches. Also using Windows 10 if that matters. Will be waiting with excitement to see the 'tweaked' spreadsheet. :-) Thank you very much for your hard work!

UPDATE: I have tested a new version of the Google Spreadsheet which I believe will now work with either version of the Ancestry DNA Match list (the format I see here in my corner of the internet in Canada, and the version some of the rest of you are seeing in other parts of the world). Thanks to those who helped test out the new formulas. The new version has TWO worksheets - one if you have a VIEW MATCH button at the end of each row, and one worksheet if you do NOT have that button. I will make a post here as soon as I've updated the Google Spreadsheet. (IT IS NOT UPDATED YET - please be patient - I need to coordinate with Devon.) Thank you for your patience ... the fix is coming ... I'm keen to share it with you all ... thanks for the very positive responses so far from those that have used it successfully, and those testers who have tried the new version.

The UPDATED spreadsheet should work now. Do you still have the link? If not, sign-up again at this website www.familyhistoryfanatics.com/post/download-ancestrydna-matches-hack The problem was in the two versions of Ancestry's DNA match list. Keep sending in the tweaks!!! We'll make this work awesome.

thank you!!

It wouldn't work in Safari on my Mac. So having avoided using Chrome for years (I don't like change) - I bit the bullet and moved to Chrome........I can't get it to do a CMD A select but that's trivial - I'm happy to scroll down. BUT..... OH MY GOODNESS - THANK YOU!!!! This has absolutely driven me nuts for years. Hubby and I have tried all sorts - and the number of hours I've spent copy, paste, delete all the gunk. Urrgh!!! I could kiss you! Fab.

Thank you Greg, this is great. For reasons I'm sure that you understand, what I'd really like to be able to do is download all of the "Shared Matches" data for each of my own matches, and get all of that information into a spreadsheet. Any thoughts?

The macro to clear data from the worksheet is under "Extensions" in my Google Docs online app, not Under Tools. While I can cut and paste the results, whether as is or just values, the formulas are not populating columns G through P I think because as other person mentioned my list of matches is not laid out in the same way yours is. I can share a snapshot if you would like; basically, I have 3 lines of information for each match, where your view only shows 2 lines.

I just updated the Google Spreadsheet to include a column that will display Which Side a match is on, if you have taken the time to identify that using the "Do you recognize this match?" buttons. The column will either be blank, or have Both, Mother's, or, Father's in it - so - yet another way you can sort your results if you wish.

When I do the control a it does not select all the text. The most important data I want in my Excel file is the centigrams.. did ancestry change the display so you can't copy the text on the screen?

Hi. I'm a new member today and have enjoyed watching a few of your really educational videos. Thanks. Regarding Greg's excellent spreadsheet for Ancestry DNA matches, Devon mentioned at the end of the video something about updating the spreadsheet with new matches from Ancestry but I couldn't understand how (a) to harvest the new matches since the last time you looked at Ancestry; and (b) how to add in the new matches to the spreadsheet without affecting the data already in there. Is there a set of instructions somewhere for this? Thanks.

Hi all! I've heard from a number of you today - and there is a problem with using this spreadsheet if your Ancestry DNA Match list doesn't look like mine. Mine has a big VIEW MATCH button at the end of each row. If yours does NOT have that button, but instead the coloured dots at the end of each row, then the formulas won't be able to perform their magic. I'm working on a fix for this, and will post a comment here as soon as that is ready. Sorry for this - I didn't realize that they were changing the format of their matches page - and that the change was not consistently applied. My bad. - Greg :-)

It stops filling in the data in columns T to the end showing 1. Add more family, U has Copy the last row that has formlas in columns T through AE, and so on. The data is in Column A but won't process

Just to say that after many tries that didn't paste anything into my copy of the spreadsheet, something caused Google to tell me that I needed to I install Google Docs Offline as a Chrome extension. Then it worked! I hope this might be useful to someone.

UPDATE!!!! The Google Spreadsheet has now been updated to handle the latest version of the AncestyDNA matches format released in June 2024. If your AncestryDNA matches page now shows "square dots" with a single character in them, and you can only see 20 or 50 matches at a time, then use the updated version (see link above), and use the 2024 Raw Data tab. On the Instructions page of the spreadsheet is also a link to a KZfaq help video I made about the update - including - how to now capture the Custom Dot Groups!

It's broken. I followed the directions exactly and it doesn't copy to correct fields and copies some of the names twice. I even tried all 4 of the different formats and none of them copy correctly. I have the "do you recognize them?" type match list.

This is super frustrating, I am following the directions and it is only bringing over 143 of my matches, not all of them. Help?

A gin & tonic are looking pretty good about now. Firstly thank you for all the work you have put in for us all. I have managed to finally paste from DNA list, though all the data goes in column A with the Demo info still in all the other columns. I tried removing the Demo info. No difference. Noticed that my Relationships & DNA % & Cm don't highlight to copy. My DNA has View Match & coloured dots. What am I Not doing correctly?

I love this spreadsheet! Does anyone know of a way to scrape data from Thrulines? I have two brick wall Lees that I suspect are siblings from a particular NC Quaker Lee family and I want to compare the Thrulines for these two, their spouses, and the suspected grandparents. I know Thrulines is based on individual trees, but I think I may find some good info. I've heard from others that they just copy and paste into a spreadsheet on their PCs, but that doesn't work with a Mac. I'd type them in, but that would involve some 250 names. Ideas?

Hello. Used this before, but I am stuck, I pasted my data into your google sheets copy. Everything is in the first column. How do I get it to populate the other columns?? I am hoping to extract names only. Please help.

CAn someone post the example spreadsheet for me please. I'm being diverted to pages with numerous samples and I can't work out which one it is. Thanks

My List on Ancestry does not look the same as yours and does not work...can you fix the formula to work with the older style list please

Hello, there! I have received the shared Google Sheet, but it seems as though it has been modified by someone who has not copied the sheet, therefore has directly modified it. Might it be possible for you to share a clean Google Sheet file? A suggestion would be if you replace the word EDIT with the word COPY in the link you share: it forces the receiver to make a copy of the file; in that way, there is no inadvertent modification. I have been unable to get the list populated. I'm not sure if it is directly related to the inadvertent edit by others or because my Ancestry visual is not the same as it is in the video. I look forward to being able to use the Google Sheet! Thank you for creating this!

I'm not able to populate the "NOTES" column but I can see that those notes are being copied. Trying with Safari and Firefox. I don't use Chrome. Trying with each version of your template. (My Ancestry has the 'Do you Recognize" feature.) Neither puts anything into the 'Notes' column which is the most vital info I want in the spreadsheet. I'm 73-years young and new to creating spreadsheets so all gentle suggestions are welcome. Thx.

The spredsheet update doesn't work for me. :( My version has "do you recognize them" at the end of each match.

Hi again! Just did another update to the latest worksheet : NEW View Match btn - to fix a problem with the Notes column. It may not be perfect still, but it seems to catch more of the Notes and place them correctly (instead of placing them in the first column of the next line).

What do you do with it once you have it? I'm not a spreadsheet user and I'm not clear what the purpose is???

Does this spreadsheet work on a ChromeBook? Data not coming across and I'm beginning to wonder if this is the problem. Thanks for any response

I suppose this would also work with Open Office? I use Ubuntu on my laptop

I've tried over and over in Google Sheets and Excel and all I get is a single column of match information. The rest of the columns don't populate.

I can not get it to paste without format. (I'm familiar on copy and pasting) I'm using firefox. Paste with formatting works but as we know it makes it "messy". Thanks in advance for any helps on what to do.

Will this work with Libre Office and Open Office?

Ancestry says "failed to copy" after selecting all, then copy. Did ancestry break this work-around?

I can do everything except ‘Paste’. I’m using an iPad 11.5, current iPadOS, and have tried with and without using the Keyboard that Apple made for the iPad.

I am not sure why the yellow isn’t highlighting, I saved as, etc.

Tried with no results. Doesn't populate the sheet. My display is not the same. Tried in Chrome, Firefox and offline. Google sheet added Raw Data in row above the other headers. A5 becomes A6 but pasting in never works.

Have had a terrible time. Nothing works. None of the yellow fields I dumped the copied data into worked.

This very nice but unfortunately, names are first then last so can't sort by last name unless there is a trick I don't know.

This is cool, but when pasting matches with Ctrl+V and Shift+Ctrl+V,: I noticed: -the former had embedded links (names, trees, etc), the latter did not. Why is that? -neither has Groups (Col P). Why not? -the former does not have Notes, at least not in Col Q ... I noticed some notes in Col G There are other issues, but probably too much to note in this comment.

Tried it, it doesn't work. I also do not see any benefit of putting it into a spreadsheet. It's a lot of fiddly work for little benefit. Unfortunately, too many youtubers are putting out "tips and tricks" videos, "hints and clues," and other how-tos that eat up a lot time and do nothing to advance actual research.

Perfect contact although not working for me as I'm on old view . Just joined based on the great content thats relevant to me

If you have DNA from Ancestry, then you'll want to watch this video: kzfaq.info/get/bejne/ldh1aaiJtrbQqoU.html Be sure to become an FHF Xtra channel member to access additional training with spreadsheets including: - Creating a Leeds Method Charts using GoogleSheets and MyHeritage kzfaq.info/get/bejne/e8uVd9mSl92lh40.html - Genealogy Spreadsheets Tips and Tricks kzfaq.info/get/bejne/lbR3l859ud2-j30.html

This doesn’t work

Good day early birds

You waste so much time on how a to c/p.