Using Excel to Format FamilySearch Data

It has been quite some time since I last posted a blog, this is mainly due to my TIMMINS One Name Study taking up more time than expected.  I am still learning about surname studies so have been reading up on the subject.  I have just finished the Surname Detective by Colin D. Rogers, this book has proved to be a very useful introduction as well interesting, I can recommend it if you are in any way interested in surnames.  Next on my reading list is a book referred to many times by Rogers – The Origin of English Surnames by P.H Reaney.

So what else have I been doing over the past few weeks.  One thing that cropped up was a requirement to investigate a surname in my wife’s family, this was subsequent to the discovery of a photograph that had a list of names on it.  The family name was WARNER, they had resided in India in the 19th and early 20th century’s.

FamilySearch has pretty good coverage of India, so some family reconstruction could be carried out to determine the family groups.  Searching on India Marriages for WARNER  produced some 253 matches, that is 13 pages of links at 20 links per page.  Each marriage record has 24 items so copy and pasting all this into an Excel spreadsheet could take a long time; but……

by using my favourite data capture program Outwit Hub I devised a really simple scraper and saved myself hours.

The methodology of using this scraper is the same as detailed in my previous post Extracting Marriage Data Made Easy

Once I had Caught the data in the Catch area it was exported into Excel, I then made a copy of the worksheet (this is so that I can work on the data but retain the original data – just in case!).  There are a number of colums of data that I don’t need so all those are deleted, that just leaves the field name in column A and the data in column B.

I was now faced with a vertically tablulated column of data stretching over 6,072 rows (253 x 24).  What I really need is 24 columns of data over 253 rows.  I have used Excel for many years but my expertise in Excel functions would not enable me to sort this one out!  I did know however that a macro in VBA would be my best bet, so I searched the usual forums and found a solution.

To make this macro work I needed an end of record identifier for each of the 253 records.  The last field on each of the record sets was “Reference Number”, this field had no useful data in it – so I filtered column A on this field and filled all 253 instances with an “@” symbol, this this is the end of record delimiter for the macro.  Column A is now no use so it is deleted.  All the useful data should now be in Column A (unfiltered).  Run the macro and you now have the data in a usable format.  Insert a row at the top and name the columns a required.  Rather than have a load of screen shots of Excel showing the process you can download the Excel Spreadsheet from my Google Documents HERE (under File – Download).

There are 4 tabs in the workbook with the instructions on how to use it in the first tab.  If you want to see the code behind the macro then go to Tools – Macro – Visual Basic Editor – if it is not already visible then double click Module 1.

Well there it is, with this macro you should be able to tackle any vertically tabulated column of data and manipulate it into a useable database.

Before I sign off thanks go to Jerry Beaucaire on the Excel Forum for the neat peice of code.  Jerry also has his own Excel Assistant web site where you can leave a donation if you found this code useful.

News From The Wormhole – Dec 2011

The latest news and tips from the world of genealogy and family history; and perhaps some local history.  A round-up of bits and pieces of news, together with some book recommendations, local history and TV.  If you missed last months edition then catch up here – News Nov 2011
FHS Cheshire Toolbar
Check out the free toolbar at the Family History Society of Cheshire web site, it works with Firefox, IE, Safari and Chrome.  All the links are here for exploring Cheshire and the surrounding counties, plus lots of useful UK sites and all the commercial sites.  All the links you need in one place!
Outwit Hub (Firefox add-on)
If you have been following my exploits searching for the origins of the TIMMINS surname, you will have noticed I found a web extraction tool called Outwit Hub.  Since the last post I have been reading the tutorials, they have proved really useful, enabling me to refine the way I extract data from genealogy web pages.  You often find hidden data that can be really useful.  You can try it for free from the Outwit Hub page.

British Newspaper Archive
There has already been lots of reviews on this new resource already.  However the blog post by Kirsty Wilkinson, at the Professional Descendant, is an excellent first hand experience view by a professional genealogist and worth a read.
One of my favourite newsletters is just out – LostCousins.  The author Peter Calver has been busy using the Freedom of Information Act, getting to the bottom of the GRO fiasco on the losses made by increasing the cost of BMD certificates.

Getting your ancestors into the LostCousins database is really easy and it’s confidential, I currently have for England & Wales: 1881 – 54 entries; 1841 – 48 entries; and 1911 – 39 entries.  You are not restricted just to the UK as Ireland, Canada and the United States Census’ are also supported.

Genealogy Search Sites
Dedicated search engines for genealogy research can be very useful tools filtering out adverts and unnecessary hits.  Here are two search sites that I use on a regular basis: first is the Google based search by Randy Majors – AncestorSearch using Google Custom Search Randy says:

This tool helps you build a better Google search for finding web pages mentioning your ancestors. It returns a more focused set of search results by using advanced Google search techniques including exact phrases, forward and reverse name order, alternate name spellings, logical operators, and so on. Nothing magical, just a big time-saver!

The second is Mocavo – Today they announced a premium PLUS version with an annual subscription of $79.95 which seems rather expensive if you ask me!!  I can’t comment in depth though as I have not tried it.  Dick Eastman has reviewed it in his latest EOG Newsletter.  My interest stems from the announcement on 29th Nov when Mocavo introduced a dedicated UK version of the search tool –  Initial results using the search were very encouraging, I would urge you to give it a try.  The Mocavo blog said: 

Today we’re thrilled to launch – a United Kingdom version of Mocavo that will search websites, blogs, records and content specifically from, or discussing, English, Irish, Scottish and Welsh decent.  While searches sites and blogs from around the world, and will continue to do so, will focus specifically on the United Kingdom, giving those who wish to narrow their search to that region the ability to do just that.

My wife has a far more interesting family history than I do!  On her maternal side having links to the Indian sub-continent and on the paternal side to the Brock Fireworks dynasty.  I have been assisting in the family research on both sides, but I find the Indian connection absorbing.  I am always looking for interesting articles or new avenues for research in this area.

The Families in British India Society (FIBIS) has partnered with Brightsolid to digitise many of the India Office Records for the British Library. The India Office records will be available online to users of Brightsolid’s website.  Included in the project are two million records of baptism, marriage and burials of European and Anglo-Indian persons in India. **I can’t wait**

An interesting blog post that came to my attention recently was by Anu Anand Hall at Sacred Cows.  Read about Anu tracing her Hindu ancestry with the aid of panda’s, no not the animals! 

Google+ Webinar

On 29th Nov I listened to a Dan Lynch Legacy Webinar – Take a Closer Look at Google+  If you are a genealogist and serious about Google+ you have to watch this.  Understand why completing your G+ Profile is so important.  Be quick though before this webinar becomes payable.  –Listen here —
Book of the Month – December 2011
Reared in Chester Zoo by Janice Madden   I go to Chester zoo regularly and never get bored. This book opened my eyes to the history of the site, reminding me of the times when I was a child watching the polar bears and riding on the elephant with the mahoot.

To the people of Cheshire the Mottershead family is synonymous with the world famous Chester Zoo – the pioneer of zoo’s without bars.  Reared in Chester Zoo traces the story of the development of Chester Zoo through the life of June Mottershead, younger daughter of George Mottershead, who conceived of and founded the Zoo in 1931.  Through June’s eyes and thoughts we understand how what most people at the time considered a crazy and intrusive idea, became a thriving reality.

Previous Books of the Month
November 2011 –The Phillimore Atlas and Index of Parish Registers  by Cecil R. Humphery-Smith   This is an expensive book to buy, but once you have used it in a record office or local library you realise its worth to the british genealogist.  It is my book of the month for its invaluable help in understanding the parish boundaries during recent research into my family surname TIMMINS in the Dudley area of Worcestershire.

Genealogy Software

I noted over the past weeks a couple of software upgrades.  First is Louis Kessler’s Behold Genealogy program which has moved from Beta into Full Release.  You can try the program free for 45 days.  Louis is a genealogist who was unhappy with the commercial programs available, as they failed to provide data in the format he wanted, so he wrote his own!  I like the program using it as one of my secondary databases, however I guess it’s different approach will not suit everyone.

Next is RootsMagic which has moved on to Version 5.  I particularly like RootsMagic for its web building capability and the New FamilySearch support.  Again you can try the program before you buy, I would suggest this with all genealogy programs, pity there weren’t more choices when I started my research!  Randy Seaver at Genea-Musings has been putting the software through rigorous testing, so if you are interested stop over at his blog.
Family Tree Maker 2012
I mentioned this last months news, but the good news is that Amazon have now dropped the price to £29.99 – If you are looking for a Xmas present then the Platinum Edition is excellent value for money as it comes with 6 months PREMIUM membership to which would normally cost £77.  Family Tree Maker 2012 Platinum Edition (PC)
Probably my most exciting peice of news this month is that I have registered my TIMMINS surname with the Guild of One Name Studies.  I had been pondering this for some time but was gently coaxed into doing something about it by Carole Davies of STEERS ONS and  Slowly Being driven mad by the Ancestors.  Thanks Carole!