Creating an Ancestor Spreadsheet with ChatGPT


Have you followed Jim Bartlett's work with DNA? I have to admit that I haven't paid much attention to his methods. However, after reading his recent blog post, "Don't Be a Small Segment Scaredy Cat!", I was intrigued by his approach to documenting DNA matches and their common ancestors.

That post prompted me to investigate his methods further.

Learning About Segmentology

I located Jim Bartlett's book, Segmentology Fundamentals, on the ISOGG website and downloaded a copy. Rather than starting at the beginning, I turned to the section on tools. Chapter 6 explains how to create an Ancestor Spreadsheet, which seemed like a logical place to begin.

The idea of organizing my ancestors in a spreadsheet appealed to me. Not only could it provide a foundation for documenting DNA matches, but it might also become a useful tool for analyzing my family history research.

Of course, the first step was getting my ancestors into a spreadsheet!

Starting with RootsMagic and Ancestry

I knew that I could download a spreadsheet containing information about my ancestors from Ancestry. However, that spreadsheet did not include Ahnentafel numbers.

For those unfamiliar with Ahnentafel numbering, it is a system that assigns a unique number to each ancestor in a pedigree. The starting person is number 1, their father is number 2, and their mother is number 3. Each person's father has a number twice their own, while their mother has a number that is twice their own plus one.

Thus, except for the starting person, even numbers identify males and odd numbers identify females.

Since I use RootsMagic 12 to maintain my genealogy database, I created an Ahnentafel report. This report included the numbers I needed, along with names, birth information, marriage information, and death information.

I could have manually entered the Ahnentafel numbers into the spreadsheet downloaded from Ancestry. I might even have tried using AI to match the names and add those numbers.

Instead, I decided to take a different approach: Why not ask ChatGPT to turn the RootsMagic report into a spreadsheet?

Turning an Ahnentafel Report into a Spreadsheet

I uploaded the PDF version of my RootsMagic Ahnentafel report to ChatGPT and entered the following prompt:

The attached is a RootsMagic Ahnentafel report. For each person, there is an ahnentafel number followed by their name with the last part of the name being the surname. That is followed by the birth date, birth place, marriage date, marriage place, death date and death place. Can you convert this information into a spreadsheet separating the different parts into columns?

ChatGPT created an Excel spreadsheet containing 1,064 individuals from my Ahnentafel report!

The spreadsheet included columns for the Ahnentafel number, generation, RootsMagic RIN, given names, surname, full name, birth date and place, marriage date and place, and death date and place.

Having the RootsMagic RIN (Record Identification Number) in the spreadsheet was an unexpected bonus. That number will make it much easier to locate an individual in my RootsMagic database.

However, the spreadsheet wasn't quite finished.

Adding Columns for Years

Bartlett's instructions suggested including separate columns for the birth, marriage, and death years in addition to the complete dates.

Having those separate year columns would make it much easier to sort individuals chronologically or filter for ancestors living during a particular time period.

Rather than manually extracting the years from each date, I returned to ChatGPT with another prompt:

Can you expand the spreadsheet by adding columns for birth year, marriage year, and death year?

ChatGPT added those three columns while preserving the existing information.

That was easy!

Separating the Place Information

The next step was to organize the place information.

Bartlett's guidelines suggested having separate columns for county, state, and country, in addition to the complete place name.

Most of the places in my RootsMagic database follow a format similar to:

Town, County, State, Country

For example:

Dodge City, Ford, Kansas, United States

Having the individual geographic components in separate columns would make it possible to sort or filter ancestors by county, state, or country.

Once again, I asked ChatGPT to do the work:

The place names are in a format similar to town, county, state, country. For the birth place can you add columns for birth county and birth state and birth country. For the marriage place can you add columns for the marriage county and marriage state and marriage country. For the death place, can you add columns for the death county, death state and death country. If a U.S. state is given in the place with no country, assume United States as the country.

ChatGPT expanded the spreadsheet with nine additional columns!

Of course, not all of the places in my database follow a consistent format. Some include historical place names, while others are missing a county or country. Thus, I will need to review the geographic information for accuracy.

Still, having the bulk of the work completed automatically is a tremendous time-saver.

Adding a Column for Sex

As I continued working with the spreadsheet, I realized that I also wanted a column identifying each person's sex.

Since the Ahnentafel numbering system already provides that information, this was another task that ChatGPT could handle.

My prompt was simple:

Everyone in the spreadsheet with an odd ahnentafel number is a female. Everyone with an even Ahnentafel number is a male. Can you add a column for sex after the RootsMagic RIN column and put an F or M in that field based on the ahnentafel number?

ChatGPT added the column and populated it using the Ahnentafel numbers.

I now had an even more useful spreadsheet without having to enter that information manually.

Working with the Spreadsheet in Excel

Once I downloaded the spreadsheet, I started rearranging columns to better match the layout suggested by Bartlett.

That is when I encountered a minor problem!

ChatGPT had created the spreadsheet using an Excel Table. When I tried to cut and paste columns into different positions, Excel displayed an error message explaining that I couldn't paste cells across the boundaries of a table.

After asking ChatGPT about the error, I learned that I could either move the columns within the table or convert the table to a regular range.

Another small learning experience along the way!

More Than Just a DNA Tool

My original goal was to begin following Jim Bartlett's process for documenting DNA matches and common ancestors.

However, as I worked with the spreadsheet, I began to see other possibilities.

With separate columns for dates, years, counties, states, and countries, I can use Excel's sorting and filtering capabilities to examine my ancestral lines in different ways.

For example, I could identify ancestors who:

  • Were born in a particular state or county.

  • Married during a specific time period.

  • Died in a different state from the one where they were born.

  • Lived during a particular historical period.

  • Shared a surname or geographic location.

These possibilities may be especially helpful when investigating family migrations or trying to identify connections between families.

And since the spreadsheet includes both Ahnentafel numbers and RootsMagic RINs, I can connect my analysis back to the individuals in my genealogy database.

What's Next?

I am just beginning to explore Jim Bartlett's Segmentology Fundamentals and his methods for documenting DNA matches.

Creating the Ancestor Spreadsheet is only the first step. I still need to study how Bartlett uses that spreadsheet in conjunction with his DNA match information.

I also need to review the spreadsheet generated by ChatGPT, particularly the division of names and places into separate fields. AI can save a tremendous amount of time, but the results still need to be checked against the original data.

What started as an effort to learn a new method of DNA analysis has already given me another tool for examining my genealogy research.

And perhaps the biggest lesson so far is that ChatGPT can do much more than help write family histories. It can also help transform genealogical reports into data that can be sorted, filtered, and analyzed.

Now, back to reading Segmentology Fundamentals and figuring out how to put that ancestor spreadsheet to work with my DNA matches!

Comments

Popular posts from this blog

Genealogy without a Subscription

Why I Upgraded to RootsMagic 12

Ancestry / RootsMagic - Tree not Searchable