AlanB
08-21-2017, 02:30 PM
Hi
I am struggling to convert a formula I have created in excel into a macro and I am looking for some help. Any help would be appreciated.
Apologies for my description below but I am finding hard to get into words what I am trying to achieve.
In column A of sheet 1 I have a large amount of text data. E.g University/ Company names - Westminster University, University of Westminster, University of Westminster London, BT PLC, BT inc, BT Inc., BT, etc etc
In column A of sheet 2 I have a table containing a larger list of similar data found in sheet 1.
Parallel to that in column B of sheet 2 I have the text that I would like to convert the text in column A of sheet 1 to if there is an perfect exact match to make everything uniform when I run the script.
In fact rather than converting the text I would like column A of sheet 1 to remain the same and the new information to be added to column B. At the same time in column C I would like the data in column C of the sheet 2 look up table to be inputted also.
Example of the sheet two lookup table.
Column A Column B Column C
Westminster University Westminster University University
University of Westminster Westminster University University
University of Westminster London Westminster University University
BT PLC BT Company
BT BT Company
BT Inc BT Company
BT inc. BT Company
I would need the ability to constantly add the table in sheet two to improve the results.
I had trouble with my excel formula creating an exact match. The lookup in some instances produced bad results - For example HeidelbergInstitute returned the result Berg as I had a company with that name in the look up table of sheet 2. If the company / university is not found then I would like N/A returned.
Many thanks for your help.
A
I am struggling to convert a formula I have created in excel into a macro and I am looking for some help. Any help would be appreciated.
Apologies for my description below but I am finding hard to get into words what I am trying to achieve.
In column A of sheet 1 I have a large amount of text data. E.g University/ Company names - Westminster University, University of Westminster, University of Westminster London, BT PLC, BT inc, BT Inc., BT, etc etc
In column A of sheet 2 I have a table containing a larger list of similar data found in sheet 1.
Parallel to that in column B of sheet 2 I have the text that I would like to convert the text in column A of sheet 1 to if there is an perfect exact match to make everything uniform when I run the script.
In fact rather than converting the text I would like column A of sheet 1 to remain the same and the new information to be added to column B. At the same time in column C I would like the data in column C of the sheet 2 look up table to be inputted also.
Example of the sheet two lookup table.
Column A Column B Column C
Westminster University Westminster University University
University of Westminster Westminster University University
University of Westminster London Westminster University University
BT PLC BT Company
BT BT Company
BT Inc BT Company
BT inc. BT Company
I would need the ability to constantly add the table in sheet two to improve the results.
I had trouble with my excel formula creating an exact match. The lookup in some instances produced bad results - For example HeidelbergInstitute returned the result Berg as I had a company with that name in the look up table of sheet 2. If the company / university is not found then I would like N/A returned.
Many thanks for your help.
A