Excel: Fix Misspelled Customer Names

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

Problem: I collect data from sales reps. There must be a half dozen ways that they enter General Electric.

  1. Conform all ways to spell these customer names.

    Strategy: You can use a pivot table to help solve this problem. Follow these steps:

    1. Build a pivot table with Customer in the Row Labels and in the Values area. This will show you each customer and the number of times that this spelling is used.
    2. Copy the entire pivot table.
    3. Paste Values to convert the pivot table to regular data.
    4. Insert a new column between A & B. Copy the customers from A to B with a heading of Good Customer.
    5. Manually scan through the report, looking for different ways to spell the same customer. When you find a duplicate, you can look at column C to see which is more prevalent. For the wrong spelling, copy the correct spelling to column B. The advantage: you only have to change the few customers that have duplicates.

  2. Find duplicates and fix one in column B.
    1. Go back to your original data. Add a new column called Fixed Customer. Do a VLOOKUP into the pivot table to get the correct customer.

  3. Do a VLOOKUP.
    1. Copy the new column. Paste Values.

For more resources for Microsoft Excel