Do you feel a little overwhelmed when looking at your Ancestry DNA matches? I find that a great way to tackle thousands of matches is to download them into a spreadsheet.
This article has a step-by-step tutorial on downloading your Ancestry matches to our Excel spreadsheet template. There are quick videos for each step so you’ll see exactly what to do if you’re not familiar with Excel.
Before we get to the tutorial, I’ll show you exactly what you get in the spreadsheet. Then I’ll go through some benefits and ideas for using the spreadsheet in your family tree research.
What Does Our Ancestry Match Spreadsheet Give You?
Our spreadsheet template will format your Ancestry matches into a list with this information per DNA match:
- Match name
- Number of shared centimorgans (cM)
- The type of family tree:
- public linked tree (the most helpful to you)
- unlinked tree (still helpful)
- private linked tree (not helpful, unless they respond to your messages)
- no trees (I usually skip over these or filter them out)
- The number of people in a linked tree
- Whether Ancestry has identified a common ancestor with you
- Mother or father’s side (if this information is available)
Here’s an example from my latest download:
Benefits Of Working With Ancestry Matches In A Spreadsheet
The power of spreadsheets lets us search, sort, and filter the match information in a single list. Here’s some ways that I put this to use.
Filtering And Sorting On Tree Size
Our free spreadsheet template automatically puts the tree size for linked trees into your spreadsheet.
If my research time is limited, I like to filter on a minimum of twenty people in a tree.
But maybe you don’t want to skip any match. Then sort the list in descending order of tree size!
Finding Ancestry Matches On Other Sites
Ancestry is the odd one out when it comes to consumer DNA sites for genealogy. 23andMe, MyHeritage, and FamilyTreeDNA all allow you to download a list of your DNA matches.
It can be a very worthwhile exercise to discover which of your Ancestry matches are also on these other sites. In my experience, most people use one of the sites to have their most up-to-date family tree. That may not be Ancestry.
When you’ve used this article to download your matches, you may find these tutorials helpful:
Using The Leeds Method
The Leeds Method is a way of organizing your DNA matches and shared matches to help getting past brick walls.
We have a separate spreadsheet for working through the Leeds method.
When you’ve gone through the process here, you can check out our article on using the Leeds Method with Ancestry.
Why Isn’t There A Simple Button To Export Ancestry Matches?
Ancestry doesn’t provide a way to download your DNA matches from their website.
In 2020, Ancestry stopped the use of several independent applications that scrolled through the match list web page and copied the data into spreadsheets or files for you.
But you can also do this for yourself. Our tutorial is going to be a breeze to follow if you are familiar with using Excel macros.
And don’t worry if you don’t even know what that means.
I’ve embedded video walkthroughs of my screen that takes you through the steps to get your matches nicely formatted in an Excel spreadsheet.
Getting Around Some Limitations Of Excel
The method that I describe in this tutorial uses filters in Excel to find the useful match details amidst all the other text that comes from the web page.
Unfortunately, Excel filters can get very slow (or not work at all) with large amounts of data.
If I try to put a filter on a download of all my matches, I have to watch a little blue wheel spinning for what seems like hours. I’ve never been patient enough to wait to see how long it takes.
So, I recommend that you work with three to four thousand matches at a time. If you have a very powerful local computer, you can experiment with a higher number than that.
But how do you work with smaller sets of matches? Easy. Just use the Ancestry filters. That is step one of our walkthrough.
Step 0: Download Our Spreadsheet Template
To make this as easy as possible for you, I’ve put together a free Excel workbook that contains a powerful macro for formatting your data.
We will send a download link to your email. Fill in your email address below. It’s a two-step verification process, and you will get the download link in a few minutes.
When you open the spreadsheet, Excel will warn you that it contains a macro.
You may also need to “enable” the content to use the spreadsheet (just click yes if you’re asked).
Does the end result not look right?
I’ve had to change the macro twice in the past year to allow for changes in the Ancestry display. The most recent was the addition of the percentage of shared DNA.
If you experience a problem, please drop a comment at the end of the article. I’ll check what’s changed on Ancestry again!
This works best with the Chrome browser
A reader commented that this process works best when using the Chrome browser.
Step 1: Filter Your Match List On Ancestry
I like to start with my matches who have public linked trees. They are most likely to be useful to my research.
Later, I will run a new download on a filtered list of matches with public unlinked trees.
You can also use the centimorgan range filter to take sets of matches at a time. For example, take all your matches above 100 cm. Then drop down to a range of 40-99 cM etc.
If you aren’t familiar with the Ancestry website filters, then watch this quick video.
Step 2: Scroll Down To The End Of Your Matches
The match list web page uses a design called “continuous scrolling”. As you scroll downward, the web page keeps loading more matches until there are none left.
In this step, you need to scroll down to the end of your filtered matches. If you have a few thousand in your list, that can be very tedious.
My video shows you a very simple trick to avoid a pain in your finger from pressing the “page down” key. It’s no big secret: I simply balance a carefully positioned object on the keyboard and go have a coffee.
Extra tip: check that all info is shown on the last few matches
One more tip: make sure that the entire display has finished loading. I’ve scratched my head wondering why the last fifty matches in my spreadsheet didn’t have any tree information.
It was because the final match names had been displayed on the web page, but I’d copied the data before the tree information had appeared on the screen.
Step 3: Copy, Paste Special, And Remove Blank Lines
Now that you’re down at the end of the matches, we’re going to copy the web page text to a spreadsheet.
We make sure that it’s formatted properly for later steps by using some built-in Excel features.
There are two steps: copy the matches, and then remove blank lines from the spreadsheet. This two-minute video walks through the process. I also lay out the sequence below.
Copy Matches To Excel
- Select the entire web page by clicking the “ctrl” and “a” keys on your keyboard.
- Copy the contents with “ctrl” and “c”
- Move to the next tab in your Excel spreadsheet
It’s important that you use Excel’s paste feature at this point. If you use your keyboard, you’ll get all the images e.g. the Facebook avatars, etc.
- Right-click in the first cell of the worksheet to open the context menu.
- Choose “Paste special”
- Choose “Text” as the formatting option
Remove blank lines
You’re going to see an unreadable splat of data in your spreadsheet now. Don’t worry, we’ll be cleaning it all up to be useful.
But the next thing to do is remove empty lines from the worksheet. If you don’t remove blank lines now, a later step won’t work properly later.
Thankfully, Excel has a built-in feature to do this. If you’re not sure how to do so,follow these simple instructions to remove blank lines in your Excel worksheet.
By the way, it’s possible that you don’t have any blank lines in your display. For me, they appear with matches where I’ve added notes. And you may not use the Ancestry note feature.
But to be sure, run through this process anyway.
Step 4: Run The Macro To Highlight The Match Names
And this is where (some of) the magic happens. A macro is a piece of code that has been added to an Excel spreadsheet.
Our spreadsheet template contains one macro (there is a link at the bottom of this article for a free download).
Just make sure you’re on the page with the copied Ancestry data when you run the macro. If you’re not sure how to run a macro, watch this quick video:
Don’t See A Developer Tab In Excel?
A reader commented that they couldn’t see the Developer tab in Excel.
Don’t worry if it’s not showing for you. Follow these instructions to show the Developer tab in Excel.
What does the macro do?
The main point of the macro is to find and highlight all the match names in the big mass of data that you’ve copied.
It also copies the centimorgans and tree details into the same row as the match name, because I find those useful to have in the final spreadsheet.
The reason to make the match name line bright red will be clear in the next section! We’re nearly finished, I promise.
Step 5: Filter On Highlighted Match Rows
Now, we use Excel filtering to filter the first column on the color red.
You may not have been aware that Excel lets you filter by colors. But when you add a filter on the first column, you’ll see “Filter by color” as a choice in the drop-down menu. And red is available as a target.
This video shows you how to apply the Excel filter.
Step 6: Copy The Filtered Rows
Our spreadsheet template has a worksheet that’s been set up with a header row and formatted columns.
This is the final step to copy the filtered content into a nicely formatted list.
- Copy the five filtered columns i.e. highlight the five columns and use ctrl-c.
- Switch to the third worksheet which has been set up with a header row.
- Click into the second cell in the first column.
The trick is to use “Paste special” to paste the values.
When you right-click in a cell, you’ll see “Paste special” in the list of items. Choose “values” from the options presented. This stops the scary red color from being copied!
There will be one extra unwanted line in the second row. Delete that row, and you’re all done!
Alternative Methods And Other Programs
Don’t like Microsoft Excel and want to work with Google Sheets instead? You’d have to rewrite the Excel macro in my template to make it work with Sheets.
Alternatively, the crew over at Family History Fanatics have their own version of a spreadsheet template for Google Sheets. Check out their tutorial here.
If you don’t mind paying the price of a cup of coffee per month, then you can check out the DNAGedcom client tool. I recommend that you join their Facebook group, which will have answers to all your questions.
If you find this spreadsheet useful, consider donating the price of a cappuccino ($5) with the PayPal button below:
88 thoughts on “Download Your Ancestry Matches To Excel Spreadsheet”
Hi, thank you for the Excel spreadsheet and the clear guidance. Seems to be a problem in the macro not picking up matches where the new ‘Do you recognise them’ feature has been used. All others were ok. Thanks.
Thanks for the heads up! I’ll take a look.
Thanks again for letting me know. I’ve uploaded a changed version.
Recent reader, first-time caller. Really appreciate the assistance on your site. It is helping me move forward with finding long-buried (pardon the expression) family lines. On my mother’s side I can go back two generations and at that point am not even sure of the family names.
I love the semi-automated Excel immigration. Noticed that within the past few days Ancestry has put in ANOTHER set of format changes so expect you’ll have to go through changing and updating this again.
In the meantime, as long as you are working in Microsoft Excel, you may as well use Word.
Word has very powerful find-and-replace functions allowing finding and replacing several lines of data in a single search. What Word does not have is decent spreadsheet functions.
The solution is to use Word’s tab or table features to create an intermediate, formatted page which is then copied into Excel. It is also possible to simply save the final, edited Word document into a txt file with delimiters, then open it natively in Excel.
As a Jewish person, I have a million 4th cousins and a billion at 5-8th cousin on Ancestry. When I copy their DNA Matches page, I scroll down to the last relative I can identify (paternal grandmother’s brothers great-great-granddaughter, about 70 cM shared DNA) and hold CTRL+SHIFT while returning to the top of the page. Then I use CTRL+C to copy only that portion instead of allowing the entire page to resolve and copying everything everything. There are matches there in the Distant Cousin realm with so little data as to be worthless to me. That copied portion is then pasted into Word (or Excel for those playing by the rules).
Hope these comments prove useful to some.
Thank you for the detailed comments. I haven’t messed about with Word much, but you have described a very helpful approach.
And thanks for the heads up about the changes!
Thank you so much for taking the time to make this necessary task easy to do. Shame on Ancestry for not providing a way to do this.
Oh my goodness! I am absolutely obsessed with your brilliant spreadsheet! Thank you for coming up with such an easy-to-understand solution for this annoying problem. Ancestry seems to love introducing “fun tools” to draw in new customers or reel back in testers who don’t subscribe, but seldom offer much of real value to the hardcore genealogy researcher. I’ve already shared your spreadsheet with several DNA Facebook groups I’m in. Can I ask for one minor tweak to the macro? One of the things they do get right is that when a parent (or parents) test, it shows you which side your match is on. It would be fantastic if your macro could grab that info & add it to a column in the final spreadsheet. Again, thank you so much for this great tool!
Thank you for the suggestion, I’ll take a look at getting that in.
I downloaded the new macro from a much older version. I suspect I am doing something wrong.
i tried to paste some screen grabs here, but apparently that is not enabled.
The download from Ancestry worked fine.
After the macro is run, some of the wrong cells are red (not necessarily red).
Some names, and some other rows got highlighted. (in column A but not a name)
A single letter appears in the second column (B) of some rows. usually in the row of a wrong cell highlighted.
Hello, what am I (or the macro) doing wrong?
Hi – Thanks for this but a little confused, Some of my names are appearing twice in the list when pasted in, but only showing once on the Ancestry, these do not have managed names..
Also why only some of the pasted info is put into columns, when especially in my case I need the Name & the Managed Name, as I have Smith & Jones as part of my Grandparents, and many of the matches using initials which are the same. Also notes, you refer to notes being useful above, but not on the extract macro?
The double names used to be when match had a picture – I was filtering that out. Maybe Ancestry has changed something there, I’ll take another look.
Notes are difficult to grab automatically so I leave them behind.
I took another look to see if there’s a glitch in the final list with double names. But I don’t think so. The reason you see them in the inital copy is that they are “behind” the picture for anyone who has uploaded a photo or has linked their account with Facebook. But the final copy gets rid of the duplicates.
Re Running the Macro.
“just click on the Developer Tab”
You have a developer tab?
BTW I have Excel Version 2108 Build 12346.20238
Is this something I can fix?
Thanks for mentioning this, sometimes Excel doesn’t show the Developer tab. You can enable it with these steps (I’ve also updated the article):
(1) Expand the File menu in Excel
(2) Click on the “Options” link at the very bottom
(3) Click on “Customize Ribbon” (near the middle of the left pane)
(4) Ensure that “Main Tabs” is selected at the top of the right pane
(should be the default)
(5) Turn on the “Developer” checkbox (near the bottom of the list)
Thanks for this
I see that the Relationship is not passed on, with our ability to now add this if we wish, it would be useful to pass it on to the final sheet.
I ran this August/ September, and got more fields than I have this time, have you limited it?
I seem to be missing fields
I don’t think so. Ancestry may have changed the page again, I’ll have to test it.
Macro does not work as described needs updated.
Works well when matches are copied using chrome browser, not so much in firefox.
Thank you for the feedback. I’ve put an update in the article to recommend the chrome browser.
Thanks for this great overview of web scraping on Ancestry. I did something similar, using multiple tabs and formulas instead of macros. This allowed me to create the standard cluster chart from the otherwise inaccessible Ancestry data. I pasted data from my DNA match page into a single column, then pasted data from 100 (or so) different shared match pages in the subsequent 100 (or so) columns. Using control-A to select the entire page, then copy, then switch to Excel, then paste into the next column required quite a lot of time, but otherwise worked well and ultimately delivered the very helpful DNA cluster chart that groups matches by common ancestors.
IW, you are so right: Firefox would not allow copy/paste of the cM data that Ancestry encapsulates within a button, but Chrome would. We Firefox users banged our head against a wall for quite a while before discovering this very important piece of information! Thanks so much for sharing!
I’ve done similar, it’s true that getting the shared matches is time-consuming!
Instead of “Paste”>”Special”>”Text” when loading the Matches, I just simply “pasted” into Excel. It can take a LONG time for the machines to chug and the output looks messy, full of pictures, etc. BUT when done, if you “Find&Select”>”go to Special…” > select “Objects” > then push “Delete” key, all the pictures disappear. Then follow instructions to remove blank rows and run the Macro … Why this way ?? It preserves a HYPERLINK. For me, I ended with the same table look but I was able to click on any Match Name and it took me straight to the appropriate Ancestry MATCH page in a browser for easy access to more details
Thanks for the tip. As I was reading, I was wondering why you’d do it this way – but I can see how the hyperlink would be useful!
Ditto, I did the same. That sort of auto-sourcing aspect worked really well whenever questions arose.
Thanks so much for this great workbook!
I’m having an issue where the macro runs fine for my lower matches, but it’s skipping over my closest matches and either not highlighting them, or it will highlight the name, but not pull up the other info to the same line.
I copied the info from Chrome and ensured the page was fully loaded. I’m working with the desktop version of Office365 Excel.
My guess is that you have added great notes to your higher matches that describes their relationship to you. Unfortunately, the macro can get confused by content in the notes vs content in the normal display.
If you wanted to email the spreadsheet to our support address (see the contact page), I’d be happy to take a look. You wouldn’t need to send the full data, just the top matches that aren’t working for you.
I’m a Mac user. Does this work with “Number”? Looks very interesting but I don’t have Excel.
no, unfortunately the Mac application is different so it wouldn’t work. I don’t have a Mac so can’t provide an alternative.
I just wanted to say thanks for making all of this info available – it is greatly appreciated! As far as I am aware no one in my family has ever attempted a family tree, so I have ordered a kit from Ancestry and am excited to get started. The info you have provided here has already been super helpful, so thanks again!
I can’t seem to find the downloadable macro.
There is a box in the middle of the article that lets you enter your email to receive a link to downlaod the spreadsheet.
Great tool, clear instructions. However, it failed for me when I hit Run the macro – nothing happened. I tried it with the column selected, with the entire Splat Sheet selected, and with nothing selected. Saved and re-opened the spreadsheet.
I am running Window 10 and Excel that is part of MS Office Pro Plus 2016.
Please ignore my previous post – I had not removed blank lines.
It would help to not have to follow so many side links and just put all the instructions in a compact form.
Again, thanks for developing this.
Thanks for updating on your progress.
I was just about to try this when I realized I was going to be doing a lot of scrolling. Then I remembered that someone had written some code that you could run that would auto mark matches on ancestry so they weren’t removed from your list when ancestry (in their infinite wisdom) decided to remove lower cm matches. Part of the code was an auto-scroll feature which I edited a bit to help with another project. Here is what you need to do:
1. Open your ancestry match list in Chrome.
2. Filter as you see fit.
3. Open the Developer Tools, found under more tools.
4. Switch to the console tab.
5. If you know how many matches you have, or have filtered it down to, take that number and divide by 50. Add a 5 to that for good measure.
6. Edit the following code, to replace the # with the number you got above If you did not do that math, replace the # with any number, If you use a bigger number the more times it will scroll so even if it finds the bottom you may still have to wait for it to stop trying. If you use a smaller number, you may need to run it multiple times to actually find the end.
for (let l = 0; l setTimeout(r, 15000));
console.warn(“Number of times scrolled ” + l);
7. Copy and paste your edited code at the > in the console pane.
8. Press enter and wait. You will know when it is done, when it has stopped the count.
9. Once it is done, you may want to manly scroll a time or two to be sure you actually found the end. If necessary, consider rerunning the code, but pasting it again.
Sorry, realzied that I did not copy all the code. So here is the actual code:
for (let l = 0; l setTimeout(r, 15000));
console.warn(“Number of times scrolled ” + l);
you can also get chrome extensions that do this for you.
The only extension I know of (Ancestry Match Downloader) hasn’t worked in years.
I use LibreOffice Calc instead of Excel and when i try to paste in to the SPLAT tab nothing seems to be pasting. Is this because i am using LibreOffcie?
Unfortunately, this won’t work with LiberOffice Calc. Not just because of copy and pasting – the Excel macros won’t work in different software.
I manage 3 DNA profiles and downloaded the macro, applied to each one separately. I loaded the matches into 3 excel worksheets. Is there a way I can search for and save same name matches using 2 at a time? Would I need additional code to create a macro to do that or is it available? I have about 660 4th cousins or closer in each one. The macro is used for deleting same name entries but I want to save a file with just those names that appear in each profile.
I hope you understand what I am asking.
I think I understand. You want to identify matches that are common across the three DNA profiles?
I think that the Excel has a feature that can help you with this – conditional formatting.
Basically, if you get three columns of DNA matches into one worksheet, you can get Excel to color-code names that appear in all three columns. The trick is to use Conditional Formatting (in the styles section), choose “Highlight Cells Rule”, and choose “Duplicate Values”.
I describe doing something similar in a different tutorial where I use the technique to highlight the same match names in spreadsheet data from Ancestry and MyHeritage. The relevant section is at this link.
Hope that would work for you? Let me know if I’ve missed what you meant!
I copied the matches from Ancestry but when I pasted them in Excel it didn’t include shared DNA information. Then when I ran the macro nothing happened – no names in red.
The spreadsheet doesn’t download shared matches, it only takes the information from the main list of matches.
Sorry to hear you’re having problems, sometimes Ancestry tweak the web page and the macro stops working. But I ran it today and it worked find.
Be sure that when you’re pasting the values, that you use “Paste Special” etc
I meant the relationship e.g. 2nd – 3rd cousin and DNA cMs shared with each match doesn’t show.
Note – when I click on ctrl a it doesn’t highlight those items.This is a sample of what I get –
Private unlinked Tree
Do you recognize them?
Hi, Can somebody share the answer to this question? I just downloaded the spreadsheet today and seem to have the same problem with the CM and relationship data is not selecting when I use CTRL + A on the Ancestry match list.
I assume I am doing something wrong but no idea what.
So excited by the potential of this spreadsheet!
Hi Mary, I wrote the tutorial. Could you tell me what kind of computer you’re using? Is it a Mac or a Windows? Are you using a laptop, tablet, or a chromebook?
Please ignore my query. After sleeping on it I realized it was probably because of my browser. Switched from Firefox to Chrome and it worked smooth as silk.
Apparently Firefox doesn’t like to copy all the columns.
Thank you so much for your work. This is very helpful. One tweak I’d like to add is showing note comments, if possible. I’ve listed all my MRCA’s there and would love to have the included. Thought I might be able to modify your macro to include this, but it appears beyond my capabilities at the present.
I may not get to this for a while but I do make changes to the spreadsheet when Ancestry changes their website. So, I’ll keep this suggestion in reserve.
You are my hero! Thank you for this clear and concise how-to tutorial. Of course, double thanks for the macro! I have a suggestion to make this page even more awesome… if its possible… have the comments scroll down from newest to oldest, rather than oldest to newest. You are appreciated!
This looks excellent. I spent 2 hours this afternoon and created a just about adequate macro to do this. Yours looks so much better. Here I am, in bed, just after midnight, like a kid on Xmas eve. Shall I get up and try it now…?
Get some sleep, then go for it!
It worked like a dream. I didn’t understand the line, “There will be one extra unwanted line in the second row. Delete that row, and you’re all done!”
I have requested the spreadsheet twice and still have not received it. I have also checked my spam/junk/trash folders and it did not come through them either. I was really interested in trying this and I am so disappointed.
I’ll send it to your email. Give me a few minutes.
I sent an email to you with the file. Hope that helps.
Margaret, Thank you so much for the spreadsheet. I was finally able to download it and it worked like a dream. I have been playing with it for a couple of hours. This will make it so much easier to figure out my dna matches from Ancestry.
Margaret, thank you for this. I did it once manually, got very useful results, but vowed never to do it again. Now, with your macro and procedure, I can do it easily whenever I need it. Wonderful.
A couple of things to let you know. First, I’m sure you’re aware but for readers who aren’t, your procedure is for recent versions of Excel. I’m on Excel 2003 which doesn’t have (to my knowledge) blank line removal nor color filtering. However, ASAP Utilities has both so I got’r done.
Second, Ancestry has some new relationship names that aren’t in your vCousinList array. Perhaps these new names came with the new Edit Relationship feature? In any case, some of my matches were missed by the macro because Ancestry labeled them sister, nephew, niece, aunt, and uncle. There’s probably also a brother and maybe some others. I added the new ones I needed to your macro code and it worked beautifully.
Regarding sides, there’s also a “Both Sides”.
Thank you for letting me know! I have a challenge with the relationship list because I don’t have many close matches. I’ll take another look to see if I can fill them in.
Drifting off to sleep and I had an epiphany: what if I used your spreadsheet for a specific proxy match and our shared matches? Jumped out of bed to try it and it worked!!
This will be great for working on a specific line/cluster. So, I’m tweaking my Grid/Matrix spreadsheet to accompany this new revelatory tool you’ve created. Will share as soon as it’s done (and have slept). 😉
Please do share when you’ve had a chance to look.
Please delete my reply above.
Here is the improved Grid/Matrix sheet: https://1drv.ms/x/s!AnY4cGihA9l3keJDgMAVdyfJ8RRWGw?e=tladas
(I expanded the matrix to accommodate 100 people instead of just 50.)
Use this is to create clusters from a shared/PROXY match – i.e. someone who represents a specific line. Entering a 1 in the appropriate grid space colors that square and it’s corresponding twin on the other side of the diagonal.
By going through a couple of shared match lists in a particular group, we can create our own clusters that could likely indicate triangulated matches to a shared ancestor.
It’s not without some effort, but I think could be extremely helpful for brick walls.
I deleted the earlier reply as requested. Many thanks for sharing this.
I’m not sure why but when I run the macro only the cm data lines up with the name. The other information stays in the original column. I am using Excel 365.
Thank you for alerting me. Ancestry changed the display recently so I’ve had to make a corresponding change to fix the macro.
I’ve uploaded a changed file. So you don’t have to resubscribe, I will send a link to your email.
Hi Margaret, I am having the same problem as Nicola. Should I re-subscribe, or can you send a link to my email as well to get the updated macro?
Daniel, I just sent a link to your email.
Thank you Margaret, I have the new spreadsheet. Is there an easy way to copy the macro from the new spreadsheet into existing spreadsheets?
I guess “easy” depends on how familiar you are with the code interface in Excel. You could open the module and copy the content.
But you can’t use the new code on an old copy of your Ancestry data.
The change I had to make was in response to the display changing what used to say “mother’s side” to “maternal” and father to paternal.
So, you should take a fresh copy of the display into the new spreadsheet.
Thanks, Margaret. I had already loaded thousands of matches into the Splat Sheet, and wanted to avoid having to do that over again. I was able to open both the worksheet I had started and the new worksheet, then go to the Developer menu, open the VB Editor, and drag and drop the new macro from the new worksheet into the worksheet I had started. Fairly painless …
Would you also send me the newest update that you sent Daniel and Nicola? I’ve been banging my head against my computer wondering where I went wrong and why it left behind so much information that I got successfully when I did this a year ago. LOL Thanks!
I sent a link to your email!
I have spent 3 days trying to get your procedure for loading my matched DNAs to an Excel SS. The fault is with Ancestry not your procedure. When I go into Ancestry, select my cM range and hit Cntl A, the cM range is not selected. The line Parent’s 1st side is selected. So when I copy using Cntl C, the cM line is not copied, the “Parent’s 1st side” is copied. And that is what is pasted in the Splatch table. I think your procedure would be excellent to use. What can I do to fix it?
Someone else had a similar problem recently and commented that the problem went away when they used the Chrome browser instead of Firefox. Does that help?
have “subscribed” several times but have not yet received the link to the template
Has it got caught in your spam folder? I’ve just sent you an email with the zip file attached.
Spreadsheet & macro work fine using Excel on a Mac but the Paste onto the Splat sheet also brings with it the match image. This hides a blank cell which in turn stops the macro.
Is there an easy way to delete all the images to expose the blank cells?
When you paste, be choose to use the “paste special” option and choose “text only”. This leaves the images behind.
I’ve just downloaded the file but as it is an “xlsb” file, I don’t seem to be able to enable the macro. I have changed the settings within the trust centre in excel 2019 and also looked online for a possible solution: the only one was “change blocked to unblocked” on the general tab within properties in windows explorer but the file does not have a “general” tab. Any ideas?
All sorted now!
I downloaded your Excel file last week (thanks for that) and followed the step-by-step procedure, but when I launched the macro, it scrolled down and nothing happened. I have a few duplicate names and I work with the French Canadian site. Could it cause any problems? What did I do wrong? Need help.
The macro depends on looking for words like “2nd Cousin” to split up the details on the page. So, it won’t work with the non-English site.
I logged into the Canadian website and set the language to French, and it was the same as you described.
When I switched the language to English (the drop-down at the top right of the page), and copied the page to the spreadsheet, it worked.
I hope that’s a workaround for you.
It worked! Thank you very much.
Hi Margaret, Great spread sheet! Loved your logic to parse the file. If you’re interested, I revised the macro a bit to (1) read the whole ancestry text file without having to delete blank lines, (2) write the results to your Matches sheet as part of the macro, (3) speed up the macro, just email me and I’ll forward.
That would be great, Paul. I sent you an email.
Sounds good, I’ll send.