Match and replace cell from another sheet

[gravy679]

Question: I have two sheets named Sheet1 and Sheet2. Sheets looks like this:

Sheet1
A B
1 John Smith
2 Jane Doe
3 Joe Smo
4 Jackie Chan

Sheet2
A
John Smith
Joe Smo
Joe Smo
Joe Smo
Jane Doe
Bruce Lee

Here is the logic: If any cell in Sheet2 Column A = Any cell in Sheet1 Column B, change sheet2 column A cell value to sheet1 column A value (id).

Thanks...

 

[semsem]

Tom Urtis
can u please explain your first code?
it doesnt work here, i think excel does not recognize which one is each column

 

[Tom Urtis]

The code I posted does what gravy679 asked for in his very first post up top.

He had two lists.

One list was on Sheet1, comprising two columns (column A with a number, and column B with some text value).

The other list was on Sheet2, comprising just one column, that being column A, containing some text values similar in nature to those in column B on Sheet1.

What he wrote in his first post is what my code does:
"If any cell in Sheet2 Column A = Any cell in Sheet1 Column B, change sheet2 column A cell value to sheet1 column A value"

So, I programmatically evaluate every cell in column A of Sheet2, and try to find that same value in column B on Sheet1. If there is a match, then that cell's value is replaced by the number in column A of Sheet1 on the same row as the found identical value in column B of Sheet1.

I know the code works based on the situation that was outlined in the first post 2 years ago, so if it is not working for you, maybe you have your lists occupying different columns, and maybe on different sheet names.

Consider posting back with what list(s) you have on what sheet(s) and exactly what you want to do.

 

[semsem]

my situation is almost same, however my first table is in sheet1 in column P there are some values for example: "Michael" and in sheet2 column A has Michael and column B same row has Mike, i need the macro to check column B sheet2 for cells that match cells in column P sheet1 and replace them with the value in column B sheet2 same row of the value in column A