Good Day Everyone,
I wanted to present a simple way use Microsoft Excel to compare and find shared matches between two or people. The initial steps first requires you to have Excel 2007, 2010 on your Windows machine. Then you need to go on 23andMe to the Countries of Ancestry Page and grab any two persons Ancestry Finder csv files.
Here is how you get to the Ancestry Finder csv file for single person.
a) Login to 23andMe account
b) Then at top - My Results -> Ancestry Tools -> Countries Of Ancestry
c) On the Countries Of Ancestry Page - click drop down window for each person. Pull down web page and on bottom - double click the blue button that says - "Download.........Ancestry Finder File"
d) save csv file to your computer
Here is how you create the spreadsheet
a) double click each csv file for each user. Excel opens up.
b) then copy the column that says matches for a particular person into another spreadsheet. Do the same with another person. The result should be a single spreadsheet with a minimum of two columns that you are comparing.
Here is how to run VBA code to compare columns
1) Open up new excel spreadsheet with names of matches of two or more people.
2) In the new excel spreadsheet - hit ATL and F11 key. This opens the visual basics editor to run code.
3) In the visual basic editor - on the toolbar - look for small green arrow pointing to the right. Looks like a small green triangle. click this green triangle
4) This opens a small window. Give your script a name and click create button.
5) erase code in the window and replace with this code:
Private Sub CommandButton1_Click()
Dim CompareRange As Variant, To_Be_Compared As Variant, x As Variant, y As Variant
str1 = InputBox("Enter Column Name to be Compared")
str2 = InputBox("Enter Column Name to Compare")
str3 = InputBox("Enter Column Name to put the Result")
Range(str1 & "1").Select
Selection.End(xlDown).Select
Set To_Be_Compared = Range(str1 & "1:" & Selection.Address)
Range(str2 & "1").Select
Selection.End(xlDown).Select
Set CompareRange = Range(str2 & "1:" & Selection.Address)
i = 1
To_Be_Compared.Select
For Each x In Selection
For Each y In CompareRange
If x = y Then
Range(str3 & i).Value = x
i = i + 1
End If
Next y
Next x
End Sub
6) Press the small green triangle button again. this runs the code and you will be prompted to enter the row letters that you want to compare and what column to place the results in
7) The result is your spreadsheet will have a new column with shared matches.
Here is the URL with the instructions starting at line that says: "Find duplicate values in two columns with VBA code"
Thanks
Steve
