Thursday, June 1, 2017

DNA Matches and Spreadsheets

I've seen a lot of people on Facebook asking for spreadsheet help and tracking aDNA results so I thought I'd throw my method into the mix. This is not for everyone. I am all about having what I need in one place. And everything has turned into a monster of data entry. You've been warned.

Unfortunately I can't give credit for the original idea. Windows 10 ate the first sheet I downloaded and I have recreated parts from memory. If anyone remembers who started this method, please let me know. I searched Facebook and couldn't find the post.

The original spreadsheet was a list of matches with an x down and across if a person was a shared match with another on Ancestry. Don't you just love my expert command of spreadsheet terminology? I've expanded it to include data from all the major sites, Ancestry, GEDmatch, MyHeritage, and FTDNA

Since I can't leave well enough alone, this has morphed into what I use today. And I'm still adding columns as something pops into my mind. The sheet is heavy on color coding and external links.
And a lot of privacy scribbling for this post.


I keep all the identified matches at the top in ascending order of cousinship. It's a helpful reminder of the randomness of DNA inheritance and lets me see just how "off" some of the test company predictions are.

At the top are my test IDs and color keys for each suspected/proven line.

A - match #. Number them however you please. Mine are a bit wonky since I insert people here and there and I haven't renumbered lately.  Color coded pink and blue for maternal and paternal lines if I'm sure.

B - GC. This stands for Genetic Community, a new Ancestry feature. It helps me see at a glance the "buckets" that my unknowns fall into. Use with caution. I have a proven match on one of my German lines that shows as Irish.

C - the name, easy enough. But... I link the name to the Ancestry match page so I can get there quickly.

D - Email  I just added this the other day. I use Genome Mate pro as well but I got tired of toggling back and forth. And of course a link that opens a new email.

E - Message. The date I sent an email or message through Ancestry or My Heritage message system, I haven't found this too helpful, probably an unnecessary field since I try to keep a message copy in Genome Mate Pro

F - Legacy #. This is very helpful. This is the RIN (person number) in my Legacy Family Tree software. Even if I can't prove a relationship I've started adding the person to my tree as an unlinked person.

G - Notes. More extensive research notes are entered in Genome Mate Pro.

H - MRCA - Most recent common ancestor along with the RIN

I - What the relationship is if I know it. If not I put an "*". This reminds me that I need to highlight the Ancestry star. I star all my matches so I don't keep checking them when I'm in Ancestry.

J - cousinship.


K-AG - test results along with links to trees if posted. Ancestry has changed the linking to their trees so I link to the person's profile page, which you can't see for privacy reasons
.




AH-end - The shared matches. You can really see at a glance which family group my matches belong to. There are probably errors here, but I look at it as a start. I use the Gedmatch abbreviations to identify a match, A for Ancestry, T for FTDNA, H for MyHeritage and G for Gedmatch, S is for self.



This is another match cluster of some Irish matches




Now if I just knew how they connected.

Finally, I have a sheet for each test I administer.

I realize that this is lot of information and most people won't go to these extremes but it works for me.

I mentioned that I also use Genome Mate Pro. Here's the link to their Facebook page where you will find links to downloads.This is where I put my more extensive research notes on each match. Warning, again a learning curve which I haven't mastered.

Any suggestions, or questions. I'd love to hear them, especially those from the spelling and grammar police.

No comments:

Post a Comment