Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

Macro to Search/Replace Text from another Column [Solved/Closed]

Report
Posts
3
Registration date
Monday July 15, 2013
Status
Member
Last seen
July 18, 2013
 -
Posts
3
Registration date
Monday July 15, 2013
Status
Member
Last seen
July 18, 2013
 -
Hello,

I didn't realize I was being rude on my previous post, that wasn't my intention. I tend to have a "too the point" personality sometimes, and forget that text doesn't show that I'm being sincere or polite.

-----------------------

I have a rather large project at my job where I am attempting to make a lot faster and easier of a processes.

I receive an Excel file each month that has the following columns

Group Name, DOS, Clinician Rpt Name, (rest of columns have patient information)

These lists have over 500 different Clinician Rpt Names to choose from. Some of them need to be deleted, the rest need to have the name correctly written. I have a separate list that looks like this:

Column A | Column B
Abbor , John |
Smith , Frank | Smith, Frank
TERRY JOHN | John, Terry
Johnson , Mary MD | Johnson, Mary
GEORGE THOMPSON |

So, column A is the list of what the names look like on the excel file we're sent each month. Column B is what they SHOULD look like. If there is a blank it needs to be deleted.

Is there a way to make a macro where the Clinician Rpt Name will be compared to Column A on the list, and changed to column B on the list?

I anyone can help show me how to do this, if there is a way, I would be so thankful.

Thank you,

mylove0mylife
See more

2 replies

Answer 1 / 2
Posts
2688
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 7, 2021
 452
Hi Mylove0mylife,

Before turning to macro's we could solve this using VLOOKUP.

Insert an extra column called something like "Corrected Clinician Rpt Name".
Use VLOOKUP in this column. (Make sure you sort the sheet with the names on column A).

Now you could hide the original "Clinician Rpt Name" column.

You can filter your data to remove the rows with empty cells where a name should be.

Best regards,
Trowa
Answer 2 / 2
Posts
3
Registration date
Monday July 15, 2013
Status
Member
Last seen
July 18, 2013
Oh my goodness! Thank you TrowaD! That worked perfectly :) I've never used VLOOKUP before.

Thank you again! :)
Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!