| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare and replace if equal
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Compare and replace if equal
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Compare and replace if equal
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 "Jacob Skaria" wrote: 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Compare and replace if equal
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" wrote: 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 "Jacob Skaria" wrote: 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Compare and replace if equal
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" wrote: 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" wrote: 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 "Jacob Skaria" wrote: 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Compare and replace if equal
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 :-( 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" wrote: 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" wrote: 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 "Jacob Skaria" wrote: 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Compare and replace if equal
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" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| compare cells and sum if equal | Excel Worksheet Functions | |||
| Compare two file/colunms, hide row not does not equal list. | New Users to Excel | |||
| compare two text and if equal copy adjacent data | Excel Worksheet Functions | |||
| Compare two dates and if they are equal | Excel Discussion (Misc queries) | |||
| How to add equal values and than replace them with their sum? | Excel Worksheet Functions | |||
Hybrid Mode
