Excel find and replace from three columns [closed]

Question

Please consider the following three columns:

OLD-REP    OLD        NEW
1          1          2
2          2          3
2          3          4
2          4          5
2          5          6
2          6          7
2          7          8
2          8          9
3          9          10
3          10         11

The last two columns are the old ID with its equivalent. The first one however has the same old ID only with repetitions, so I need a way to replace the old REP ID with its equivalent in the new ID.

It will be like this:

OLD-REP    OLD        NEW
2          1          2
3          2          3
3          3          4
3          4          5
3          5          6
3          6          7
3          7          8
3          8          9
4          9          10
4          10         11

Any ideas how to do this?

Answer 1

I am not 100% sure of what you are asking but if you are looking to compare multiple columns and do some replace logic with the third you can accomplish this with vlookup.

Here is a nice video to explain how it's done.

http://www.youtube.com/watch?v=8Cl1sc9ExMI

If that doesn't work for you some searching within Mr. Excel's videos will probably answer the question for you. I was a financial analyst in a past life and his videos are pretty priceless for advanced excel questions. Hope this helps!

Answer 2

not quite whati mean there J.

to make it clearer, if i created a fourth column, the value in that column would be:

SEARCH FOR $(value in first column) IN $(second column{and match whole number}) AND REPLACE WITH $(opposing {adjacent} value in third column)

i.e. in the first column, i have all of the old values with many of them repeated.

if the old ID value is 10, we can see that the 10 in second column is opposed with 20 in the third column (so in the new system its now 20) i need all of the 10 in the first column to become 20

Answer 3

Here is an example code of what you want to do with the example you gave using vlookup:

In a fourth column type:

=VLOOKUP(B2,C$2:D$11,2,1)

Please note that $ is used to make the cell reference static. This is necessary for the code to work.

Basically the first parameter is the reference value. If your OLD-REP is on column A, then change that to A.

The second parameter is the reference table you want to look up. It will search for the reference value(the first value) from the first column in the reference table(second value). So you want it to be of your OLD and NEW cells.

The third paramenter is a integer. This integer tells excel which column to print the value from. In your case its the second column since it you want to replace it with the value of NEW cells

Finally the last parameter is if you want an exact match or not. 1 for true and 0 for false.

And of course drag the corner of the cell to extend the code to the cells below it.