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]

Chris Dean

£18Chris Dean

col J

name

currency

amount

row 5

formula

John farmer

£

3

=OFFSET($J$5,MATCH(A1,$J$6:J448,0),1)&OFFSET($J$5,MATCH(A1,$J$6:J448,0),2)&A1

Tony Smith

$

22

Chris Dean

£

18

<colgroup><col><col><col span="7"><col><col span="4"></colgroup><tbody>
</tbody>