The previous article in this series is Introduction To Using GEDmatch Part 3 (The People Who Match One Or Both Of 2 Kits Tool).
As I mentioned in the previous post in this series, for some unknown reason, there are times when the GEDmatch People who match one or both of 2 kits tool does not properly report the matches between the two kits. In this post, I will show you how to conduct a check for common matches between two kits using a spreadsheet. This allows you to double check the GEDmatch tool, and has the advantage of providing you with the ability to save your results and/or check for common matches among more than two kits.
A spreadsheet program, such as Microsoft Excel, is needed for the comparison method I will be demonstrating in this post. While a spreadsheet program works perfectly well, I will be using Google Sheets in my examples, since it is readily available online for free to anyone with a Google account.
Click here if you’d like to open Google Sheets in a new window/tab to make it easier to follow along with these steps.
After going to Google Sheets and signing in (if you are not already signed in), you will see the following screen. Click on the plus sign (+) in the large green circle at the lower right.
Clicking on the plus sign (+) will bring up an empty spreadsheet page.
Leave this window/tab open in your browser and open a new window/tab to do your GEDmatch comparisons (Click here to automatically open GEDmatch in a new window/tab).
Run a ‘One-to-many’ matches comparison at GEDmatch using one of the kit numbers you’d like to check for common matches. If you need help with how to do a ‘One-to-many’ matches comparison, please refer to my previous article Introduction To Using GEDmatch Part 2 (The One-To-Many Comparison Tool).
While viewing the results screen, select everything on the page (Windows users hold down the Control key and the A key on your keyboard, Mac users hold down the Command ⌘ key and the A key on your keyboard). Copy the data (Windows users hold down the Control key and the C key on your keyboard, Mac users hold down the Command ⌘ key and the C key on your keyboard), and then switch back to your Google Sheets spreadsheet.
In your Google Sheets spreadsheet, paste in the data you just copied (Windows users hold down the Control key and the V key on your keyboard, Mac users hold down the Command ⌘ key and the V key on your keyboard).
Click in the upper lefthand cell (A1), hold down your left mouse button, move down to the cell immediately above the first kit number in column A (usually this is cell A39) while keeping the left mouse button held down to select those rows, and then release the mouse button.
Go to Edit in the menu bar, left click, and then select Delete rows 1 – 39 (or whichever row number was the last one you selected).
Scroll to the very bottom of your spreadsheet, select the bottom two rows, and delete them.
Scroll back to the top of your spreadsheet, click in the gray B box right above row 1, hold down your left mouse button, move to the right to the gray S box immediately above the first row while keeping the left mouse button held down to select those columns, and then release the mouse button. (Note: as you move to the right hand edge of your screen, the spreadsheet will begin to scroll, allowing you to continue moving right to the gray S box.)
Delete columns B – S.
This will leave you with just the kit numbers from the ‘One-to-many’ matches comparison. Go to File > New > Spreadsheet.
Repeat the process above, using the other kit number you are checking for common matches with, so that you have a column of just the kit numbers from this ‘One-to-many’ matches comparison. Click in the gray A box right above row 1 to select the entire column of kit numbers.
Copy the data (Windows users hold down the Control key and the C key on your keyboard, Mac users hold down the Command ⌘ key and the C key on your keyboard), and then switch back to your first Google Sheets spreadsheet. Click in cell C1, and paste in the data you just copied (Windows users hold down the Control key and the V key on your keyboard, Mac users hold down the Command ⌘ key and the V key on your keyboard).
Click in cell B1 and type the following formula into the white box next to where is says fx (outlined by the red box in the screenshot below):
(Note: instead of typing out the formula, you can alternately highlight the formula above, and then copy and paste it into the white box next to where is says fx.)
After you finish typing, or cutting and pasting, hit the Return key on your keyboard.
We have just entered a formula into cell B1 which searches all of column C for the kit number in cell A1 and displays the kit number in cell B1 if it is found in column C, or leaves cell B1 blank if it is not found in Column C. (Note: in this example, kit number A776948, in cell A1, is found in cell C15, so the kit number is displayed in cell B1.)
To copy the formula to all the rest of the cells in column B, click in the gray B box right above row 1 to select all of column B. After all of column B is selected, hold down the Control key on your keyboard and then press the D key on your keyboard (Mac users hold down the Command ⌘ key on your keyboard and then press the D key on your keyboard). (Note: if you are using Microsoft Excel to do this exercise, instead of Google Sheets, the command to copy the formula to all the rest of the cells in column B is Edit > Fill Down.)
We now have a list in column B of the kit numbers that are shared in common between the two ‘One-to-many’ comparisons we did. In the examples above, I setup the first column so the matching kit numbers would be grouped together in the first four rows to make it easier to see the results of each step. In reality, your results list in column B will almost certainly not be grouped together like this. Instead, the results will be scattered throughout the list in column B.
To get all of the results to appear at the top of the list, go to Data in the menu bar and then to Sort sheet by column B, Z → A.
These results can then be saved, if you wish, by going to File in the menu bar and then to Download as. While the choice of Microsoft Excel (.xlsx) will save a file which will work just fine with recent versions of Microsoft Excel, the choice of Comma-separated values (.csv, current sheet) will save a file that will be compatible with a much wider variety of spreadsheet programs, including Microsoft Excel.
If you would like to take the results and then compare them against another kit number for common matches among the three tests, select the kit numbers in column A that have kit numbers appearing in column B.
Cut and paste these kit numbers into column A in a new spreadsheet, and then follow the procedure we used above to add the kit numbers of another ‘One-to-many’ comparison into column C. Repeat the process of adding the formula to all the cells in column B and sorting the column, and you will then have a results list of all the kit numbers shared by all three ‘One-to-many’ comparisons. This same process can be used to continue looking for common matches between as many tests as you would like, although in my own genetic family history research I have yet to feel a need to run a comparison of more than three tests.
I hope you find this exercise to be helpful, and that it will assist you with finding more matches to check for common segments. If you get stuck or experience any problems while following the steps above, or notice that a screen has changed significantly from what I’ve shown in the screenshots, please let me know by commenting on this thread or sending me an email. Thanks for reading.
The next article in this series is Introduction To Using GEDmatch Part 5 (Alternate To Triangulation Tool).
This article last updated 19 Oct 2014