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.