Creating a 'find and replace' template

Hi Guys,

I need to create some kind of template/macro that looks at a column, and replaces the values in that column with values from a mapping list. The mapping is fixed which should make things quite straight forward, and the columns are always fixed as well.

I will have a report coming out of a payroll system, the extract is created in Excel format. I then need to have a template to load this data into, or a macro that can be run, which will take one column (which contains payroll Wage Type codes that represent different wage types - ie: different earnings and deductions) because the Wage Type codes then have to be changed, as we then need to load this extract into a different system that has different WT codes for the same Wage Type.

What is the best way to do something like this - a template, or simply create a Macro and run it once we have the extract from the first system?

Example:

I will have an extract that looks something like this (pipe used here to separate columns to make it look clearer, no pipe in actual file, just columns):

Base Salary | 1,000 | 100
Bonus | 500 | 105
Tax | 200 | 200
Soc Security | 50 | 205

and I need to change the values in the third column so that I get the Wage Type codes needed to load into the next system:

Base Salary | 1,000 | BS10
Bonus | 500 | EC05
Tax | 200 | IT00
Soc Security | 50 | SS00

The mapping of the values in the third column is fixed, ie: 100 = BS10, 105 = EC05 etc.

Any help is much appreciated guys!

Refer attached file for a solution that should meet your needs.