PDA

View Full Version : Solved: Find value in column "A" on one tab to another tab and replace the value in column "B



marreco
02-03-2013, 03:47 PM
Hi.

How do I look in "Sheet1" column "A"
if the value is equal to "Sheet2" column "A" replace the value from column "B" from "Sheet1" column by the value of "B" worksheet "Sheet2"

Trebor76
02-03-2013, 04:09 PM
Hi marreco,

The easiest way would be to use a blank column in Sheet1 (say column F) and but put this formula in the first data row (1 in your case)...

=IFERROR(VLOOKUP(A1,Sheet2!A:B,2,FALSE),B1)

...and then copy it down to the last row. After that copy and value the formulas and move the range back to column B.

HTH

Robert

marreco
02-03-2013, 04:14 PM
Hi.
Thank you!!
I have to replace the value!
as I do, in column "B" have value instead of formula?
teri to use formula I use that one more column.

in my original spreadsheet I have a lot of data.

marreco
02-03-2013, 04:30 PM
Hi
I'm try
Sub teste()
Sheets("Sheet1").Activate
[C1].Formula = "=IFERROR(VLOOKUP(A1,Sheet2!A:B,2,FALSE),B1)"
[C1].AutoFill Destination:=Range("C1:C106")
Sheets("Sheet1").Range("C1:C106").Copy
Sheets("Sheet1").Range("C1:C106").PasteSpecial (xlPasteValues)
Application.CutCopyMode = False
Columns(2).EntireColumn.Delete
End Sub

Trebor76
02-03-2013, 05:00 PM
Hi marreco,

Unless you're doing this repetitively there's no real need for a macro, but that said, this will do the job:


Option Explicit
Sub Macro1()

'Written by Trebor76
'Visit my website www.excelguru.net.au (http://www.excelguru.net.au)

Dim rngCell As Range

Application.ScreenUpdating = False

With Sheets("Sheet1")

For Each rngCell In .Range("B1", .Range("B" & Rows.Count).End(xlUp))

rngCell.Value = Evaluate("IFERROR(VLOOKUP(A" & rngCell.Row & ",Sheet2!A:B,2,FALSE),B" & rngCell.Row & ")")

Next rngCell

End With

Application.ScreenUpdating = True

End Sub

Regards,

Robert

marreco
02-03-2013, 05:15 PM
Hi.
was wonderful, thank you!! :rotlaugh:

can I ask one more question?
how do I adapt to a tab with 4 columns?:(

Trebor76
02-03-2013, 05:26 PM
Which column or columns do you want replaced? Columns B to D in Sheet2 for ID 2 are the same as in Sheet1 so there's nothing to replace :confused:

marreco
02-03-2013, 06:02 PM
Hi.
in my example is really equal.
but if "B" or "C" or "D" planiha2 is different then it is replaced on the "Sheet1".

Trebor76
02-03-2013, 07:36 PM
Column D of Sheet2 is a lookup from Sheet1 so it can only be on columns B and C. Correct :confused:

marreco
02-04-2013, 05:11 AM
Hi.
When I sent the file had these details wrong.

See image, the code procraria in column "A" sheet2 based on column "A" Sheet1, and return (replace) the columns "B, C, D" if the same ID found


I believe the same reasoning be the first code you sent me, but besides returning the column "B" also returns "C" and "D"

Trebor76
02-04-2013, 05:31 AM
OK - try this:


Option Explicit

Sub Macro1()

'Written by Trebor76
'Visit my website www.excelguru.net.au (http://www.excelguru.net.au)

Dim rngCell As Range
Dim lngMatchRowNum As Long

Application.ScreenUpdating = False

With Sheets("Sheet1")

For Each rngCell In .Range("A2", .Range("A" & Rows.Count).End(xlUp))

If (IsError(Evaluate("VLOOKUP(" & Sheets("Sheet1").Range(rngCell.Address(False, False)) & ",Sheet2!A:A,1,FALSE)"))) = False Then

lngMatchRowNum = Evaluate("MATCH(" & Sheets("Sheet1").Range(rngCell.Address(False, False)) & ",Sheet2!A:A,0)")

.Range("B" & rngCell.Row & ":D" & rngCell.Row).Value = Sheets("Sheet2").Range("B" & lngMatchRowNum & ":D" & lngMatchRowNum).Value

End If

Next rngCell

End With

Application.ScreenUpdating = True

MsgBox "All applicable records have now been updated."

End Sub

Regards,

Robert

marreco
02-04-2013, 05:56 AM
was perfect!!

Thank you very much!

Trebor76
02-04-2013, 01:49 PM
Thanks for letting us know and you're welcome ;)

marreco
02-17-2013, 02:16 PM
hi
Trebor76

you can adapt your code to run not just a line, but sima with two lines.

That is, go on sheet2, take line 2 and line 3.