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

[marreco]

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]

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]

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]

Hi
I'm try
[VBA]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
[/VBA]

[Trebor76]

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
 
    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]

Hi.
was wonderful, thank you!!

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

[Trebor76]

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

[marreco]

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]

Column D of Sheet2 is a lookup from Sheet1 so it can only be on columns B and C. Correct

[marreco]

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]

OK - try this:

Option Explicit

Sub Macro1()
 
    'Written by Trebor76
    'Visit my website 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]

was perfect!!

Thank you very much!

[Trebor76]

Thanks for letting us know and you're welcome

[marreco]

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.