Multiple find and replace?

  • Thread starter Thread starter res520
  • Start date Start date
R

res520

Hi

I’m a relatively new user to Excel and have hit a bit of a problem.

I basically have a block of data and a list. The list equates certain
codes with certain names and I need to go through the data block
replacing the names with the relevant codes.
As I already have the list, is there a way of instructing excel to swap
the data in the block according to the list. A kind of ‘multiple find
and replace’ if you like.

Any advice that could point me in the right direction would be greatly
appreciated.

Cheers
 
Hi

Some assumptations (as you didn't give us enough about your diesign):
You have your list on some sheet, p.e. MyList (for my example I assume the
lists datarange is MyList!A2:B100);
Your data on your datasheet start with row 2, and the code is in column A.

Be sure that the column order in your list is Code, Name;
On your sheet with data, into some free column (adjacent to data block, or
you insert an empty column nex to column with Code), p.e. into row 2, enter
the formula:
=VLOOKUP(A2,MyList!$A$2:$B$100,2,FALSE)
and copy it down until end of table.
You can replace then formulas with values, copying the range with them, and
then using PasteSpecial.Values with same range. After that you can delete
the Codes column, when you like.


Arvi Laanemets
 
Back
Top