Data Mapping

[zinah]

Hi,

One of the challenges that some of us are facing is data mapping and I'm sure some of the respected members here have some solutions or suggestions. What I have is below example and what I need is to know if there's anyway that I can automate the cleaning by a formula?
The company name column is where a user update the company name periodically and sometimes they put different name for the same company and below I put Amazon as an example. Is there a formula I can use to map the company name to a specific company (i.e. Amazon AWS to Amazon?)

Book3
	A	B
1	Company Name	Mapped Company
2	Amazon	Amazon
3	Amazon - London	Amazon
4	Amazon AWS	Amazon
5	Amazon Development Center	Amazon
6	Amazon Development Centre India	Amazon
7	AMAZON INC	Amazon
8	Amazon Lab126	Amazon
9	Amazon Web Services	Amazon
10	Amazon Web Services (AWS)	Amazon
11	Amazon Web Services(AWS)	Amazon
12	Amazon, Hyderabad	Amazon
13	Amazon.com, Inc	Amazon
14	AWS	Amazon
15	AWS	Amazon
16	AWS Amazon	Amazon
17	AWS Security	Amazon
18	Amazon Webservices	Amazon
Sheet1

 

[Zot]

Why not just use FIND. I think probably other better way than my formula here

Book1.xlsx
	A	B
1	Company Name	Mapped Company
2	Amazon	Amazon
3	Amazon - London	Amazon
4	Amazon AWS	Amazon
5	Amazon Development Center	Amazon
6	Amazon Development Centre India	Amazon
7	AMAZON INC
8	Amazon Lab126	Amazon
9	Amazon Web Services	Amazon
10	Amazon Web Services (AWS)	Amazon
11	Amazon Web Services(AWS)	Amazon
12	Amazon, Hyderabad	Amazon
13	Amazon.com, Inc	Amazon
14	AWS
15	AWS
16	AWS Amazon	Amazon
17	AWS Security
18	Amazon Webservices	Amazon
Sheet2

Cell Formulas
Range	Formula
B2:B18	B2	=IFERROR(IF(FIND("Amazon",A2),"Amazon",""),"")

 

[zinah]

Why not just use FIND. I think probably other better way than my formula here

Book1.xlsx
	A	B
1	Company Name	Mapped Company
2	Amazon	Amazon
3	Amazon - London	Amazon
4	Amazon AWS	Amazon
5	Amazon Development Center	Amazon
6	Amazon Development Centre India	Amazon
7	AMAZON INC
8	Amazon Lab126	Amazon
9	Amazon Web Services	Amazon
10	Amazon Web Services (AWS)	Amazon
11	Amazon Web Services(AWS)	Amazon
12	Amazon, Hyderabad	Amazon
13	Amazon.com, Inc	Amazon
14	AWS
15	AWS
16	AWS Amazon	Amazon
17	AWS Security
18	Amazon Webservices	Amazon
Sheet2

Cell Formulas
Range	Formula
B2:B18	B2	=IFERROR(IF(FIND("Amazon",A2),"Amazon",""),"")

Thank you for your input. I have so many companies in my list. I only gave one example i.e. Amazon what about other companies?

 

[Zot]

Thank you for your input. I have so many companies in my list. I only gave one example i.e. Amazon what about other companies?

If you have many companies that you need to map, then I think formula is not a good solution. You need VBA. You can always add or remove list for company names from a table list which can be in one of Excel sheet, not hard coded into the macro itself.

Probably you need to give scenario on how you approach your work for helpers to give suitable solution.

 

[zinah]

If you have many companies that you need to map, then I think formula is not a good solution. You need VBA. You can always add or remove list for company names from a table list which can be in one of Excel sheet, not hard coded into the macro itself.

Probably you need to give scenario on how you approach your work for helpers to give suitable solution.

That can be an option, but I'm not that familiar with VBA.
My current scenario is: I have a lookup table that has the name of the company (correct name) and other column has all the expected names of this company that users update periodically. I do index/match every month from this table and map the new companies accordingly. But this process has to have manual steps, as I usually get n/a and need to map each company one by one and it takes a lot of time. That's why I need to automate this process as much as I can, by a new formula or other suggested solution from members here.