Updating data in 1 column from another

[Zataniel]

Hi.

I'm new to excel and I've been looking around but haven't found anything yet. I need a formula that let me change the value in column B with the value from column D. But to do the change I have to check that the value in column C is equal to the value in column A. If a value from column A is not present in column C (like 1002 in the example), the value in column B wont change. The values in columns A and C are alphanumeric, the values in columns B and D are numeric.

For example:

Column A Column B Column C Column D

1001 2 1001 4
1002 1 1003 5
1003 0 1004 1
1004 2



The columns A and B should end like this (Changed):

1001 4
1002 1
1003 5
1004 1

Thanks for any help that you can give me.

 

[Gerald Higgins]

Hi Zataniel and welcome to the board.

Your post is a bit vague - I'm not sure exactly what you want.

I THINK you are saying
Look at each value in Column A.
If that value is NOT found in Column C, leave the value in Column B unchanged.
If that value in Column A IS found in Col C, replace the value in Col B with the corresponding value in Col D.

Is that correct ?

If so, try this. Edit the ranges to suit what you've actually got, and copy the formula down the column as far as required.

Try it first in a new column, let's say Col E, and then if you are happy with the results, use Copy, Paste Special, Values to over-write what you have in Col B.

=if(iserror(vlookup(a1,c$1:c$100,2,false)),+b2,vlookup(a1,c$1:d$100,2,false))

 

[lenze]

hi and welcome to the board!!!
Maybe a macro

Sub ReDoMe()
Dim cl As Range
For Each cl In Range("$A$2:$A" & Range("$A$65536").End(xlUp).Row)
If Application.WorksheetFunction.CountIf(Range("C:C"),cl) > 0 Then Cells(cl.Row, 2) = Cells(cl.Row, 4)
Next cl
End Sub

lenze

 

[Zataniel]

Ty for the fast response.

Sorry for my vague explanation but you almost got it. The only problem is that instead of looking the value in column C, you use the value in column C and look for it in column A.

I used the formula that you gave me but all the cells values in the formula were changd with #REF!.

 

[Gerald Higgins]

Sorry, there was a mistake in my formula, should have been

if(iserror(vlookup(a1,c$1:c$100,2,false)),+b1,vlookup(a1,c$1:d$100,2,false))

but I don't think that would have given you your #ref error.

Please can you post your formula here, exactly as it is in your spreadsheet ?<!-- / message --><!-- sig -->

 

[Zataniel]

To Lenze:

I tried the macro, inverting the cells to invert the search as I need it. At first, I only used numerical values in the columns A and C (the columns involve in the search). As I said in my post those columns have alphanumeric values. When i used some combinations with letters, the macro didn't work. The macro change the value in B3 for the value in D3, even with different codes in the columns A and C. For example: the value 1 (in cell B4) was changed for the value 22 (in cell D4) but the value in cell A4 is 1105 while the value in cell C4 is MM-A55. Since the column C is shorter than column A (columns used for the search) the macro stop with the last value in the column C, leaving the rest of the values in column A without the change.

 

[Zataniel]

To Gerald Higgins:

The first formula:

=IF(ISERROR(VLOOKUP(C1,A$1:A$100,2,FALSE)),+B2,VLOOKUP(C1,A$1:D$100,2,FALSE))

I copied the formula in a different column and make the cells changes. Then I copied the formula from that cell to the cells in column B, the cells that I want to change.

After you wrote the 2nd formula I copied it directly to the cells in column B (the cells that are going to change) and invert the cell values (c --> a and a -->c.

=IF(ISERROR(VLOOKUP(C1,A$1:A$100,2,FALSE)),+B1,VLOOKUP(C1,A$1:D$100,2,FALSE))

A windows open up saying:

MS Excel cannot calculate the formula. Cell references in the formula refer to the formula's result, creating a circular reference. Try one of the following:

* If you accidentally created the circular reference, click OK.
* To display the circular reference toobar .....

After I pressed Ok it changed the value in the cell for a 0 (aligned to the left).

Sorry for all the problems I'm giving you guys, but I'm really a newbie. . I'm very grateful for all your help.

 

[Peter_SSs]

I am still a bit unsure about which column is checked for which values, but see if this is it.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Zataniel()<br>    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> iFound <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Range("A1", Range("A" & Rows.Count).End(xlUp))<br>        <SPAN style="color:#00007F">Set</SPAN> iFound = Columns("C").Find(What:=c.Value, LookAt:=xlWhole)<br>        <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> iFound <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>            c.Offset(, 1).Value = iFound.Offset(, 1).Value<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> c<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Gerald Higgins]

Zataniel, for the formula option, it looks like you're not following the instructions I gave you.

Try it first in a new column, let's say Col E

and then

use Copy, Paste Special, Values to over-write what you have in Col B.

So, to repeat.
Put the formula in a NEW column, one that is currently blank.
Copy the formula as far down the column as necessary.
Check the results.
If you're happy with the results, use COPY, PASTE SPECIAL, VALUES, to copy the values, NOT the formula itself, into column B.
DO NOT use ordinary COPY, PASTE because this will paste the formula, and as you have discovered, you don't want that.

 

[Zataniel]

Hi:

Peter_SSs, thank you for your help. I used your macro and it works. Gerald Higgins and Ienze, thank you for your help too. Really nice community, and very talentd.

Again, thank you very much. ray: