# Updating data in 1 column from another

#### Zataniel

##### New Member
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.

### Excel Facts

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

#### Gerald Higgins

##### Well-known Member
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.

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

#### lenze

##### Legend
hi and welcome to the board!!!
Maybe a macro
Code:
``````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

##### New Member
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

##### Well-known Member

Sorry, there was a mistake in my formula, should have been
Code:
``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.

#### Zataniel

##### New Member
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

##### New Member

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

##### MrExcel MVP, Moderator
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

##### Well-known Member
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

##### New Member
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:

Replies
1
Views
138
Replies
1
Views
168
Replies
6
Views
313
Replies
7
Views
183
Replies
8
Views
241

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,871
Messages
5,834,150
Members
430,261
Latest member
quangtinator

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back