ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
Ancestry DNA Match Table Maker
2
Shared with DNA Skills Workshop course participants
3
Google Spreadsheet programming by Greg Clarke
4
uptergroveGreg@gmail.com
5
version 1.14.Skills
6
last updated: 18 June 2024
7
How To Video on YouTube
8
STEP 0:
9
If you just have VIEW ONLY rights to this spreadsheet, then use FILE --> MAKE A COPY, and store it in your own Google Drive account
10
11
Use the Google Chrome browser to view
AncestyDNA Matches and this Google Spreadsheet.
12
STEP 1:
13
Open up your Ancestry DNA Matches to show the matches you wish to record in the table.
14
Note that sorting by Relationship (default view) seems to work best, compared to Sorting by more recent.
15
16
STEP 2: Copy all text
17
Copy the text of all the matches you wish to record
18
* Easy option: Press CTRL + A (or CMD + A on a Mac) to SELECT ALL, then do CTRL + C (or CMD + C) to COPY the text to the clipboard
19
20
* Suggestion from user: One way to get a subset, is to use the Filters at the top of the page to show just some of your matches, then use the SELECT ALL and COPY commands
21
NOTE: Some browsers do NOT support the CTRL + A and CTRL + C shortcuts to SELECT ALL and COPY.

This DOES work with Google Chrome, but support in others is apparently limited.
Please use GOOGLE CHROME.
22
23
STEP 3: Switch to Raw Data tab / Paste
24
- IF - you have the most recent update, released in June 2024 (with square "dots" with character inside)
* Switch to the tab marked 2024 Raw Data (see tabs at bottom of this spreadsheet, and the note below)

- ELSE -
* Switch to the tab marked OLD Raw Data (see tabs at bottom of this spreadsheet, and the note below)
(The older version had a "Do you recognize them?" section with a YES / LEARN MORE button combo)

ALSO:
The 2024 version serves 20 or 50 matches per page,
the OLD version had endless scrolling and could show infinite matches at a time

If you are being served up a DIFFERENT version of the DNA Matches page , then email Greg for a previous version of the spreadsheet to use.
25
* single click Cell A7 (the one below the cell highlighted in yellow) to place your cursor there
26
* and PASTE the copied text into that cell.
27
(WARNING: IF you double-click into the cell by mistake, all the data will fill up that one cell.
You want to single-click so the data starts at the cell and fills all the way down column A)
28
29
* Note: if you use CTRL + V (or CMD + V) to paste directly - this will include the formatted links in the resulting table
30
31
* Alternatively you can use the menu item: EDIT - PASTE
32
33
NOTE 2: If you re-use the Worksheet multiple times, you should clear the data in Column A from A6 downwards before each copy & paste.
34
* Otherwise, the data may include some of the rows from the previous matches - see PRO TIP down below for a quick way to do this
35
36
STEP 3b: ** NEW for 2024 version ** - Support for custom Dot Groups
37
* Edit cell AL1 (column AL, row 1, very top) to add the single characters for each of your custom groups.
* Separate each character with a comma
38
* This will automatically populate the column titles for the columns to the right of AL, one column per single character.
39

* The Notes and the Groups are automatically placed in columns AK and AL for each row.
* If there is NO NOTE added for a DNA Match, the Custom Group single character will be displayed in the Notes column,
(by default, it slides over to the left ...)
* IF that happens, click the CHECKBOX immediately to the right of it to slide that text into the Groups column (as appropriate)
40
41
STEP 4: Copy new table and save
42
* Copy the new table of information and paste it into your own Google Spreadsheet or Excel table
(columns Z and to the right, for the 2024 Raw Data tab, or columns T to AE for the OLD version)
43
* Since this may be a shared Google spreadsheet, you'll want to copy your data once you've got it formatted into a private spreadsheet so you won't lose it
44
45
Note: A quick way to do this copy to a new spreadsheet is to click the arrow next to the words "Raw Data" in the tab at the bottom, and choose Copy to -> New Spreadsheet
46
* Since this may be a shared Google spreadsheet, you'll want to copy your data once you've got it formatted into a private spreadsheet so you won't lose it
47
48
Note: A quick way to do this copy to a new spreadsheet is to click the arrow next to the words "Raw Data" in the tab at the bottom, and choose Copy to -> New Spreadsheet
49
IMPORTANT NOTE about different versions of Worksheet tabs:
50
Ancestry DNA Matches now come in FOUR flavours (and counting ...), and the layout of YOUR matches page will determine which Worksheet will "work" for you !

I have provided the most recent version here, but, if Ancestry in your country serves you an older version, and this version doesn't work for you, email me, and I'll give you an older version that will work.
51
52
IF your Ancestry DNA Matches page has a DO YOU RECOGNIZE THEM button,
then the Raw Data worksheet will work for you
53
54
55
56
PRO TIP:
57
I have created a Macro that will automatically CLEAR the PREVIOUS DATA, which you might want to run BEFORE or AFTER you are done using this spreadsheet and attached it to a button that is prominently labelled.
58
NOTE: To run this Macro Script, you will be asked for your permission to do so (this is a precaution that Google Sheets puts in place, to ensure you are aware of another mini-program being run on your data). If you trust the script (it just clears the data from cell A6 in the worksheet - nothing else), then say YES. If you don't say Yes, then the button won't work. To give permission to the run the script, you will need to login to your Google Account.
59
60
NOTE 2: Sometimes (for no reason I can see), the button seems to "disappear" from view. If this happens, you can still invoke the ClearData script, by doing the following:
61
* Go to the TOOLS menu --> MACRO sub-menu, then choose CLEAR DATA (Macro is listed at the bottom of the popup menu)
62
* Alternatively, you can always just clear the data in column A by highlighting and hitting the DELETE key ...
63
64
65
NOTE about Lots of Matches
66
If you have more than 4000 matches at a time, you will have to extend the formulas past row 4000. To do this, highlignt the bottom row with the formulas, then drag the right corner of the selection downwards for as many rows as you need.

NOTE: Adding more rows of formulas may slow down the spreadsheet!
67
68
NOTE about Empty Groups column
69
The Groups column will be empty. This is because the Group information is represented by the coloured dot system, and those dots are images, and not text that can be scooped up in a copy and paste action.

I have left the Groups column in the worksheet in case you wish to fill this information in manually after you've transferred the table to your own spreadsheet. If you don't wish this,you can always delete the column.
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100