Using GEDmatch Part 4 (Alternate Way To Find People Who Match One Or Both Of 2 Kits)

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.

Google Sheets Screenshot 1

Clicking on the plus sign (+) will bring up an empty spreadsheet page.

Google Sheets Screenshot 2

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.

Google Sheets Screenshot 3

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).

Google Sheets Screenshot 4

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.

Google Sheets Screenshot 5

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).

Google Sheets Screenshot 7

Scroll to the very bottom of your spreadsheet, select the bottom two rows, and delete them.

Google Sheets Screenshot 7

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.)

Google Sheets Screenshot 8

Delete columns B – S.

Google Sheets Screenshot 9

This will leave you with just the kit numbers from the ‘One-to-many’ matches comparison. Go to File > New > Spreadsheet.

Google Sheets Screenshot 10

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.

Google Sheets Screenshot 11

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).

Google Sheets Screenshot 12

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):

=IF((COUNTIF(C:C,A1)>0),A1,””)

(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.

Google Sheets Screenshot 13 (revised)

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.)

Google Sheets Screenshot 14

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.)

Google Sheets Screenshot 15

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.

Google Sheets Screenshot 16

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.

Google Sheets Screenshot 15

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.

Google Sheets Screenshot 18

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.

Google Sheets Screenshot 19

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

Advertisements

5 thoughts on “Using GEDmatch Part 4 (Alternate Way To Find People Who Match One Or Both Of 2 Kits)

  1. Pingback: Introduction To Using GEDmatch Part 3 (The People Who Match One Or Both Of 2 Kits Tool) | Adventures In Genealogy Research: No Stone Unturned/The Wright Stuff

  2. I am having a problem with the formula. Receive an error message “Formula Parse Error”. Tried typing it in and copy and paste and the same error message appears. On a Mac using 10.8.5 using the google spreadsheets.

    • Hi Roberta,

      I greatly appreciate you alerting me to the error you are experiencing. After trying a few things, I was able to duplicate the error, and I now think I know what is causing it. I have just emailed you a revision to my initial instructions posted above. Please try this revision, and then let me know whether or not it works for you, so I can either revise my instructions posted above or troubleshoot this problem with you further.

      Thank you very much,

      Dan

  3. Roberta,

    Thanks for trying my corrections to the formula, and for letting me know that the most recent revision is now working for you. I’ve updated the instructions in the post above to properly reflect the updated formula. I appreciate all your help in troubleshooting this issue.

    Best regards,

    Dan

  4. Pingback: Introduction To Using GEDmatch Part 5 (Alternate To Triangulation Tool) | Adventures In Genealogy Research: No Stone Unturned/The Wright Stuff

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s