|Ancestry DNA Match Table Maker|
|Google Spreadsheet programming by Greg Clarke|
|last updated: 29 March 2023|
|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|
|Open up your Ancestry DNA Matches to show the matches you wish to record|
|Note that sorting by Relationship (default view) seems to work best, compared to Sorting by more recent.|
|Note2: You may wish to scroll down the Ancestry DNA Matches page to ensure you get all the matches you want - by default, Ancestry only loads the first 50 - 100 or so matches, but continues to add more as you scroll downwards.|
|Copy the text of all the matches you wish to record in your table|
|* 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|
|* Alternative option: If you just want a subset, then with your mouse, highlight the matches you want, then do CTRL + C (CMD + C)|
|* Suggestion from user: Another 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|
|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.
If this method does not work for you, try using a different browser to load up your Ancestry DNA matches.
|* Switch to the appropriate tab marked Worksheet (see tabs at bottom of this spreadsheet, and the note below)|
NOTE: In June 2021, Ancestry added a new feature - a "Recognize" button, two new worksheet options have been added that will work with this new format.
|* and single click Cell A6 (highlighted in yellow) to place your cursor there|
|* and PASTE the copied text into that cell.|
|(IF you double-click into the cell, 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)
|NOTE: You will get different results depending on how you paste the data.|
|* if you use CTRL + V (or CMD + V) to paste directly - this will include the formatted links in the resulting table|
|* If you use SHIFT + CTRL + V ( or SHIFT + CMD + V) - this will paste the VALUES only, with no hyperlinks, but the formulas will work more completely, including Notes|
|* Alternatively you can use the menu item: EDIT - PASTE SPECIAL - PASTE VALUES ONLY|
|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.|
|* 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|
|* Copy the new table of information (columns T to AE, or Q to AB, etc., depending on your worksheet) and paste it into your own Google Spreadsheet or Excel table|
|* 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|
|IMPORTANT NOTE about different versions of Worksheet tabs:|
|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 !|
|IF your Ancestry DNA Matches page has a DO YOU RECOGNIZE THEM button, |
then use Newest w Recognize btn and the SHIFT + CTRL + V key combo
or use Newest - Recognize btn with LIVE Links and the CTRL + V key combo
|IF your Ancestry DNA Matches page has no button at the end of each row, then use the tab labelled Worksheet : No btn|
|IF your Ancestry DNA Matches page has a VIEW MATCH button at the end of each row, and the shared DNA is listed with % first, then cM, then use the tab labelled Worksheet : View Match btn|
|IF your Ancestry DNA Matches page is the NEWEST version which has a VIEW MATCH button at the end of each row, AND the shared DNA is listed with cM first, THEN % shared DNA, custom group coloured dots to far right, then use the tab labelled Worksheet : NEW View Match btn|
|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.|
|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.|
|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:|
|* Go to the EXTENSIONS menu --> MACRO sub-menu, then choose CLEAR DATA (Macro is listed at the bottom of the popup menu)|
|* Alternatively, you can always just clear the data in column A by highlighting and hitting the DELETE key ...|
|NOTE about Notes|
|The Notes column, especially in the No btn version of the worksheet, does not always properly transfer correctly. |
Because of the formatting in the No btn version, with the Notes being an optional field, and thus missing some of the time, they sometimes get placed in the first position of the next row - or - vice versa. In this case, the only real solution is to manually review the Notes column after you copy the table into a new spreadsheet where you can manipulate the data directly.
FINAL NOTE in the FINAL ROW - the formulas often MISS this final note - you may have to enter that one manually.
|NOTE about Lots of Matches|
|If you have more than 1400 matches at a time, you will have to extend the formulas past row 1400. 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.|
The two newest worksheets, that work with the Recognize button, come with 4000 rows of formulas. Adding more rows of formulas may slow down the spreadsheet.
|NOTE about Empty Groups column|
|In the No Btn worksheet, and now the NEW View Match btn worksheet, 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.
Note that in the View Match btn version of the worksheet, the information scooped is only the # of groups, and not the actual groups.