Updating data in 1 column from another

Zataniel

New Member
Joined
Jun 27, 2008
Messages
5
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.
 

Some videos you may like

Excel Facts

Links? Where??
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
Joined
Mar 26, 2007
Messages
9,115
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
Joined
Feb 18, 2002
Messages
13,690
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
Joined
Jun 27, 2008
Messages
5
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
Joined
Mar 26, 2007
Messages
9,115

ADVERTISEMENT

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.

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

Zataniel

New Member
Joined
Jun 27, 2008
Messages
5
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
Joined
Jun 27, 2008
Messages
5

ADVERTISEMENT

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
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Mar 26, 2007
Messages
9,115
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
Joined
Jun 27, 2008
Messages
5
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. :biggrin: :pray:
 

Watch MrExcel Video

Forum statistics

Threads
1,122,715
Messages
5,597,726
Members
414,169
Latest member
Preston_Cleric

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top