MrExcel.com - Your One Stop for Excel Tips & Solutions

Fix Misspelled Customer Names

February 07, 2023 - by

Fix Misspelled Customer Names

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

A list of customers shows some misspellings. General Electric has purchases both as G.E. and General Electric. Wal-Mart is there with a dash and with an asterisk: Wal*Mart. IBM is there without spaces and I B M with spaces.
Figure 1003. 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.

Create a pivot table with Customer in A and Sales in B. Convert the pivot table to values and insert a new column B with the customer names again. When you see AT & T with 1 sale and AT&T with 38 sales, you put AT&T for both rows in the Good Customer column. This will allow you to do a VLOOKUP later.
Figure 1004. 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.

In the original data, insert a new Fixed Customer column that VLOOKUPs into the data you added in the pivot table.
Figure 1005. Do a VLOOKUP.

  • 7. Copy the new column. Paste Values.

This article is an excerpt from Power Excel With MrExcel

Title photo by Ryan Wallace on Unsplash