More Fun with Spreadsheets

If you've ever been to any of my Intermediate or Advanced Genealogy lectures, you know that I love spreadsheets. I use them to analyze large document files. I use them to track and analyze statistical censuses. I use them to hunt for data holes and inconsistencies. I use them to track events in the lives of individuals, families, and cohorts. I use them to identify an individual's (or family's) associates. I really love them! When a problem comes up, one of my first thoughts is, "Would a spreadsheet help me solve this?" One of my favorite TMG features is its ability to export so many reports to Excel.

I needed a break from preparing an upcoming presentation, and so I decided to play some more with TMG reports and Excel. My TMG database includes a growing body of Civil War events, entered so that I can quickly see a soldier or his unit's involvement in the war. "Wouldn't it be neat to see these events on a map," I thought to myself. Thanks to BatchGeo and a custom TMG "List of Witnesses" report, here's a map of the events in the life of Company G, 4th Vermont Infantry - at least through the Battle of Jerusalem Plank Road, the last event I've entered for this company. Isn't this cool? And, if you have the data entered in TMG, it's easy to do.

• I used a List of Witnesses report and output it to Excel. A big part of this trick is data entry. If it's not in TMG, it can't be included in a report - but that's another story.
• Since Excel doesn't understand pre-1900 dates, if you want your list in chronological order, make sure that Date is included as one of the report's output columns and that its sort order is marked 1. After the report is created, add a "Sort" column and number the events in order. Now, you can sort and filter to your heart's content, but you will always be able to return to the original chronological order. There are several ways to handle Excel's date problem, but if you're working with a TMG report, this method of sorting chronologically is the easiest.
• A lot of extra white space is included in the Date field, so I cleaned up that column with Excel's TRIM and CLEAN functions.
• Because BatchGeo has only one field for City and/or County, I used Excel's CONCATENATE function to combine the two columns.
• Since the column headers are included in the map, I relabeled them to make them more relevant to the contents.

That's it. I copied the data to BatchGeo and set up the options. Here are some highlights.

• The points are numbered in chronological order.
• If the point marks a battle, the point's color identifies the victor. (I haven't figured out a way to specify the color.)
• High density markers are clustered.
• You can include a URL or an image URL. I've done that with some maps, but not with this one.
• You can download a KML file and view this map on GoogleEarth. Since I have the David Rumsey historical map layer activated on my GoogleEarth, I was able to view some of these points on maps from 1860-1867. Neat!

Start to finish, this procedure took me about fifteen minutes - less time than it took me to write this post. BatchGeo is really worth exploring. Combining its abilities with TMG's gives us family historians a powerful visualization tool.