Has Anyone done a spreadsheet on their matches?

Question

I'm always looking for new avenues to help break my Hughey my brick wall, so I'm thinking I should make a spreadsheet chart with my Hughey matches, MRCAs, Centimorgans, DNA Companies used, etc.

Has anyone else done something like this? How well did it work for you? What advice do you have?? What did you learn?

Comments

Debbie, this isn't quite what you asked, but I really think maps, though a lot of work, are more useful than spreadsheets.  I started a page that lists some resources and shows an example:

https://www.wikitree.com/wiki/Space:Chromosome_Mapping_Examples

---

Hi Julie! Is this like DNA painting?? I've done that before, but need to look at it again now that I have more matches and a better theory on my 3x great grandfather...

---

Yes (if I understand your question).  The website DNAPainter.com offers a type of mapping.

---

Oh wow. I didn't realize that! Thanks.

Answer 1

The NEHGS held a free Webinar yesterday (19 Dec 2019) at 3pm EST on DNA and included various ways to display family that included name, relationship, cM, etc. They generally record these and you might want to contact the NEHGS by email and ask if it's possible to watch the Webinar on DNA on the day and date provided above. If I get a follow up email with URL from them, I'll post it to this G2G site. It was very informative and showed different approaches to your question. Have a great Holiday!

Comments

Very interesting! Is it not on YouTube or Vimeo etc.??

---

Thanks I’d like to see this too!  Merry Christmas!

Answer 2

Debbie, there is a program called Genome Mate Pro that was recommended here by several people to track DNA matches, so I downloaded it and tried it - it's free.  I never got far with tracking my matches because I have so many (I'm Ashkenazi) that it was - and is - much too overwhelming to do much about.  As a result I can't really say much for or against the program, but it's probably better than just doing your own spreadsheet.

Comments

Wow. I've never heard of it. I will look into it. Thanks for the info!!

Answer 3

I've done a speadsheet like that. Here's what I do.

From the top left to right I have a column for:

1. Name.

2. cM

3. Relation

4. actual relation

5. Common ancestors

6. Shared cM project percentage

7. Contact info

8. Notes

9. Company

Using this, I learned that even 7 cMs can make a third cousin once removed. I also color coded my common ancestors. I listed all my matches and I did the same thing for my parents and my great-aunt.

Comments

I have a 2C1R at 10cM! I don't use the shared cM project percentage for matches farther than 3cM because I find them too unreliable. As you point out, there is a huge variance for small cM matches.

---

Dna is random. Main thing I learned after testing.  I think I have a 2c1r around there. Will have to check.

---

"Random" doesn't mean arbitrary. There are no reported cases of 2nd cousins who share less than 7cM, so 10 cM or lower for 2C1R is seemingly a very low probability event.

---

True. Just easier to say random or roll of the dice. :)

---

Hey, I like this list. Thanks!!

---

The lowest 2C1R I've run across is 8.5cM. Probably, it's possible to have 0cM (no match) but that has to be pretty rare. Even being below 20cM (making it a "Distant Cousin" match on AncestryDNA) is about a 1-in-30 chance, from what I'm seeing.

2C1R should be thought of as the most distant relation level where it is VIRTUALLY certain that there will be a match (half-2C, half-1C2R, and 1C3R are in that same level, of course).

So for 2C1R and beyond, the likely range of cM values is simplified - it's a question only of what the MAXIMUM can be. It becomes more a game of what is the probability of actually GETTING a match.

---

Thanks! I like this list too!

---

Glad I was able to help, Anonymous.

@ Frank: From what I've seen, DNA is wild and it's like a roll of the dice. Might be better off playing with Vegas odds.

@ Debbie: Thanks!

Answer 4

I have several types of spreadsheet.

1. A spreadsheet of "segments". I downloaded all of my data from various sites and put it in one big spreadsheet. I then sorted by chromosome number, then start position, and used this to identify triangulation groups. DNA painter will do this, but I like having numbers.

2. A spreadsheet of common match groups -- pretty much like the Leed's method, but with more refined groups than just one for each grandparent.

3. A spreadsheet of identified matches, including most of the information Chris gave in his answer, but including the entire descent for the other person from the common ancestor in case they should ever delete or hide that information. On one of these spreadsheets I include my father's information as well so I can easily see false matching segments.

4. My father has a spreadsheet created for visual phasing with his two siblings. If you test three full siblings, you can map all of the "crossovers" in your genome where one grandparent's DNA leaves off and another starts up.

Comments

Wow this is great! You think like do! Thanks for all the information Barry!

---

I don't understand that I am "anonymous" when replying on my phone. ?????

Answer 5

Debbie,

I have a huge spreadsheet.  It is organized by chromosome and segment. In addition to all of the material from the match I add Wikitree ID if there is one, common ancestors if I have found them.  I also add e-mail address and the date I sent an e-mail. If there is a family file at Ancestry I add the link.  As I use , Gedmatch, FTDNA and MyHeritage, I show which system produced the match and make a point of identifying those in multiple systems.  I make a lot of notes, things like relationships between various kits, for example when I have been able to determine that one kit is for the parent of second kit, I add that note.

I have used spreadsheets for years and am use to using very large ones so this was a logical choice for me.  I think of data as a spreadsheet, it is how I visualize data sets.  If you are very comfortable with a spreadsheet I think it is a great tool.   

In same cases I add color coding for groups.  As I have siblings with DNA tests I have subsections for their matches but those are not very large at this time.

Comments

I use spreadsheets so I agree with your logic. Thanks for the tips. I'll use them!

---

Again, why am I "anonymous" on here when answering from my cell phone???

Answer 6

Debbie, have you tried the various clustering tools that show you who shares with whom, grouping them up so you can work on eg the ones that might lead to your brckwall as you can (usually) eliminate the clusters for the known rellies.

The technique is described in detail at 

https://www.danaleeds.com/dna-color-clustering-the-leeds-method-for-easily-visualizing-matches/

and there are several automated versions, eg at

DNAGedCom / Genetic Family
Blogpost (slightly outdated as this clustering does work with more than Ancestry)
https://blog.kittycooper.com/2018/12/more-automated-dna-match-clustering/
 

Genetic Affairs:

blogposts 
https://dna-explained.com/2018/12/04/autoclustering-by-genetic-affairs/

https://blog.kittycooper.com/2018/12/automatic-clustering-from-genetic-affairs/

This site can also recreate family trees showing suggested connections (https://dna-explained.com/2019/12/02/genetic-affairs-reconstructs-trees-from-genetic-clusters-even-without-your-tree-or-common-ancestors/)

and GEDmatch Tier 1 clustering tool

If you are on MyHeritage their advanced tools include an auto clustering tool which is a good start, but the above are all more flexible.

There is also the network analysis avalible in RootsFinder (which is the site I use most for analyses of this sort, and keeping track of where matches fit).

Most have facebook groups for additional help.

Comments

Yes, I am on MyHeritage. I never really clicked on those tools. Will have to check them out. I haven't heard of Rootsfinder so will look into it. Thanks Lorna!!

---

Once again, my cell phone puts me as "anonymous." I love these links and will look more closely at them!

---

I really like this site!! I have to try it!! Thanks! 

"DNA Color Clustering: The Leeds Method for Easily Visualizing Matches"

---

Debbie,

I suspect that you show as anonymous when you have not logged into the system.  Perhaps you can check that you are logged on when using your phone.

---

Debbie,

I've added a couple more links for the Genetic Affairs version/site, and will look for more on the others

---

Hey, I checked my phone and sure enough, I wasn't logged in. I am now!

---

Thanks Lorna!

Answer 7

I did set up a spreadsheet for each of the DNA tests I monitor for family and distant cousins.  It was a lot of work and really didn't give me any kind information that I can not find out in other ways.  Took me time and I may have updated a few times since, but never refer to it now.  

I do download my matches in a CSV file along with any surnames and places to determine commoninalities I may have missed in my genealogies.  I also make note of contants I have made and make notes about possible leads or connections.  I do not use it a whole lot, just time to time.

Comments

Good to know, but I like some of the links ppl have posted for similar "research." I don't want to get discouraged.

Answer 8

I have a few Google Sheets spreadsheets going on for matches. Each pertains to a particular branch of my family i'm focusing on.

Within the spreadsheet I use a separate tab for each chromosome. Under each tab I use the same column headers:

1. Start point

2. End point

3. cMs

4. SNPs

5. Match 1 Name

6. Match 1 GEDmatch number

7. Match 2 Name

8. Match 2 GEDmatch number

9. Notes (I generally input relationship to MRCA if known or if the match came from MyHeritage rather than GEDmatch)

I color code triangulated matches.

Comments

Wow. I like this idea! Thanks!

---

It has really helped me to organize matches for specific family branches.

I wrote a blog post an out it that you may find helpful.

Answer 9

I imported my chromosome matches from MyHeritage and FamilytreeDNA into a spreadsheet. What I found was ridiculous and probably useless. With 100% Jewish DNA comes a history of endogamy. There were clusters (or pileups) everywhere. Even if I took out matches under 6cMs, there were 55,000 lines from MyHeritage alone. I have a few known relatives in that mess, but nothing that helped me figure out any new relatives.

Comments

I totally sympathize - scratch that - the right word is empathize, even though I'm not as Jewish as you are - I'm "only" 87%.  I was all excited when I tested, looking forward to learning all about how to use DNA to identify relatives … until I found that the whole world are my relatives.  I gave up pretty quickly.

---

I saw your answer and noticed your mention of being ashkenazi. I checked your Gedmatch number and we are distantly related, as almost every one of us is. I gave up slower just because I like numbers and spreadsheets.

---

Dave, my retirement career is as a professional developer of database driven websites - trust me, nobody likes numbers and spreadsheets better than I do.<grin>

I think what I found most discouraging was that I wrote to about the half dozen closest matches and got zero responses.  Then 3 or 4 wrote to me but the matches were all so distant that there was no point, plus all of them only wanted to know how we're related - like I should figure this out when my entire tree is on gedmatch and ftdna (and I just added it to MyHeritage) and none of them has anything about their family there.  They think I have a crystal ball and am omniscient.  I did reply politely, though, letting them know that, at best, our relationship would be too distant for DNA to confirm.

When I posted something here about being disappointed with my foray into DNA, someone suggested that I test with ancestry because I'll get more matches there … that's about the last thing I want, having way too many everywhere else!!!

---

I forgot to mention - a few days ago when I took the offer from MyHeritage to upload and get free use of their tools for life, I did see 3 very close matches, but I can't write to them because you need a subscription in order to do that.

---

I have had similar experiences with unresponsive close matches as we all have. I have a few that actually did respond and worked on their tree to see if I could connect with them that way. They should be second or third cousins (in theory) but I was never able to connect our trees. Unless they are all related through my paternal grandmother who I know precious little about her parents and their families, I am stuck and no amount of DNA matches is going to help that.

---

Dave, I like numbers and spreadsheets too!

---

Dave, is there any chance this is your grandfather (I'm sure you've learned, as I have, that names/dates/places vary a lot in Eastern European Jewish records)?

Schlome Samuel [no surname]
son of Alter KÜCHLER & Chaje Golde SICHER

Birth record
1853

Year of Registration:	1853
Sex:	M
Town of Record:	Lwów
House Number:	84 2/
Comments:	[surname is KICHLER in other records]

This record comes from the Lwów Jewish Births (1805-1872) database, fond 701, series 1, volume 130, page(s) 123, record number 21. The original records are held in Central State Historical Archives of Ukraine in Lviv (TsDIAL) and were added to this search engine on 25 August 2016. An image of this record can also be viewed on FamilySearch/LDS microfilm #2405324, item #2. The Gesher Galicia unique record ID is GG-lviv_births_revised-26098.

---

While I have a great grandfather named Samuel Kochler or Kohler or any other variant (even Kichler) who was born around 1857 (maybe even 1853) I have his father's name as Avrom-Ber Kochler aka Kohler and his mother as Hannah. With all the different name spellings and translations from Russian, it may be possible. Especially if you consider they may have had Jewish names that weren't the same as their everyday use names.

---

For what it's worth, I did not find any Kochler on all of jewishgen.org.  I found lots of variants, including Kichler.  Sorry - I meant your great-grandfather and I saw that the father's name was wrong, but, like you, I'm all too aware of all the spelling differences and errors and language differences and have learned not to rule anything out.  What got my attention when I looked at your grandfather's ancestors is that Grodno is a place I'm working on at the moment for my family, although distant ones from me.  HERE is the one I just got a lot written for, but beware - the LNAB should probably be Kupnik instead of Krupnick.  I'm not going to change it until I'm sure of what's right.

---

Oddly enough Jewishgen has given me absolutely no usable information at all, ever. Sadly (because it's about the Holocaust) Yadvshem has been very useful. I even found and have had contact with a 1c1r that survived the Holocaust because of the site.

---

I do pretty well with jewishgen for Lithuania and Hungary, but not so much for Poland and hardly anything for Ukraine.  Yes, Yad Vashem is invaluable for Holocaust victims, but I have yet to find a survivor there, although I know they do have some.  Gedenkbuch is another good place for Holocaust victims.

---

Gaile,

On Holocaust events certainly the Arolsen Archives that went online earlier this year also are a tremendously valuable resource, with access to original documents.

Answer 10

Yes I do several different ones.  

Here is a link to how I look for family groups on GedMatch

https://1drv.ms/u/s!An4GlsTQbJ_QiOBba5x9moNjUAOeyA?e=q2Ggi8  Please copy and do not move the items.  

For others like my matches at FTDNA I download the matches and add some note columns like if I sent an email and the date

I have just recently tested with Ancestry and have not yet done a spreadsheet for those links but I can see where one might come in handy.   I have coded matches with colors for my different branches and it might be easy to create a spreadsheet by branch with notes.  

I also plan to create one for all the My Heritage emails I get with so called matches.  Many are not good matches and I need to keep the bad ones in one place and the good ones in another but since they are often on the same email I am thinking a spreadsheet will help me do that by person.

Comments

Hey....I remember that grid! Didn't you send me this a long time ago?

---

Yep I sure did

---

I thought so! I think it was the basis for my own grid. =D

---

Looks like we have a new word to replace brickwall … now we can call it gridlock

Answer 11

I really like DNApainter.com and would use the Genome Mate Pro but it does not work on a Macintosh computer only windows.

Answer 12

All these great spreadsheet suggestions are helpful. I used them for years but so much work. Recently, I just did a SUPERCLUSTER spreadsheet via Genetic Affairs. It took me a whole afternoon to take the time and actually do it but the spreadsheet it produced saved me hours of work!

SUPERCLUSTER is when they combine your matches at FTDNA & AncestryDNA.

Read all about it at " DNA Explained" by Roberta >

https://dna-explained.com/2019/12/02/genetic-affairs-reconstructs-trees-from-genetic-clusters-even-without-your-tree-or-common-ancestors/

Comments

Also if you just only have AncestryDNA or just FTDNA accounts, you can produce spreadsheets from them.

Answer 13

Not actually a spreadsheet, but notebooks, hehe. I like writing. I'm trying to find all the triangulations I can. And some companies - I don't know about all of them - offer a full spreadsheet, you can just download it.

Answer 14

there is a method to "extract" Ancestry.com DNA matches  to an Excel that bypasses the 3rd party ban

Comments

Hi, Gary. Can you maybe elaborate just a bit? The raw DNA data from all the major genealogy testing companies comes downloaded in the form of a delimited, plain-text file that can be opened directly in Excel or another spreadsheet app, or even in a text editor (providing the text editor supports up to 750K lines).

Where we really run into problems with Excel is the inherent limit of 1,048,576 rows. That's why there's no good way to use Excel to match identical loci values among multiple kits and then create a merged file of unique SNPs as a "superkit." Gotta use a for-purpose database app for that.

Do you mean something other than the actual DNA raw data?

---

Most of the sites allow you to download your list of matches as a .CSV file, which can then be imported in a excel etc. The information included is different for each site, depending what they show you on their matches page. However the one site that doesn't provide this facility is Ancestry, sadly the biggest number of matches for most people.  There used to be a 3rd party hack to download the data, but they changed their interface to stop it. 
There is now a way, but it involves a lot of mouseclicks and time, mostly just waiting for the thousands of matches to load.
Full instructions can be found at download ancestry matches from Family History Fanatics.

---

i edited my comment - i was referring to extracting match data

btw Excel can deal with data files larger than the maximum number of rows

---

my version uses macros to

1) open the Ancestry Page

2) send ctrl-ends to expand the numbers of results

3) save Ancestry web page with data to local pc

4) use Excel Power Query to extract data from the saved pages

my method captures pieces of information than shows on the web page display - and is not stopped by the Ancestry third party ban

Answer 15

I do all kinds of spreadsheets.    I like to take the closest match and run a precooked who match 1 or 2 kits report on Gedmatch to see all the matches we have in common.  Which are listed in top part of that report.   That helps me see how we all relate

Answer 16

I did the Leeds Method on a spreadsheet as I was trying to find my missing Great Grandfather this way.  I haven't found him yet, but did find it helpful to group matches.