My friend and Genea-Blogger Dear MYRTLE had an interesting Blog recently.
In that blog entry, she had this question:
We are new to Genealogy and have Ancesrtry.com and Family Tree Maker.
This is a question that I am sure must have been answered a million times but we have not been able to find a solution doing Google searches. We would like to use Excel to help sort out what we know and don’t know from the tree that we have built so far.
Is there any software that will download an existing gedcom file onto an Excel spreadsheet?
I thought, that is exactly what I am doing for this project. So, why not share it here.
Since I know very little about David Ridgely Howard, I wanted to see if the Census Records could help me build what his family might look like, using Census Records, since other records were not helping me identify his parents. The census would help build households that my subject was in in various census years. Hopefully, one of them would give clues to his parents. As census years progress, relationships could be established within the Household. So, I wanted to build an EXCEL file, as was indicated in Dr. Tom Jones’ discussion on Inferential Genealogy.
Since I use Family Tree Maker, Version 2011, and the question was the same, here is how I created my EXCEL file. Please keep in mind that I didn’t start out with this file, but I had data in my genealogy program file.
The key for me was to use a feature of Family Tree Maker and that is for the program to assign, with some user control, a PersonID. I did that, and each PersonID will begin with DRH (David Ridgely Howard), there is a reason for me to do that, which is not important (yet). So that I can see the PersonID in my file AND in EXCEL.
In Family Tree Maker I went to the Publish Workspace, Person Collection, and selected Custom Report.
This will allow me to select the information that I want in EXCEL.
When I brought up the Custom Report the first ICON when that screen opens is Items to Include. Clicking on that ICON will bring up this screen.
I included the Name, PersonID, Birth, Marriage, and Death FACTS. I wanted the EXCEL file to have the Name format to be Last Name, First Name, Middle Name. As that will be the first column in EXCEL.
Selecting name, in the above screen, then Name Options, I select Last, First, Middle to get the output of the Name in the format I want.
Here is what that report looks like in Family Tree Maker. My subject is in the red box. All I have at this point is his Name, the PersonID that Family Tree Maker generated for me, Birth and Death Dates.
That’s a good start and will help generate an EXCEL file so that I can track the Census Records. I select Include All in the Right Hand Panel in the Publish Workspace for the Custom Report. (won’t show all of the Family Tree Maker screen, as that can be a topic of my other blog)
With that report on my display, I click on Share, and select Export to CSV, a format that EXCEL can read and understand.
I want the data to appear in Columns, That is one row per person. So the first button, is selected.
Once exported, you are given the option to Open the file. Here it is in EXCEL. You can see David listed. So, my data is now in EXCEL to do what I want. Maybe not in the format that I want, but the main pieces of information is there.
I made some changes in EXCEL, like moving the PersonID column from the 2nd column to the first column. Normal Cut / Paste features in EXCEL were used. I did, but really didn’t have to, split up the Name from one column to three. Over time, I knew that the exact wording of the names would change over time.
For David, he is DRH001, I changed DRH1 to DRH001, because of the way EXCEL sorts. I wanted the FACTS to be in a column followed by the Date of that Event (or Fact), followed by the State, Place that it occurred and a description as provided by Family Tree Maker.
All of this was a little extra work, but the Sorting in EXCEL and Filtering that you can do with EXCEL it was worth the extra time to reformat what Family Tree Maker had provided.
I knew the Birth and Death Dates for David, so I entered those Facts with Dates. In Family Tree Maker when a Census Record is used, the Residence FACT is used to record where the person was in the Census. Above you can see that I have gathered the 1850, 1860, 1870, 1880, 1900, and 1910 Census, but haven’t found (yet) the 1920 Census. He died in 1927.
I added the Residence FACT dates If I hadn’t found them yet, so in looking at this spreadsheet I can tell I need to look for that.
Using the Sorting and Filtering capability of EXCEL I can see households over time. Who is in the household, who is not in the household, then later the Relationships.
I won’t post more about that, as what is really an EXCEL lesson or two, and that isn’t the purpose of this Blog.
I wanted to track females, over time, in Census Records. The first thing I did was to put a background color for them.
In this example, I do not know her birth Surname. For unknown names, I enter 5 underscores “_____”. I know her birth and death year AND when she got married. So, for the Census years before she was married, her surname has those 5 underscores. There are ways to find that information, but for this spreadsheet I wanted to be reminded visually that I need to look. Since she married a Howard, and I found her in the 1880 Census, just after her marriage, I changed her unknown surname to Howard. I know here death date, so I also have a visible reminder that I need to find her in those census records.
I thank Dear MYRLE for posting the question and I hope I have provided an answer to the question asked. I know that this spreadsheet, generated by Family Tree Maker has really helped me with this project.