Using ChatGPT to Streamline DNA Analysis in My Coleman Research

David Coleman, my husband’s third great grandfather, was born about 1801 somewhere in Ireland. This information comes from the 1850 Federal Census — the only record we have includes these details about him. He married Catherine, likely in Ireland, though her maiden name is unknown. By 1820 they were in Saratoga, New York, where oldest son James was born.

For more than sixty years Mary Coleman Kennedy searched for David’s birth location. The family was Catholic, but few baptismal records from 1801 have survived in Ireland.

About eight-years ago Dave and three of his sisters took DNA tests. Using their data, I followed Blaine Bettinger’s five-part blog posts on visual phasing to determine which DNA segments came from each grandparent. Since I’m on a Mac, I did the analysis manually using Keynote and Excel, and then imported the phased segments into DNA Painter.

Below is Dave’s visual phasing (VP) showing the Coleman segments he inherited from his grandfather, Herbert Coleman, Sr.

Whenever one of the siblings has a DNA match, I compare the match’s chromosome location in DNA Painter to the four grandparents segments. This allows me to determine whether the match is maternal or paternal — and often which grandparent line it belongs to.

Over the last eight years, we’ve collected a large number of DNA matches. The example below shows chromosome 10, where the blue segment represents Coleman DNA, and the green segment represents the paternal grandmother’s Marti DNA.

The matches whose segments fall under the Coleman blue could help me locate David Coleman (born ~1801) in Ireland. Since they are DNA, there must be a family connection somewhere back in time. If I can identify where those matches’ ancestors lived in Ireland it might point to the area where David himself lived.

Earlier I used this same method to locate my great-grandfather Thomas Byrnes’ birthplace in County Roscommon, Ireland — research that was published in The Septs in April 2024. Starting with DNA from my second cousin and me, I traces Thomas three generations back. Beginning with Dave and his sister’s grandfather,we would also need to go back three generations to reach their third great grandfather, David (born ~1801).

For the Byrnes research, the earliest records were Griffith’s Valuation (1847 —1864). With luck, some of these Coleman DNA matches have earlier, surviving baptismal records that could finally identify David’s birthplace.

The biggest challenge has been isolating only the Coleman-line matches from the DNA Painter data. Although DNA Painter allows me to download a csv file for each chromosome, it’s cumbersome to filter through hundreds of matches manually.

For example. Dave has 100 matches just on chromosome 1. The gold segments under the blue Coleman line represent triangulated matches from MyHeritage and the pink ones are triangulated matches from 23andMe.

There was no easy way to extract just the matches under the Coleman line — until I asked ChatGPT to write an Excel macro for me!

This was my first prompt to ChatGPT on the problem: Here’s a new problem to work on. Family lore says David Coleman was born about 1801 somewhere in Ireland. We don’t know where.  I was to use locations of DNA matches to find where he likely lived.  Here are 2 csv files.  C.csv has the locations of Coleman segments on various chromosomes.  There is a header row.  Column A had chr# the chromosome number. Column B has the start and Column C the end of the segment.  P.csv file has all of the paternal data.  For the comparison A2 of file P must equal A2 of file C.  Then look at Column B of file P.csv until you find a number greater than or equal to the number in B2 of file C.  Remember that row. Now look at Column C of file P and scroll till you find a number less than or equal to the number in C2 of file C.  Now take that the data you found and copy those rows to a new csv file.

The c.csv file has the Coleman segments from Dave’s VP file for the various chromosomes.

The p.csv file has all of the paternal matches. It’s ChatGPT’s job to create an Excel macro for Mac that will collect only the Coleman matches.

There were a few issues initially. I’m on the free version of ChatGPT and had hit the end of the day.

ChatGPT’s reply: It seems like I can’t do more advanced data analysis right now. Please try again later. If you want, I can still walk you through the exact steps to run this matching process in Excel or in a simple Python script on your computer so you can get the output immediately. Would you like me to give you those step-by-step instructions?

So I asked for an Excel macro that would run on Mac. This was the first macro iteration.

ChatGPT’s reply: Assumptions

• You have two sheets in the workbook:
• Both have headers in row 1
• Column A = Chromosome, Column B = Start, Column C = End
• You want results on a new sheet “Matches”

I changed from the two separate csv files and made them two sheets in one Excel file. And then ran the macro.

My next prompt: I got it to run – it only found the 4 occurrences of Coleman, not all the data within the start and end range, which is what I want. Once you find the start value in P that matches the one in C, then copy all the rows of data you find until the end value in P matches the end value in C.  would you change the macro to do that?

This was marco version 2. My prompt: Still not working – let’s try something else. You’ve got the chr# in C and P matches perfectly. Now let’s not look at Start or End.  In column F under the header ‘Match’ in file C it has text Coleman.  With the chr# selected – look in column F of the P file and find text Coleman. Copy that row into new match sheet and then copy all the rows under it, that still have chr# matching until you find the text  Marti in column F in sheet P. Then move to the next row in sheet C and repeat this.   If you do not find text Marti, but the chr# no longer matches, then also move to the next row.

ChatGPT’s reply: Got it — so now the logic becomes:

• Use the chromosome number in C and P to match
• In C, column F (“Match”) must contain “Coleman”.
• Once we have the chromosome number, look in P column F for “Coleman” on that chromosome.
• Copy that row from P to Matches sheet.
• Then keep copying the following rows (same chromosome) until column F says “Marti” — then stop.
• If “Marti” isn’t found, stop when the chromosome changes.
• Move to the next “Coleman” in C and repeat.

This was macro version 3. My prompt: Almost perfect!  several chr like chr 2 have more than one segment.  The macro duplicated all the matches on chr 2.  When the macro finds the first occurrence of Coleman in column F for a chr# it needs to remember the start that was in column B of that row.  then it goes on and stops at Marti and starts at Coleman again if chr# is the same as before and column B has larger start value. It should not go back to a lower sOkay — the duplication on chr 2 means my current loop is checking every “Coleman” in P for a given chr in C, even if it’s already been handled from another row in C.

ChatGPT’s reply: To fix that, we need to:

• Loop through C in order.
• For each row in C, find only the matching Coleman in P that has
  • the same chr number,
  • the exact same Start as in C column B,
• Then copy from there until Marti or chr changes.

That way, we never re-run the same Coleman block twice for the same start value.

My prompt: That worked!!

This entire conversation — and the four versions of the macro it took to get it working —lasted about an hour and forty-five minutes. I can not even estimate the time it would have taken me to do this myself!

Of course, you always want to verify anything a chat bot gives you. In this case, I already had a csv file in the correct format for DNA Painter, so I could easily add it back into DNA Painter for a visual check.

The only match that might have fallen outside the selected region was at the bottom of the image — for Aunt Mary. Her parents are Dave’s grandparents, Herbert Coleman and Harriett Marti. Since her match could reasonably extend a bit beyond the region of interest, that doesn’t concern me.

This project was a great example of how AI can enhance traditional genealogical research. What once felt tedious became manageable — and even fun — with a little help from ChatGPT. There’s still plenty of work ahead before I can pinpoint where David Coleman (born about 1801 in Ireland) came from, but these DNA-based tools have moved me one step closer.

I shared this example as part of my presentation, “Enhancing Genealogy Research with AI Assistant,” at the Minnesota North Star Conference in Eagan, MN on 25 October 2025.