Friday, May 29, 2015

Excel trick

I posted an Excel trick on the Organized Genealogist, Technology for Genealogy and the Excel-ling Genealogists Facebook Group pages.  I was completely overwhelmed with the response.  In less that 24 hours there are over 200 Likes and over 70 comments.

A little background…  I am doing a One-Name Study on the Glaentzer surname.  You can read about it HERE.  I have a distant cousin in Germany that is also working on this surname and we collaborate. She has two trees on Ancestry.com.  I wanted to compare what she has with what I have.  She could send me a gedcom but I don’t want to import a gedcom into my file because it would mean a lot of cleanup work because of the duplicates. Trying to look through her tree is also not a good option because she has a lot more information in these trees besides just the Glaentzers.  Here is what I did.

1) I went to the first tree.  How you get to the right screen depends a bit on whether or not you are using the beta version of Ancestry.  Here is where you want to be.  This screenshot is from my DNA tree.  You want to click LIST ALL PEOPLE from the dropdown menu.



2)  This is what you will see.  This too is from my DNA tree.

 

3) I needed to filter this list to only include Glaentzers and variations so I typed “Gl” in the Last Name field to filter the list.  If you want to put the entire tree in Excel then you will skip this step.  Here is the filtered list, this time from one of Christina’s trees.  This is the actual data I was working with.




4) Notice that the names are hot links to that person’s page in Christina’s tree. I copied this list and then pasted it into Excel.  The links transfer with no problem.  You can see in the above screenshot that there are two pages.  You have to copy and paste each page one at a time.

5) Once you have the data in Excel, you will need to adjust the column widths and the row heights and add column headings.  I also froze the headings. 

6) Now is where it gets fun.  After I pasted all of the names I needed from both of her trees and cleaned up the spreadsheet, I did a custom sort.  I sorted by the Name column first, then by the Birth Date/Location column second.  Now I have the names in the exact order I want.  Here is what it looks like.




7) You can see that the names are still hyperlinks and you can see which ones I have already clicked.  As I check each entry, I can color code them showing which ones I have looked at.  The reason this is important is if I ever decide to sort the list in a different way the ones I have checked won’t necessarily be at the top anymore so this will show me which ones I have done.




This has been a real timesaver for me. 
 

Copyright © 2015 Michele Simmons Lewis

20 comments:

  1. Michele,

    I want to let you know that your blog post is listed in today's Fab Finds post at http://janasgenealogyandfamilyhistory.blogspot.com/2015/06/follow-friday-fab-finds-for-june-5-2015.html

    Have a wonderful weekend!

    ReplyDelete
  2. Michele - your post served as inspiration for me to organize my research on Ancestry. Just finished writing about it here, with due credit to you: http://rootsofkinship.com/2015/08/25/how-to-get-organized-with-researching-my-family-tree/

    ReplyDelete
    Replies
    1. Wow! I wish I knew as much about Excel as you do! I am memorizing your blog post! There are several things I want to do on an Excel spreadsheet I am working on now. It is a VERY long research calendar for my brick wall ancestor. Thank you so much for posting this.

      The only thing, I want to give credit where credit is due. It isn't my Ancestry tree that I did this to but rather the trees of a friend of mine who is also working on my One-Name Study. The only tree I have on Ancestry is a bare-bones, direct line pedigree for DNA purposes (BMD information only on each person).

      Delete
  3. Setup a Data --> Filter
    This makes searching and sorting easy
    You can even search on color

    ReplyDelete
  4. how to i change my ancestry file to allow me to do this?

    ReplyDelete
    Replies
    1. You don't have to do anything to your Ancestry file and you can do this same trick to anyone else's file too as long as their tree is public.

      Delete
  5. I love this. Thank you so much.

    I am not quite Excel literate so I have a couple of questions. The last column of my file has an Icon of a scroll. I'm not sure what that means. Can you explain?

    Also, all the names come in with comma in front of them. I'm not sure why?

    ReplyDelete
  6. I see now that the names are understood by Excel as "Last, First". The comma is there because on my trial sheet the names are not first and last. They are i.e. Charles the Bald. Still don't know what the scroll icon is, though.

    ReplyDelete
    Replies
    1. When you export from Legacy (if you are using Legacy) you can choose to have the names exported in a single field or break up the first and last in separate fields. You can also export prefix and suffix fields.

      As far as the scroll icon, I am not sure. I've not seen that on anything I have imported. Maybe one of the other readers will know.

      Delete
  7. I have several different trees that I am going to combine into one tree, so I added one extra column which shows which of the original trees the info is on. If I run into issues with names that are too similar to easily tell apart I might add a parents column and/or a spouse column.

    ReplyDelete
  8. Excellent posting. Thank You for working outside the box.
    Jose from Clarkston, Michigan

    ReplyDelete
  9. Thank you so much for this. However, my names aren't linked after I copy to Excel. I am using a MAC. Could this be the problem?

    ReplyDelete
  10. I know absolutely nothing about Macs :( Maybe a Mac user will know what the problem is.

    ReplyDelete
  11. You might think about using Wikitree - one tree, no duplicates, lots of one name studies and easy to collaborate. I love it - it's free in the cloud and I like how it helps with DNA matching too.

    ReplyDelete
  12. I've been doing something similar, but your technique is so much better than mine. Thanks.

    ReplyDelete
  13. It doesn't look like you have used the filter option on your columns. THis is a great feature when you have information in many columns.

    ReplyDelete