What kind of shape is your contact data in? Are all your firm’s contacts in one place?  If your firm is growing, and you haven’t already got all your contacts into one centralized list, then you should definitely get that dealt with. If your contacts are all in the same list, but they’ve never been reviewed, then cleaning them up is a really good idea.

Seriously, I recommend you call us and let us deal with this. Lest you think I’m just trying to sell you something you don’treally need, let me run you through a high-level summary of the process…..

What are the usual problems with contacts?

  • not centralized – spread over multiple individual computers
  • obsolete information
  • lots of personal contacts
  • duplicates
  • data in wrong fields

Let’s deal with these in order…

Lots of different contact lists, no centralization.

Assuming you have a place to put the combined lists when you’re done, here’s how to proceed to gather them together:

Using Excel, make a “contact list master” spreadsheet. Lots of columns, with as much granularity as you can imagine. This means separate fields for title, first name, middle name, last name, credentials, etc. If you want to get really specific, you might even have a field for common name or nickname. Think about all the people named Katherine, and how many of them are called Kathy.

Eventually, you’ll want separate fields for street address (usually 2), city, province, postal code, and separate collections of these for Home, Business, and Other addresses. Each with separate fields for phone, fax, email and cell phone. BUT, if you have any addresses in your system(s) as one long string, then make a separate column for that long string. We can unwind it later.

Now, add some important fields you might not have thought of: generic description (lawyer, supplier, expert, etc). If you want you can have 1 column where you put 1 word as the generic description, or separate columns for each generic with Y in the relevant rows. Think of a colleague who is both a lawyer AND an expert.

Lastly, and this is important, add a column where the source of the contact (remember, we’re combining multiple contact lists here) can be identified. Once you get your 3000 contacts all in one list, it might be beneficial to know who included each.

When you have this spreadsheet designed, send empty versions of it to all the users and ask them to export their existing contacts into these columns. It is vital that all staff who are “contributing” contacts adds their contacts to this list, and formats them to fit the column order of the spreadsheet. All contacts must fit an identical “template” before they can be imported.

Obsolete information and personal contacts

And before they hand it back to you, ask them to delete all the obsolete information. Irrelevant or deceased contacts can be removed entirely. If someone has relocated, just delete the “old” address. Also, ask your staff to remove any personal contacts from the lists.

So, once you get these lists back, you’re ready to combine them. Before you do, make sure that each list has a column that identifies the source. This column will look weird because it will say the same thing in every row…”JS Outlook contacts”, or similar.


Once tagged by source and combined, you’ll have probably thousands of contacts, and hundreds of duplicates. At this point, you can sort the list alphabetically and delete rows manually, or you can use Excel’s Remove Duplicates feature to remove the most obvious duplicate contacts.

Now, make sure that your information is granulated as much as possible, and you’re ready to import this new, slimmed down list into your new centralized contact database table.

Data in wrong fields

We see a lot of this. The most common is the “phone number with a description” problem a al “613-542-1294 (Cottage)”. There are functions in Excel which will strip out the letters and leave only the numbers, if you prefer. The full text version of the phone number can be added to the Notes/Comment section, if there’s no other way to preserve this information.

Ready to Jump in?

So, that’s a very high-level overview of the process. This task requires organization and focus in the extreme. I think it’s obvious that this type of data cleansing is best done outside your firm.

Give us a call. We can help.