Compare two datasheets and create a new one with modified and newinformation - Excel

[eleanor]

HI, this is my first post so please be gentle! I am fairly good in
Excel (as an amateur) and usually love the challenge of getting a
whizzy macro to work but I am really pushed for time and i know this
is easier than I am making it!

Table 1
Lot of information on products in it and lots of fields that can be
updated

Table 2
Contains some of the information from Table 1 as well as other
information added from different set of people

I want to create Table 3
Core set of information from Table 2 that is added too showing;
Updates to any existing products in Table 2 (from Table 1) but KEEPING
any information people have added
Any new products that have been added to Table 1 that are not in Table
2

I have tried to use a simple example below to show how these would be
compared and updated into TABLE 3 weekly
Table 1
ID - Fruit - Colour - Price - Quantity
15- Apple - Green - 10p - 100
16 - Orange- Orange- 10p - 100
17 - Plum - Purple - 20p - 50
18 - Pineapple - Yellow - 50p - 300

Table 2 -
ID - Fruit - Colour - price - quantity - Vitamins - From - texture
14 - Banana - Yellow - 20 - 300 - Vit A - Tesco - Squishy
15- Apple - Green - 10p - 100 - Vit B, D - Sainsburys - Hard
17 - Plum - Purple - 20p - 50 - Vit X, Greengrocer - Soft
18 - Pineapple - Brown - 50p - 300 - Tesco - Hard
16 - Orange- Silver - 0p - 20 - Vit C - Corner shop - Pips

Table 3 - This should compare Table 1 as the main source of certain
fields then update Table 2 where necessary fields have been modified
and add new records that are in Table 1 not in Table 2. However if
there is a match on ID in table 2 - the additional comments needs to
be preserved

Table 3
ID - Fruit - Colour - price - quantity - Vitamins - From - texture
16 - Orange- Orange- 10p - 100 - Vit C - Corner shop - Pips
17 - Plum - Purple - 20p - 50
18 - Pineapple - Yellow - 50p - 300 - Tesco - Hard
14 - Banana - Yellow - 20 - 300 - Vit A - Tesco - Squishy
15- Apple - Green - 10p - 100 - Vit B, D - Sainsburys - Hard
17 - Plum - Purple - 20p - 50 - Vit X, Greengrocer - Soft


Please help!!

 

[Don Guillett Excel MVP]

HI, this is my first post so please be gentle! I am fairly good in
Excel (as an amateur) and usually love the challenge of getting a
whizzy macro to work but I am really pushed for time and i know this
is easier than I am making it!

Table 1
Lot of information on products in it and lots of fields that can be
updated

Table 2
Contains some of the information from Table 1 as well as other
information added from different set of people

I want to create Table 3
Core set of information from Table 2 that is added too showing;
Updates to any existing products in Table 2 (from Table 1) but KEEPING
any information people have added
Any new products that have been added to Table 1 that are not in Table
2

I have tried to use a simple example below to show how these would be
compared and updated into TABLE 3 weekly
Table 1
ID - Fruit - Colour - Price - Quantity
15-  Apple - Green - 10p - 100
16 - Orange- Orange- 10p - 100
17 - Plum - Purple - 20p - 50
18 - Pineapple - Yellow - 50p - 300

Table 2 -
ID - Fruit - Colour - price - quantity - Vitamins - From - texture
14 - Banana - Yellow - 20 - 300 - Vit A - Tesco - Squishy
15-  Apple - Green - 10p - 100 - Vit B, D - Sainsburys - Hard
17 - Plum - Purple - 20p - 50 - Vit X, Greengrocer - Soft
18 - Pineapple - Brown - 50p - 300 - Tesco - Hard
16 - Orange- Silver - 0p - 20 - Vit C - Corner shop - Pips

Table 3 - This should compare Table 1 as the main source of certain
fields then update Table 2 where necessary fields have been modified
and add new records that are in Table 1 not in Table 2. However if
there is a match on ID in table 2 - the additional comments needs to
be preserved

Table 3
ID - Fruit - Colour - price - quantity - Vitamins - From - texture
16 - Orange- Orange- 10p - 100 - Vit C - Corner shop - Pips
17 - Plum - Purple - 20p - 50
18 - Pineapple - Yellow - 50p - 300 - Tesco - Hard
14 - Banana - Yellow - 20 - 300 - Vit A - Tesco - Squishy
15-  Apple - Green - 10p - 100 - Vit B, D - Sainsburys - Hard
17 - Plum - Purple - 20p - 50 - Vit X, Greengrocer - Soft

Please help!!

In order to properly see your layout
"If desired, send your file to dguillett @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."