How to find duplicates from two workbooks (if duplicate add new value in the list)

[megaexcel]

Hi,

I really need help. Here is my case.
Each month I will have an "old list" and a "new list", so two separate workbooks.

"old list" has column A, B, C, D, E.
"new list" has column A, B, C, D, E, F, G. or just the "new list" has column A, B, C, D, E.

Column A is a list containing words.
Column B are value (numbers).

My two workbooks are opened. I would like to find the duplicates i(column A) in the new list from the old list and if there is a duplicate in the new list I would like that duplicate to be highlighted and the value in column B to replaced the value in "old list" column B.

For instance if "old list" A3 is Lab and in the "new list" Lab is in A2 I would like A2 in the new list to be highlighted and the value corresponding in B2 10(-1) to be paste in B3 in the "old list"

Is it confusing? I attached four excel files to visualize what I am looking for. It's easier when you look at it.

I will need to do it every month and the list has a lot of data. I am looking for a macro or a good formulas to do the work.

I appreciate any help. Many thanks.

[hensucker]

Hello firstly welcome to the forums.
Now your request becomes very difficult and somewhat challengable, checking for duplicates accross several workbooks in this case 2 is not the best option at all i had this puzzle a few years back, my workaround was to have everything in one workbook and check across multiple sheets instead.
Now if this is something you cant do then your best bet is a database back end and excel front end. You would add or use your data to be found in excel and have it connect to the database and check for existing matches, you can then choose how it handles that data.

This will be a timely and costly project and will not come free if you need a full build.
I suggest you specifically ask for certain parts and we can give advice and small amounts of code to help you.

But please think on what i have said and decide what you think would be the best outcome the we can go from there.