Applying a lesson

A week or two ago, one of my Facebook friends (thank you whoever you were!) recommended this post by Mary from Searching for Stories blog: Spreadsheet Magic – Importing Data from Do go and look at the post, and Mary’s many other fantastic posts. I thought I knew a bit about Excel but it seems not.

This week I thought I’d try Mary’s strategies on my Irish research to see how it worked. Following her steps this is what I did.

Sign into with your own subscription or at a library near you.

My Step 1:

Bring up the search dialogue box and I used the Birth, Marriage and Death records option.

I entered no names and no dates. In the place of birth I simply put “Clare, Ireland”. As I’m mainly interested in those who emigrated to Australia I put “Australia” against the place of death. I don’t care which state so I wanted to pick up as much info as possible. I also ticked the exact box for both, as I didn’t want anything random to come up. Finally, I chose records from Australia as I’m expecting that is the most likely source of useful information -though perhaps not exclusively. I made sure I had the maximum entries per page (50).

import into Excel

Of course, as with all record searches you need to understand (1) what records might include both birth and death information and (2) what records are held within the overall database. As it happens, for me this means a heavy focus on New South Wales. This will be only one component of my research strategies.

Following Step 2, I copied the very long URL into to give me a short link.See what a difference it makes – from 399 characters down to 26! Thanks TinyURL!

make tiny

Step 3: I opened a blank excel spreadsheet, chose the Data tab and clicked “from web” on the left hand side. In here I pasted my Tiny URL, pressed “Go” to bring up the data, then ticked the box to the left of the data. (in this I’m following Mary’s instructions exactly). Then click “Import”. Voila!

excel import from web

A dialogue asks you where you want to paste it. I think it’s safest to put each batch into a separate page within the spreadsheet. You can do what you want with it later. With some whizzing and whirring, the data is imported to Excel.

Next step

I named that page “Clare no YOB page 1” (my first 50 details)

I repeated the process until I captured all 304 entries. This was pretty tedious I have to agree.

Step 5:

I deleted all the “padding” info at the top and bottom except the line that said items 1-50/51-100 etc.

Repeated this for all six of my page tabs.

Step 6:

As I wanted the names with other data in separate columns beside it, I dragged and dropped “spouse” “birth” and “death” into separate columns for each page, making sure each page was formatted the same.

Step 7:

Collated data extract 2

Extract from my collated spreadsheet of data. Notice the variable information.

I copied each page into one consolidated page so that entries 1-304 followed each other sequentially. I still have a problem with it because if I sort by name it will do so by first name so I will probably end up putting in another column with just surname to sort.

Similarly, the dates will sort by day rather than year and place by the first part of the entry. Is this enough for me? I will probably live with the dates, but will put in a column for state so I can see the dispersal patterns for their migration.


Was this helpful? Did it save time? Yes, I found it very helpful and I certainly got faster as I went along. The big benefit though is that it saves any transcription errors on your part (but not by the first indexer).

Mary has said the process works with Family Search but I haven’t tried that. I did try it with Trove and my “County Clare” + Obituary search. It worked okay but would require more fiddling with, and as there are MANY entries, there’d be lots of repeating of all the steps.

I tried it this morning with My Heritage but it kept giving me error messages which included that I needed to sign in, which I already was with my current subscription.

Similarly I tried FindMyPast but their search options don’t allow me to have the Clare + Australia option (or am I missing something?), so that didn’t work.

However, I believe this is a super-helpful process for anyone looking for FANs (Friends, Associates, Neighbours) or those of us working on One Place Studies projects.



Once again my very sincere thanks to Mary for sharing her expertise, permitting me to publish how I used her strategies, and giving me a new skill. I encourage everyone to check out her blog.





12 thoughts on “Applying a lesson

  1. I missed this post earlier. Thanks for resharing – will be useful at our Lake House where I have limited internet. Can download for later play. Look out Currys in Australia.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s