Saturday, January 21, 2017

BLM and spreadsheets

I love the Bureau of Land Management’ s General Land Office Records. I do lot of research is in the state of Mississippi which is a public land state so the BLM records are invaluable. I am working on a FAN* project involving my favorite brick wall, James Simmons (1764-1843). James’ first known land purchase in the Mississippi Territory was in 1816 (the BLM records show 1820 because that is when the note was paid off but the transaction was in 1816). I want to to see who bought land in the same township/range as James but I want to narrow it to the same time period. James’ property was in T5NR11W. I can easily do a search for everyone in that township/range but I get a list that is 6 pages long. I can get a consolidated list by clicking the Printer Friendly button in the upper right corner which is better but the list is alphabetical and not in date order. It is easy to miss something when you just scan the list. Here is what it looks like:


This table is screaming, “Put me in a spreadsheet!” I simply copied and pasted the table straight into an Open Office spreadsheet. MS Excel is my normal go to but in this case no because of the dates (MS Excel does not recognize pre 1901 dates). It pasted beautifully. I had to do two things first.

1) Format > Merge Cells
2) Highlight the date column and then Format > Cells.  Change the date so that you can see a 4 digit year.

Now I can sort. Highlight the entire spreadsheet and then go to Data > Sort. Sort by Column C (the date column) and choose Ascending.

And here is what it looks like now:



My James is right at the top. I can cross reference this against everyone that was in the same section or the adjacent sections if I wanted to. It is important to know that Section 33 happens to be on the border between townships so I need to do the same thing for T4NR11W to pick up those sections to the south that border Section 33, namely Section 4 of T4NR11W. I am a visual person so once I have my names and dates I use History Geo to show my the layouts and the proximity of the plats. You can see that the dates are on the map but I prefer to have the names and dates in my hand up front because again, if I just scan the map I might miss something.

ss2Screenshot from History Geo

* FAN = Friends, Associates, Neighbors.  This acronym was coined by Elizabeth Shown Mills.  It is a research technique where you look to the people that surround your ancestor in hopes of finding out information about your ancestor.  You can read more about it HERE.

Copyright © 2017 Michèle Simmons Lewis


  1. Excellent post Michele, thanks. I can think of several of my ancestors for whom this would be a good working method with land records.

  2. You can use Excel to sort pre-1901 dates by separating the year from the rest of the date. Add a column and enter the function =RIGHT(text,4) where "text" is the cell reference where the date resides. Copy the cell down the column. Then select that column, copy and paste with Paste > Paste Special > Paste Values. Then you can sort the column as years.

  3. Thanks for the comment about MS Excel and pre-1901 dates --- very helpful to know!

  4. Michele - are you aware that your last few posts are being cut off when they are sent by e-mail? I finally went directly to your blog because I was worried - your post abruptly ended in the middle of the second or third sentence. Glad to see the whole post is here but not sure what is going on with the e-mail version or how to fix it. Thanks, Georgia

    1. Thanks for letting me know, Georgia. I will check into it. The entire blog is coming through for me via gmail both on my computer and on my phone. Is anyone else having a problem with this???

    2. Your emails have been truncated for over a year in my experience. I thought it was by design, but I did fear missing an interesting post because scanning the couple of sentences might not catch my attention. Lately I've been making a habit of always clicking on the title (link) to make sure I scan the blog post.

    3. As Mark says. It's been truncated for me for awhile. I just thought you wanted to drive traffic to your blog as a number of other blogs I follow do that too.

    4. I think I found the problem and I have changed a setting in blogger. Please let me know if that fixed the problem.

  5. Thank you for posting! Great research tool!

  6. Thanks for the link to History Geo. I could really use this for my research in Alabama and Mississippi!

  7. Thanks for the reminder about BLM and HistoryGeo, two sites I've used quite a bit in the past but not recently. And, that's interesting about Excel and pre-1901 dates. Strange!