Ancestry does not allow you to download your Ancestry DNA matches. So, what's a researcher
to do when you want to try to organize and manage your DNA research? Well, that's when my special
guest Greg has a really cool tool to help you grab your DNA match list. Put it over into a
spreadsheet. And you'll want to check out the blog post, that I'm going to talk about at the
end of the video, for a link to access what he's- his cool research tool. So, let me welcome Greg
Clarke from Ontario. Hey Greg! How are you? I'm doing fine. How are you Devon? I'm. I'm better
than I deserve, if I can borrow that from Dave Ramsey. I really am better than I deserve. So,
walk us through how you came up with the solution to a problem that we talked about in the Family
History Fanatics fan channel membership. Oh, okay. Sure.um, So, during the.um,
during the membership discussion we're talking about.um, matches and ways of organizing the
matches. And when you have a long list of matches, then you know, lots of genealogists
think. Well if I just grab those and put them in a spreadsheet, it'll be perfect. Um, So,
someone had said, how do we do that? and I. It doesn't work I, I replied in the chat, oh it's
easy. You just copy and maybe you just CTRL-V or maybe CTRL-shift-V. Something you know simple/
and they tried it and didn't work. And I was, of course. Shocked and aghast. And sure enough
it's true. Because uh. Well first of all, Ancestry doesn't have a way to download it automatically,
like some platforms do. And then when you do just a bald copy and paste it doesn't format
very nice at all. Um. So, luckily I've done lots of spreadsheet work and played with formulas. So,
I thought this is just a problem that we can solve with a few formulas. Um, So, I've done that. So, I
will share that with you now. So, what I'm sharing now, uh, put down the steps so that you can follow
it. And this is in a Google spreadsheet. And the. It's got two tabs. And if you look at the bottom,
there's one tab that has the instructions. On the second tab is the worksheet. That actually
is where your matches will show. Where you dump the data and your matches will get formatted.
What I would suggest is that when you first go to this, if you make a copy for yourself.
And then you can save it in your own drive. This is so, so important. Because we are going to make
this spreadsheet for Google available through the link in the blog post I'm
going to talk about later. But you're not going to get request to edit. Make a copy
and save it to your own Google drive. And then you'll always have a copy to use for yourself.
And the nice thing about that is when you do make a copy. So, I've just made a copy now. And you can
say my Ancestry DNA Match Maker. Now whatever I do here, Devon's not going to see on her version
of the Match Maker. So. Because I mean people's DNA matches is kind of a personal information. So,
though I'm apparently sharing all of mine. So, so. That's step number one. And that's important it.
Isn't Google. It's in a Google spreadsheet because that's the platform that can be
viewed on a Mac and on Windows. And the formulas will work the same. Now if you save it as an Excel
file. I haven't tried this on Excel. But if you were to download it as Excel, I know there are
some differences in the way formulas work. And I'm using some fairly sophisticated formulas.
Usually it goes the opposite way. The Excel formulas don't always work with these
Google formulas. I can't guarantee it'll work on the Excel. So, basically step one is you after
you've made the copy. So, you go to your ancestor DNA list. And all you have to do is highlight all
of the text and copy it. And the easiest way to do that is on a Windows PC, you just do Ctrl-A. A for
all. On a Mac it's Command-A. And I don't know if you can tell, but the text has been highlighted.
I'm going to go CTRL-C or Command-C on a Mac to copy it. And then going back to the spreadsheet,
over on the worksheet area. Starting at the cell that's highlighted in yellow. That's your target.
Put your cursor there- the yellow cell. And then what you're going to do is, is paste. And you
can either do a CTRL-V or a CTRL-Shift-V I'm going to do the Shift-CTRL-V because
takes out all the formatting. And this is a a pro tip for, um, when you're copying and pasting on a
computer. If you've copied a bunch of text and it has different formatting. Like some bold,
some underlining, maybe some hyperlinks or whatever. If all you want is the actual text,
you don't want all the extra hit formatting. Because it makes it look ugly. Then Shift-CTRL-V
or Shift-Command-V on a Mac will paste the text without doing it all the extra stuff. So,
you saw that I pasted it there was a pause there while I kept on talking long enough to give the
computer time to do some computations. And now if you look you can see that all of a sudden
I have a whole pile of information. That's all sorted. All this stuff from
this column G onwards is the information that's been sorted by my various formulas that are
hidden in the background. Wow. You recommend the CTRL-Shift and the Shift+CTRL+V all at the same
time. Dump it in there. And just give it moments. And then all of a sudden the right side popped up.
That's exactly. Cool! So, I parsed it. So, it shows the name of the person. Now in some cases.
And if you look at the doc, the list. Sometimes you'll see like there's one. There's a KR
KR is managed by Victoria okay. So, there's two names. I'm not going to be in that column one. Oh,
okay. One's the person. And one is the DNA kit manager. In other ones there's just a single name.
And then sometimes there's a category. And then a name. Okay. It seems a little inconsistent but
I've tried to, uh, work the formula so it would guess and. And plot things properly. All of these
people are your matches. And they match to you. And you can look at the the centimorgans. Yeah,
ok. Tells you how much. And it. By default now. I use the default which is most to
least. If you sort it some other way, sometimes I found the formulas don't work as well. Okay.
And I'm not sure why that is. But generally most to least is the one that's most useful anyways.
And you can tell some that have trees. And some that don't have trees. And
then the ones in the bottom here don't have any type of trees. It looks like
they found a common ancestor. How many people are in the tree. If they had one.
And I use notes a lot. And so, what's nice is it also brings over your notes and then puts that
there. The way I use notes. I put the family name, the surname, and then the relationship. So,
first cousin once removed for example. And then the the actual connection of who did the
granddaughter of her. You know. So, it makes it easier for me to find. I've tried to make
it fairly straightforward to use. When we wanted to do sorting and manipulation. I know there's a
way to copy things without the formulas. And then you create an essentially a duplicate.
Yes. Could you walk us through that? I can certainly do it. That's an excellent suggestion.
Okay. So, to do that. Because this page is all reliant on the data in column A. And then it
auto populates the stuff all through the rest of it. But you're right if you want to manipulate
it you want to copy this information just as it is. So, what I do is I highlight in columns G
to P. So, what I did was I clicked on the letter G. Okay. So, that you can see the column G is
now all highlighted. And then I'm going to go over. And then I'm going to hold my shift key
down with. I'm holding my shift key down here. And then I'm going to click on the P up here.
Okay. And that's going to highlight all of those columns in between gotcha including the G and the
P. Now what I'm going to do is I'm going to go CTRL-C. Or maybe I'm going to Edit-copy.
Same sort of thing. Command-C. And now I'm going to go down here and add a new sheet. Okay. I'm
in the very bottom. Can you see where I am? Yes. I'm in the bottom left corner. I'm hitting. And
I'm adding a new blank sheet. And I'm going to do that trick I did before CTRL-Shift-V. CTRL+Shift+V
all three of them. And if you're on a Mac. Command+Shift+V, all three of them at the same
time. Actually what you should do. If you're doing one of those combinations put the Shift-Command or
Shift+CTRL down first. Hit the V at the very end. Oh, okay. For all of those ones. Use the
letter last. Okay. Because if you're. If you're a little shaky and you hit the letter first,
then it doesn't always work. Okay. There you go. Oh, you know what? It's probably thinking.
There it worked. Now sometimes it takes a while if you've copied the whole column. That takes
a lot of memory. You might think it's actually broken. And it may spin out of control. But it,
it should pop up. I can. What I can do is. Let me go back. And I can show you a different way
of doing the same sort of thing. Please do. Instead of highlighting the whole column.
Because if you scroll down the column goes forever and ever and ever. Well it doesn't go forever and
ever. But it goes down to as many lines as there were in the original table here. Which you know is
in the hundreds. But really I only need the first 52. So, I'm going to click on the top left cell
that I want. Which is going to be the category name in G-2. I'm going to then move my cursor
in to. I'm going to be in column P because that's where the notes are. I want the notes. And I'm
just going to go down until. I'm just scrolling down until I find where I want, which would be
right 52. So, I'm just trying to find where those line up. There we go. And I'm going to. Now I'm
going to hold my shift key down. Holding the shift key down. I'm pointing to myself point.
Holding the shift key. And I'm clicking on this cell here P-52. You clicked on this cell. Category
name. Yeah. You moved your mouse without touching anything over to the one that has a little note.
And then you hit your shift and clicked in the cell. And you're good to go. That's exactly it.
Okay. Now uh if you have a mouse you can actually click on the category name and drag and highlight
all at once. There is another way that you can do it. Actually click on the category name.
And this one. Okay. Hold the shift key down right at the beginning okay. And this is kind
of a cool trick. Then I'm using my arrow keys. Oh, on your keyboard? On my keyboard. And as I
use the arrow key to go down. You've got a lot more control actually this way. Oh, nice! Yeah.
For those of us with unsteady hands. No but you see that. So, that actually. And then I'm using. I
once. I got all the way down then I started using my right arrow key. That's actually probably the
easiest way. If the most controlled way. Mice. So, now how do you copy it CTRL-C. Okay. Command-C or
Edit-Copy. Gotcha. See how fast that. There was no delay there. No. I'm gonna go add a new sheet.
Now again you're gonna do CTRL-Shift-V. Paste the values only. And then you've got just the data.
So, much faster. Okay Greg. Question of the day. That seems to be page one. How do we get page two
of DNA matches? Ancestry, in one of their most recent changes they give you a certain amount.
And then if you scroll to the bottom of the page, oh, I guess he wants more.
So, I guess the trick is to make sure that the page that you're copying has all the ones you
want. Okay. And if you. If they don't then just wait a sec to load up more. Okay. CTRL-Shift-V
paste. Now the top one should still be the same but I should now have more than row 52.
And you do. New names. Okay. New names with notes. So, yeah awesome. Thinking that I was done so,
soon. Okay, so that kind of leads us to the question is that sometimes we're going to come
back. And there's going to be more people, new people. Like if we do this a month from now we can
put it in there. And it will override the table. Some things may shift. Most things will stay the
same. And then when you want to manipulate it, then you do the highlight copy of just those
cells. The second method. Yeah. And start a new sheet. And put things in there. That way if you
did any kind of sorting or extra notes in your spreadsheet you're not affecting that. Right.
Nice. That's pretty slick. Thank you so much Greg for um helping us do that. I think this
is going to be a really, really handy tool, um, to help us get shared matches
into our DNA sheet. Greg did put his email. He put his email in this spreadsheet. And you can
contact him directly if you have any questions or something stops working. But you can access
that the link for the spreadsheet over on our blog. You can look where the description of this
video is and it'll be direct link. But that's how you find it. It'll be over there. It is free for
you to access. If you have any other tweaks and suggestions make sure you send them Greg's way.