Replace value comparing range with another range i

Question

I have data in two columns in excel, data in "column a" need to replace with another single value lets say "Newdata" matching data in "column b", for example, 4001024 and 4000521 in column b are also present in column a so I need to replace those values in column a with "newData".

the sample data and output is as bellow. I need to look all values of column B as well, column b contains aroudn 1000 entries and column a contains more than 5000 entries

original data expected output column a column b column a column b 4000520 4001024 4000520 4001024 4000520 4001204 4000520 4001204 4000520 4002475 4000520 4002475 4000521 4002477 newData 4002477 4000521 4002517 newData 4002517 4000521 4003062 newData 4003062 4000521 4000521 newData 4000521 4000521 newData 4000521 newData 4000521 newData 4000522 4000522 4000522 4000522 4001024 newData 4001024 newData

Answer 1

I would create a helper column in column C and use this formula in cell C2 and drag it down:

=IF(ISERROR(VLOOKUP(A2,B:B,1,FALSE)),A2,"NewData")

If the value from column A is found in Column B, then it will be replaced with "NewData", otherwise it will just show up as the original value.

Then you can copy column C and paste values over the original column A.

Answer 2

If you use VBA, the code could be something like this:

Sub newdata()

Dim awb As Workbook
Dim ws As Worksheet

Dim a_lastrow As Integer     'last row of column A
Dim b_lastrow As Integer     'last row of column B

    Set awb = ThisWorkbook
    Set ws = awb.Worksheets("Sheet1")     'change "Sheet1" to the name of your sheet

    With ws

        a_lastrow = .Range("A100000").End(xlUp).Row
        b_lastrow = .Range("B100000").End(xlUp).Row

        For r = 1 To a_lastrow

            If Application.WorksheetFunction.CountIf(.Range("B1:B" & b_lastrow), .Range("A" & r).Value) > 0 Then
                .Range("A" & r).Value = "newData"
            End If

        Next r

    End With

    MsgBox ("done")

End Sub

Post this code into a separate module. Change "Sheet1" to the name of your sheet.