Macro for batch search/replace but also add columns (advanced?)

[richiwatts]

Hi,
Can someone help me with a batch search replace script?

I get a fixed a list of names in column A every month. I have a fixed list of 100 people but each month, the amount of people reporting is different.

Chris Dean
John farmer
Tony Smith

I need to batch search and replace these names so that I end up with

JPY<NC>80<NC>Chris Dean
USD<NC>5<NC>John farmer
EUR<NC>6<NC>Tony Smith

This is the person's hourly rate. NC just means nnew column.

So after the batch search and repalce I end up with Name in column C, amount in column B and currency in Column A.

Thanks for your help.

Rich

 

[oldbrewer]

do you have a table somewhere so you can look up Chris Dean and obtain JPY and 80 ?

if so put JPY in col B 80 in col C make col D = col A then use a macro to copy columns A to D, paste special values and delete column A

 

[richiwatts]

do you have a table somewhere so you can look up Chris Dean and obtain JPY and 80 ?

if so put JPY in col B 80 in col C make col D = col A then use a macro to copy columns A to D, paste special values and delete column A

yes, I have a table in another sheet but I am not sure I understand the above. Can you hekp with an example?

 

[oldbrewer]

use vlookup to get JPY and 80

 

[richiwatts]

use vlookup to get JPY and 80

Thank you. I acheived what I needed with Vlookup. Took me a while to Google and work it out but worth the time it will save me each month.

 

[oldbrewer]

glad to help - and to put a new arrow in your quiver - just remember to put first column into ascending order - if this is not possible then offset match can be used

 

[oldbrewer]

[TABLE="width: 1094"]
<colgroup><col><col><col span="7"><col><col span="4"></colgroup><tbody>[TR]
[TD]Chris Dean[/TD]
[TD]£18Chris Dean[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]col J[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]name[/TD]
[TD]currency[/TD]
[TD]amount[/TD]
[TD]row 5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]formula[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]John farmer[/TD]
[TD]£[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 8"]=OFFSET($J$5,MATCH(A1,$J$6:J448,0),1)&OFFSET($J$5,MATCH(A1,$J$6:J448,0),2)&A1[/TD]
[TD]Tony Smith[/TD]
[TD]$[/TD]
[TD="align: right"]22[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Chris Dean[/TD]
[TD]£[/TD]
[TD="align: right"]18[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]