[SOLVED] renumber numbers?

say column A is a list of numbers:

[A]
1
1
1
1
1
2
2
2
3
3
3

say i would like to re-number the numbers to the following 1=29, 2=40, 3=5

[A]
29
29
29
29
29
40
40
40
5
5
5

is there any way to accomplish this?
thanks.

MASS REPLACE
I have a macro for searching for a series of strings and replacing them with a new set of values:

Mass Replace

To use the macro as is:

1. create a new sheet called Categories
2. put the values to find in column A
3. put the replacement values in column B
   
   The order of the values in columns A and B are important, make sure you're going in the correct order to not create problems as you work down the list
   
   
4. name your sheet to search Data
5. run the macro

You should probably change the parameter xlPart to xlWhole in the macro, too.

If you create a table somewhere on the worksheet with the original numbers listed in one column, e.g. in D2 down, 1,2,3.....and then in E2 down the corresponding numbers you want to change them to, 29, 40, 5...

Then in B1 copied down

=VLOOKUP(A1,D$2:E$100,2,0)

That will give you the revised numbers in column B. If you actually want those numbers to replace column A just selct column B, copy, then Edit > Paste Special > Values - now delete column A

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

Nick,

As per your email regarding odd results using the MASS REPLACE macro to search/replaces a series of text strings, you cited:

FIND: CHANGE:
bill...... mark
mark.... sam

If you run "Mass Replace" on this set of data, 'bill' will change to sam, not mark -- as "Mass Replace" will find 'bill' change it to 'mark,' and than see 'mark' and change it to 'sam.'

You are correct. When dealing with strings it's up to the user to construct the table so that this doesn't happen. In other words you're searching in the wrong order. The correct table would look like:

FIND: CHANGE:
mark.... sam
bill...... mark

This would find "mark" and turn it into "sam". Then you're free to find "bill" and turn it into "mark".

Never search in a subsequent search for a string you've already used as the REPLACE in an earlier iteration.

Also:

Originally Posted by JBeaucaire

You should probably change the parameter xlPart to xlWhole in the macro, too.

In your scenarios this adjustment would resolve what you're showing as well.