Looking for guidance on how to approach an excel project

[emma313823]

Hi All

I'm not a programmer, so if you could please address from that perspective it would be appreciated.

I have the need to clean up sales data provided by several of our principal customers. The data can easily be put into a template type format file - meaning specific column related structure.

Customers are reported in a variety of ways - ie ACB Company, ABC co., ABC Inc...and I want to find a way to create something that would allow me to take all these customer variations and roll them up under one primary customer name. Right now we have a customer tool which does this, but it is going away. I believe the tool was written in VBA, so have no idea how that tool does what it does.

Additionally, to making customer name resolution changes, we also have the next step, which would be to ensure that the appropriate salesperson has the correct accounts assigned to them. The one unique identifier is a customer ID per customer, however there are multiple lines of sales data for each customer ID.

Does anyone have suggestions as to how to tackle this project? Because I do not have the programming experience, it may be if this is beyond my scope of knowledge that we acquire the skills of someone who can do this.

Emma

 

[mrmmickle1]

I would recommend trying to fix the data at the source. The problem you mention has to due with data integrity. It's extremely problematic to refer to customers the way you mention:

ie ACB Company, ABC co., ABC Inc

I have fixed things like this in the past by using VBA that finds and replaces values in an effort to standardize the values......

If you do not have any programming background this could prove difficult but not impossible. You could potentially build a lookup table and use Vlookups + Copy and Paste Values to do the same thing I mentioned. For Example:

Excel 2010
	A	B
1	Junk Data	Standard
2	ABC Inc	ABC Company
3	ABC Inc.	ABC Company
4	ABC Company	ABC Company
5	ABC Incorporated	ABC Company
6	ABC Ltd	ABC Company
LookupTable

Excel 2010
	A	B
1	ABC Inc	ABC Company
2	ABC Inc.	ABC Company
3	ABC Company	ABC Company
4	ABC Incorporated	ABC Company
5	ABC Ltd	ABC Company
6	ABC Inc	ABC Company
7	ABC Inc.	ABC Company
8	ABC Company	ABC Company
9	ABC Incorporated	ABC Company
10	ABC Ltd	ABC Company
11	ABC Inc	ABC Company
12	ABC Inc.	ABC Company
13	ABC Company	ABC Company
14	ABC Incorporated	ABC Company
15	ABC Ltd	ABC Company
16	ABC Inc	ABC Company
17	ABC Inc.	ABC Company
18	ABC Company	ABC Company
19	ABC Incorporated	ABC Company
20	ABC Ltd	ABC Company
Your Data

Cell Formulas
Range	Formula
B1	=VLOOKUP(A1,LookupTable!$A$1:$B$6,2,FALSE)

After you have standardized the company names then you could do better analyses.

Additionally, to making customer name resolution changes, we also have the next step, which would be to ensure that the appropriate salesperson has the correct accounts assigned to them. The one unique identifier is a customer ID per customer, however there are multiple lines of sales data for each customer ID.

Similarly, you could create a lookup table for Sales Reps use this to ensure the actual output corresponds to your correct lookup table....