everydayExcel Issue - 29 Sep 09

Eliminate Duplicate Records and Reduce Wastage!

If you are currently managing a customer database, there is a high chance that your database contains duplicated records. We are not referring to duplicated records where the details from the records are exactly the same. That would have been easy to remove. We are referring to records containing the same postal addresses written in different ways, as a result of abbreviation and spaces. For example, someone unknowingly entered two same entries with the address as 10 Anson Road and 10 Anson Rd. In this case, if the entries appear far apart after sorting, it can be difficult to detect the duplicate. To solve this problem, you can make use of Excel formulas to compare the Postal code and the Unit number of the addresses. If the Postal code and the Unit number match another record, it means that this record is a duplicate and should be deleted away.

But what if the duplicated records are caused by the different order in the first name and last name, for instance, with one record presenting the name as Jason Khoo while another presented as Khoo, Jason? It would have tough to identify the duplicated records except to rely on your memory as you run through record by record. So is there anything we could do to solve this problem caused by inconsistency when entering the names into the database? The good news is YES! We have developed a formula in Excel to sort out the duplicated names that are in any order (Jason Khoo or Khoo Jason), as long as there are no extra characters in the names. With the result, we can then apply the same technique used for the duplicated mailing address to remove the duplicated name records.

If you wish to get hold of this formula and learn how to make use of this formula to identify the duplicated records, send out your brochure or newsletter only once to each of your customers, reduce wastage and even save cost, sign up for our Customer Data Analysis with Excel Course now!

Year after year, whenever my car insurance is about to expire, AIG will send me a renewal notice. When that happens, I will call a few insurance agents to find out the cheapest deal in town, starting with my own insurance agent. I will also get quotations from car insurance agents who left their flyers on my car. The usual process is to furnish my particulars before they are able to generate a quotation. Although the process is simple, it always leaves me wondering...

>>Why my life insurance agent did not call to help? Isn't this a good opportunity to keep in touch with me?

>>Why my existing car agent did not call? Wouldn't I be a ready customer for him?

The most plausible reason I could think of is they do not have a Customer Management System to track their existing customers. They probably have rationalized that it is cheaper to get somebody to distribute flyers and get new customers than to spend on a Customer Management System to follow up with existing customers. What they do not know is - in actual fact; they do already have an excellent system for this! It exists right in their computer - Microsoft® Excel.

With Microsoft® Excel, you could easily identify your customers whose car insurance policy is due to expire in the following month. You can achieve this through auto-filter, pivot table and use conditional formatting to highlight those policies that are due in the next 30 days. Especially with conditional formatting, the records that need your follow up will be highlighted automatically upon coming within your next 30 days window. As they are renewed, the highlighted records will disappear automatically, until the following year when the car insurance policy expires again.

If there is a need to send a reminder to your customers that their car policy are due for renewal, you can make use of MS Word mail merge function and Outlook to create customized messages and surprise them with this value-add service. This may lead to more referrals from your existing customers and who knows, they could be so impressed with your personalized service that they might buy life insurance policies from you too!

Traveling time can make a lot of difference to how many customers you are able to meet each day. If you need to travel around a lot to meet customers, then you might want to consolidate your meetings so that you can just travel to a location and meet all the customers staying in the same area. You can do this just by making reference to their Postal Codes in your database.

Using Pivot Tables, you can also identify the customer segment you are strong in. It could be age group, income group or location. This allows you to allocate sufficient resources to increase your market share in that customer segment.

Find out how you can increase your sales at minimal cost step by step through our Customer Data Analysis with Excel Course today!