Copying your ancestors to a spreadsheet

[Bob Spiers]

Can one of you spreadsheet experts tell me how to copy my Ancestry DNA matches (not all of them, just the ones on the page) to a spreadsheet in such a way that I can sort them by user name?

I presume you mean as displayed in Ancestry format...Icon/User Name/cousin relationship/Tree information/view match ...in which case I would think copying as such would not allow for isolating the user name to allow an Excel Custom Sort. (But could be wrong of course).

If you do not mind a little more work you can achieve a CHEAT form of searching within a Spreadsheet by using the 'web capture' facility in Ancestry. (Make sure you have an Excel blank page open with at least one column wide enough to paste the captured item). Then FOR EACH item you wish to transfer to the spreadsheet, do the following:

In Ancestry: right click to the left of the selected outline box and from the list select 'web capture'. Then 'Free Select' (which will require you to drag the mouse to 'white out' the item) then 'Copy'.
In Excel: Select 'Paste'. (You can use the tried and trusted Ctrl C and Ctrl V if you prefer). The single item will now show in Excel as it does in Ancestry.

NOW DO THE SAME FOR EACH ITEM YOU WISH TO TRANSFER TO THE SPREADSHEET. YOU CAN CAN CHANGE THE ORDER AT WHICH THEY APPEAR IN THE SPREADSHEET EACH TIME IF YOU WISH, OR JUST LAYER THEN AS IN ANCESTRY, AND SORT WHEN YOU HAVE TRANSFERRED ALL THE ITEMS.

To MANUALLY REPOSITION left click in a blank area and holding the mouse button, move to its chosen USER NAME position.

Simples!

(Mind you it may not be what you seek, but it is the only way I can think of for allowing Ancestry DNA matches to be sorted, unless Phil or he/she of similar genius, (meant sincerely) can come up with a better solution)

 

[Tim]

Web Capture? Is that an Edge only feature?

 

[peter]

My brain hurts.....

 

[canadianbeth]

Mine too. I did the right click and did not see "web capture"

 

[At home in NZ]

My brain hurts.....

So would mine, I bet you're sorry you asked.

 

[Bob Spiers]

Web Capture? Is that an Edge only feature?

I admit to using Edge on most of my devices, and yes, on the one I used to check out my Ancestry DNA. I use Opera on two, and have Chrome in the reserve on another. It is certainly a feature of Edge and just before this post checked in Opera, and the 'web capture' DOES NOT show. I have yet to check Chrome, but given the responses of a 'no show' by others; then by deduction it must be a feature of Edge. (Thereby boosting my claim in the recent past that Edge is the superior browser and not just in this one instance).

Sorry about that folks and it makes a change to hear Peter and others saying their brain hurts, which is my usual reaction to a post by you know who. Why not use Edge (it is there in the wings for all) and discover one of its many useful features?

 

[Bob Spiers]

Tried to show the 'web capture' as it appears in Edge, but as it only appears with a right click and disappears the moment I try to use the snipping tool, I decided to photo it and show it that way. So here it is:

and before you try to use the Ctrl+Shift+S other than in Edge, sorry that will not work either.

 

[Bob Spiers]

Not nice and not pretty but it does work.

Just followed through with your own solution Tim, and yes it does work as you describe and does allow a more proprietary Excel filter sort, providing you can endure the effort to copy each item manually. But no reward without effort hey?

 

[Bob Spiers]

For those who could not fathom what I meant in using 'web capture' here is a simple 3 line example. Above an exact copy as it appeared in Ancestry DNA Matches (ABC added by me). Below, when manually 'moved' (BAC) to correspond with the User's first name. It could, of course be moved to correspond with surname or any other criteria to suit.


Of course to provide an example showing both before and after events, I actualy copied and pasted, rather than simply moving.

 

[Alexander Bisset]

I don't get the option 'Match destination formatting' when pasting to blank spreadsheet.
.

It only shows you that text in the tooltip so you just need to know the icon. This is the menu you get when you right click on an Excel cell having copied data from a website - eg: My Ancestors page.

 

[peter]

Thanks, Alexander - something I can understand at last!

 

[Tim]

My brain hurts.....

Hi Peter, I found this and it does exactly what you want.

Download Ancestry DNA There is a video here that explains it all and it looks like it works. It's an automated way of doing what I did manually. Let me know it it works for you.

 

[Bob Spiers]

Hi Peter, I found this and it does exactly what you want.

Download Ancestry DNA There is a video here that explains it all and it looks like it works. It's an automated way of doing what I did manually. Let me know it it works for you.

Looks interesting Tim and I too will check it out when I have time. Not sure about introducing a Google Spreadsheet into my own Office orientated setup, but looks as though the programming and macros are tied to Google. I could only take a minute of the Video mind, to American brash (and a little more diluted Canadian) for me but seems to be well explained and I take my hat off to the Canadian who masterminded it all.

 

[Tim]

I haven't tried it, but he did demonstrate a way to save it as an .xlsx file.

 

[Bob Spiers]

haven't tried it, but he did demonstrate a way to save it as an .xlsx file.

Had a quick try out and transferring to an Excel spreadsheet by simple Copy/Paste is covered in the instructions and works fine as a copy of the DNA sheet. You need to revert back to the Google Spreadsheet of course for additional actions, but can save those again in in a different tab. Still work in progress at the moment until I get the hang of things.