Problem: I collect data from sales reps. There must be a half dozen ways that they enter General Electric.
- Conform all ways to spell these customer names.
Strategy: You can use a pivot table to help solve this problem. Follow these steps:
- 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.
- Copy the entire pivot table.
- Paste Values to convert the pivot table to regular data.
- Insert a new column between A & B. Copy the customers from A to B with a heading of Good Customer.
- 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.
- Find duplicates and fix one in column B.
- 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.
- Do a VLOOKUP.
- Copy the new column. Paste Values.