Compare and copy new rows from Sheet2 to Sheet1 if conditions are met

I am a newbie to VBA and could get parts of what I wanted to do with vlookup, but not the complete solution. I will be gratefull for any help on this.

Essentially, I have a sheet of data (Sheet1 below) running into almost 1000 rows. Each month, I get an updated sheet (Sheet2) which has overlapping data with sheet1 (rows starting with N, O, P, Q), new data (rows starting with R, S) and data present in Sheet1 but not in Sheet2 (row starting with M in Sheet1). The columns "Item" and "Quantity" in sheet1 are populated by me and are blank in sheet2. The table below explains the structure.

Sheet1

Name	ID	Date	Item	Quantity
M	231	14/03/2001	egg	5
N	322	21/03/2002	bread	6
O	234	22/04/2001	tomato	45
P	542	16/01/1999	potato	25
Q	562	31/01/2003	carrot	14

Sheet2

Name	ID	Address	Date	Item	Quantity
N	322	address1	21/03/2002
O	234	address2	22/04/2001
P	542	address3	16/01/1999
Q	562	address4	31/01/2003
R	524	address5	16/02/1998
S	352	address6	02/01/2011

It gets tedious to update Sheet2 every month. Is it possible to merge the 2 sheets into a Sheet3 (like below) if Name, ID, Date in each row is an exact match?

Sheet3

Name	ID	Date	Item	Quantity
M	231	14/03/2001	egg	5
N	322	21/03/2002	bread	6
O	234	22/04/2001	tomato	45
P	542	16/01/1999	potato	25
Q	562	31/01/2003	carrot	14
R	524	16/02/1998
S	352	02/01/2011

Further, it will make my job much easier, if it was possible to

• identify rows only present in Sheet1 and add a tag "absent in new" in a new column
• identify rows only present in Sheet2 and add a tag "new" in the new column
• identify rows with matching data in Name, ID, Date in Sheet1 and Sheet2 and add a tag "current" in the new column.

Something like the table below:

Sheet3 (Desirable)

Name	ID	Date	Item	Quantity	Comments
M	231	14/03/2001	egg	5	Absent in New
N	322	21/03/2002	bread	6	Current
O	234	22/04/2001	tomato	45	Current
P	542	16/01/1999	potato	25	Current
Q	562	31/01/2003	carrot	14	Current
R	524	16/02/1998			New
S	352	02/01/2011			New

Many thanks for all your help and thanks a lot for reading through this so far.

Hi..

Can you attach a sample Workbook.. sounds like a job for the Scripting.Dictionary..

@venkat1926 - works brilliantly without a flaw or error. Many thanks for the effort and time. You have essentially replaced this program "http://www.ablebits.com/excel-lookup-tables/index.php".
Many thanks once again. I have combined steps one and two into a single macro.

Please Login or Register  to view this content.

One of the problems I have noticed with the above: If there is a discrepancy in the data between Sheet1 and Sheet2 in rows other than "Name" the errors are disregarded.

Can the above be modified to check for the "Name", "ID Number" and "Date" between sheet1 and Sheet2 and then to implement the following please-

• "current" if all three match in both sheets
• "Absent in new" if no matching rows for "Name", "ID Number" and "Date" of Sheet1 row in Sheet2
• "New" if only present in Sheet2

Thank you once again.