Compare and replace if equal

[Peter Stone]

Excel 2003
Novice
I wish to paste some numbers into column C, compare each number in column C
with a list in column A and replace each number in column C with the
corresponding number in column B.

I don't care if it's a macro or VB

I can't really program, but the logic is something like this:

If C1 = A1
Then Replace C1 with B1

If C1 = A2
Then replace C1 with B2

etc. to end of column A

If C2 = A1
Then replace C2 with B1

If C2 = A2
Then replace C2 with B2

etc. to end of column A

continue comparing and replacing to end of column C

Thank you

Peter

 

[Jacob Skaria]

Hi Peter

Solution 1: Using function (This will provide the value in column D)
Paste this formula in D1...... =VLOOKUP(C1,A$1:B$5,2,FALSE)
Adjust B$5 to the number of rows you have in column A and B
Drag the same formula to the other cells in Column D.

Solution 2: Macro. Launch VBE using Alt+F11. On the left treeview, right
click 'This Workbook' --> Insert--> Module . Paste the below code. Goto
workbook. Paste your values in column C. Run Macro1 from Tools-->Macro

If this post helps please click Yes
---------------
Jacob Skaria


Sub Macro1()

Dim intRowA As Integer
Dim intRowC As Integer

intRowC = 1
Do While Range("C" & intRowC) <> ""
intRowA = 1
Do While Range("A" & intRowA) <> ""
If Range("C" & intRowC) = Range("A" & intRowA) Then
Range("C" & intRowC) = Range("B" & intRowA)
Exit Do
End If
intRowA = intRowA + 1
Loop
intRowC = intRowC + 1
Loop

End Sub

 

[Sheeloo]

You can not check the value in C1 to update C1...

Use this in D1
=INDEX($B$1:$B$20,MATCH(C1,$A$1:$A$20))
and copy down... it will give you what you want.
Replace 20 with the last row in your data

In Excel 2007
=INDEX(B:B,MATCH(C1,A:A))
works... try this in Excel 2003 and let us know whether it works.

 

[Peter Stone]

Both of Jacob's solutions worked (using either the function or the macro).

Sheeloo's solutions worked sometimes (there were correct replacements mixed
with incorrect repeating replacements).

Thank you both for taking the time to help.

Peter

 

[Sheeloo]

Peter,

I missed the fourth parameter in MATCH...

Pl. test with

=INDEX($B$1:$B$20,MATCH(C1,$A$1:$A$20))

This may also work....
=INDEX(B:B,MATCH(C1,A:A,0))

You may not need this but this will help someone else...

 

[Peter Stone]

FYI
Sheeloo: your second solution
=INDEX(B:B,MATCH(C1,A:A,0))
works fine in Excel 2003

the first solution
=INDEX($B$1:$B$20,MATCH(C1,$A$1:$A$20))
has problems with missing matches (it puts a value instead of flagging them
"#N/A" and maybe also has problems when column A isn't sorted).

 

[Sheeloo]

I can't believe I made the same mistake again (corrected in one place but not
the second)... Missed the 0 again...

You have identified the problem...

I should have given you
=INDEX($B$1:$B$20,MATCH(C1,$A$1:$A$20),0)
and not
=INDEX($B$1:$B$20,MATCH(C1,$A$1:$A$20)) as I asked you to do, TWICE :-(