This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Fix Misspelled Customer Names
Problem: I collect data from sales reps. There must be a half dozen ways that they enter General Electric.
Figure 937 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.
Figure 938 Find duplicates and fix one in column B.
6. 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.