Come along as I attempt to create a functional spreadsheet to parse a large number of Smiths. The end product is probably not the finished product as this is just an experiment. Your feedback is welcome!
I was a bit disappointed with my last post because there weren’t as many Smiths as I was anticipating. I had wanted to write specifically about my research experience where there was an abundance of people with the same surname in one geographical location.
Unfortunately, it didn’t pan out that way. However, I went ahead with my post because there were some decent tips that I shared. One of those tips mentioned that you may want to consider creating a spreadsheet (or table); however, I didn’t really elaborate, so I feel like I dropped the ball there.
The table I originally created was fine for a handful of Smiths, but I don’t think it would have been practical for a large number of Smiths. So, I decided to do a surname search on a different location in the hopes of finding a larger population of Smiths. I hit the jackpot in Reading, MA, where roughly 100 Smiths lived between 1669-1710.
To begin, I created a spreadsheet with the following categories: Name, Birth, Marriage, Death, Notes, and Source. Then I got to work transcribing the Smiths of Reading. I used the “Notes” column to record how people were related. I ended up with about 75 different Smiths (there were duplicate records for some people). That spreadsheet was overwhelming to me, and I wasn’t sure how to proceed in organizing it. Eventually, it made sense to alphabetize the names.
Alphabetizing was a good choice because it allowed me to weed out some duplicate people. Additionally, I noticed that a lot of the people with the same first names were actually the same person but their birthdates had most likely been transcribed wrong:
You also probably noticed that I shaded the males in gray and assigned ascending numbers to those with the same first names. I am not sure if these steps are actually warranted; I was just experimenting.
Once I got rid of the duplicates, I used the information contained in the “Notes” column to help me group people into family groups. I accomplished this by clicking and dragging the rows that I wanted to move. I then assigned a color to every family group that I had created. This worked…okay. But I felt like I could do better. As you can see, there were a handful of leftover people at the bottom (which honestly probably would have happened regardless of the method used):
At this point, I wondered how I could improve my spreadsheet. So, instead of using notes to track familial relationships, I decided to give each individual relationship its own column: mother, father, spouse, and child. That way, I could alphabetize these columns if need be. I also decided to create a column in which I could number each individual or family group. Finally, I created an empty column at the far left for color-coding:
Next, I moved from Reading to the Smiths of Woburn, MA. I used FamilySearch to search Smiths in Woburn from 1630-1700, and then I started transcribing the records that had popped up. Notice below how I numbered each individual or family group. That was just in case I wanted to sort people back into their original family groups later on. I don’t know whether or not this is helpful yet, but it can’t hurt.
I recorded every piece of the following information with the exception of the place (since I was only searching within Woburn):
Here is how the above information looked once it was transcribed:
Once I recorded the last of my Smiths of Woburn, I alphabetized the Name column. I use Apple Numbers, so Excel will be a bit different, but first I clicked on the “C” located at the top of the “Name” category to highlight the whole column. Upon clicking, a series of choices popped up; I clicked “Column Actions.” That made a box pop up to the far right from which I clicked on “Sort Ascending.”
Once the names were alphabetized, I checked for duplicate names and date transcription errors:
Once I took care of duplicates, I alphabetized the “Father” column. Notice below how all of Matthew’s children are now grouped together. Now, before you do anything with the kids, go ahead and check out the “Father” column; you will probably notice that you have several duplicate fathers (for instance I had a ton of the same Matthews). You will need to carefully go through and see if you can figure out which men are unique and which are duplicates of the same man. Delete any extras you find. If you’re not sure, just leave them alone for now.
Once I eradicated the extra Matthews (I felt confident that there was only one Matthew), I dragged my remaining “Matthew row” and dropped it at the top of the list of his children. I then went ahead and assigned a blue color to Matthew’s family group. [Also: don’t forget to see if the father has a spouse. If he does, drag and drop her “row” just below his. In this scenario, Matthew did not have a spouse listed]
Once you get to the point where each father is in a color-coded group with his wife and children, you may want to create family group sheets for each family:
Once I had all of my family groups together, I ended up with four distinct families—most belonging to Matthew’s group. Had there been a ton of Smiths like the Reading group, I think this task would have been much more difficult. However, since this was a relatively small group, my spreadsheet method worked well for me.
Since there weren’t too many Smiths in Woburn, I decided to use my latest spreadsheet to re-transcribe the Smiths of Reading; I wanted to see how it stood up to a large number of people. There were also about 10 different John Smiths in Reading, so I wanted to see if it would be any easier to sort through them.
The verdict: Unfortunately, I didn’t find Spreadsheet 2.0 any easier to use on this large group of Smiths. There were just too many people to efficiently sort through. While I first alphabetized the “Name” category to delete some identical entries, afterwards I still wound up with a bunch more duplicates, for which I had to use the “Find” feature (see below). Additionally, alphabetizing the “Father” column didn’t seem to make the process any easier either.
I really feel like I am missing something here; there has got to be a way to better utilize this spreadsheet for a large amount of people. I guess it’s back to the drawing board for me.
At the end of the day, I am just one person, and this spreadsheet was created in a way that made sense to my brain. I have no clue if it will work well for anyone else, but I am very curious. If you happen to test out Spreadsheet 2.0, definitely let me know what you think—good and bad. Or if you know of better ways to organize a large group of people, I’d love to hear from you. After all, when it comes to genealogy, I am entirely self-taught, so I’m not the least bit familiar with methods other people are using. And I’m definitely not looking to reinvent the wheel!
No matter which method you use, creating a surname spreadsheet can be a lot of—tedious and time-consuming—work. At the same time, it can really pay off. First of all, it’s nice to be able to visualize the various family groups. Secondly, as you create these groupings, you will potentially make some discoveries and generate some leads, which can help point you in the right direction for future research. Finally, if you’re like me & love playing with spreadsheets (especially when you can use color), it can actually be fun—once you’ve made it past the transcription part.