Let me re-tell a story told to me over coffee by one of the members of the Electrical and Communications Association Queensland during their fabulous national conference in Ayers Rock. “Debbie, we had a large contract that was coming up in a few months, but in the interim we needed to take up the slack. Naturally we thought we’d go to our customers first and see if we could get some new business from them.
“We initially thought we’d contact 1000 of them with the idea of offering a complimentary check of their outdoor electrical connection. We began with a trial of 100 letters and got 80 appointments! In fact, we got so much work from that first mail out of 100 we couldn’t even contemplate sending more letters we were so flat out!”
 So what’s the point?
- Your customer database is one of your most valuable assets.
- Go to the people that know you and love you first for more business (should you ever need it!)
- Here’s a great P.S. to add to any communication (especially invoices) “Your referral is our greatest compliment”.
Problems, problems
So you might want to use your database en-masse instead of sending out individual statements and invoices. There could be inconsistencies in there (duplications, mis-spellings, common email address errors (bigpond,com) or you might need to add, separate or delete some information.
Database Magic
There are many hidden jewels in Excel that with a push of a button can help you fix your database information in seconds. Most are hidden behind the “fx” button (functions), however you’d have to be an utter rocket scientist to understand the Excel description of them, then to take the concept and translate it to words, names ‘facts’ instead of figures. Having said that you still may be thinking “Excel, what’s she talking about? We keep our database in Access, File Maker, our own software. Who uses Excel?”
It doesn’t matter where you store your information – that’s almost irrelevant. There is nothing better than Excel to quickly, easily and stunningly fix database information or to create customised mailing /email lists in split-second time. To move information from one software program to another (such as MYOB, Quicken, Proprietary databases), simply save the information as a CSV (comma separated variable) or a TAB (text) file.
Then open it in Excel. Additionally, you might not know that it’s incredibly easy to export information from your ‘contacts / address book’ in your email program to Excel. There’s a wizard that walks you through it completely. Just go to the file menu and select import and export.
Six magical database solutions
A little creative thinking and knowledge of a few crucial functions can make a database maestro of anyone. Let me part with a few of my favourites taken from the book Superb Tips and Tricks For Managing Your Customer Information. (www.debbiespeaks.co.nz/books.htm).
Problem One: First and last name together in one column. You want to personalise and not send Dear John Smith. Solution: (Data menu> Text to Columns). This function has a three step wizard that separates out information from within one column. It asks you what to look for (blank space, a comma) and then asks if you want any special formatting. That’s it! Just be sure to add a lot of extra blank columns for the information to go to – some people have last names like van de Whitten!
Problem Two: Fix errors. For example, you might want to weed out easy typo’s on email addresses. Solution: Find and Replace (Edit menu > Replace). Simply highlight the column of information where the email addresses are. Then in the “find” box put , (comma) and in “replace” put the . (fullstop). Find .co. au and replace with .com.au. You get the idea. The following functions are hidden behind the “fx” button (functions).
Problem Three: Mixed upper and lower case. This could be from the way you store your names or from accumulating data from website entry. You don’t want to send out letters or emails that say Dear JOHN or Dear ROSE do you? Solution: (fx) Proper. A veritable gem. Proper will change the first letter of each word to upper case, the rest to lower case. So debbie mayo-smith becomes Debbie Mayo-Smith. Additionally, there are the functions “upper” which will turn the information into all upper case and lower which turns it to all lower case.
Problem Four: Information in many columns that need to be put together in one. For example, you have a list where the first and last names are separate, but your database requires them to be together. Or postcode and state need to be together. Solution: (fx) Concatenate. This will merge separate columns together. So if you have P O Box 123, Sydney, NSW, 2000 in four different columns, Concatenate will put them together into one column.
Problem Five: Extra blank spaces got into your database from incorrect data entry or some other way. Solution: (fx) Trim. Removes all extra spaces in a column except for ones that should be there in-between words.
Problem Six: Duplicates in your database. If you’ve merged several lists together, you might find duplicates. Weeding them out is arduous if the list is long. Solution: (fx) If statement. This is where creative thinking comes in. Sort, for example, by email address. Then using the If statement function, you can have Excel look to see if one row of a column (let’s say C2) is the same as the row after it (C3). You tell the function to say duplicate if they’re equal; false or “not duplicate”. If they’re not, drag the function down the entire column or double click quickly. Voila. Now you know what duplicate is.
Two Important Notes
Here’s how all the functions work:
1. Insert an empty column to the right of the one you want changed. (Highlight the Column>Right click>select Insert).
a. Double click on the little fx.
b. Type in Proper, Upper etc or select it if it’s showing.
c. As shown below, a box opens called Function Arguments. What you’re meant to do is just show Excel what columns you’d like to work from.
2. Caveat – they’re still “functions” and you need to turn them into ‘text’.
It’s SIMPLE:
a. Highlight the new column with your corrected data in it.
b. Select Copy
c. Edit >Paste Special
d. Select Paste Value
e. Hit okay (you paste special right over the original content)
Debbie Mayo-Smith conducts customised in-house training (09) 575 5359, or get her new book Conquer Your Email Overload Superb Tips and Tricks For Busy People. Sign up for Debbie’s Business Quick Tips. Debbie Mayo-Smith is a keynote speaker, consultant and author.
Sign up for her online newsletter at www.successis.co.nz |
