Hi,

Below are two lists. Data is located in the first two columns. Each list has
over one thousand items. How do you write a program to update only the
prices in the old list using the data from the new list?


Old.xls New.xls

AA1 12.25 AA1 11.50
AA2 20.45 AA2 7.20
------ ------- ------ --------
------ ------- ------ --------
BB1 30.20 BB1 30.00
BB2 46.60 BB7 18.45
------ ------- ------ --------
------ ------- ------ --------
ZZ46 56.35 ZZ21 14.00

The columns are different in length and their items don't match completely.
Much appreciate if anyone can help.

TIA
tom

Hello Tom,

Open both files. Add a third column into Old.xls: Enter into C1:
=IF(ISERROR(MATCH(A1,[New.xls]Sheet1!$A:$A,0)),B1,[New.xls]Sheet1!B1)
and copy down.

Check values in column C thoroughly. If you are satisfied with the
result, copy their values (not formulas!) into column B and delete
column C.

Regards,
Bernd

Hi Bernd,

Thank you for your suggestion. I think your idea would work if the two lists
contain identical items and are listed in the same order. The new list I am
using has several items missing. As well, it has some new ones (which I'm
not interested in) and is different in its listing order compared to the old
list.

Regards,
tom
"Bernd P" wrote in message
...
Hello Tom,

Open both files. Add a third column into Old.xls: Enter into C1:
=IF(ISERROR(MATCH(A1,[New.xls]Sheet1!$A:$A,0)),B1,[New.xls]Sheet1!B1)
and copy down.

Check values in column C thoroughly. If you are satisfied with the
result, copy their values (not formulas!) into column B and delete
column C.

Regards,
Bernd

Hello Tom,

Sorry, I forgot the INDEX(MATCH()):
=IF(ISERROR(MATCH(A1,[New.xls]Sheet1!$A:$A,0)),B1,INDEX([New.xls]
Sheet1!$B:$B,MATCH(A1,[New.xls]Sheet1!$A:$A,0)))

Regards,
Bernd

Hi Bernd,

Aha! Everything now matches perfectly. What a wonderful function,
INDEX(MATCH()) - saves lots of work. Thanks Bernd that was great.

Regards,
Tom

"Bernd P" wrote in message
...
Hello Tom,

Sorry, I forgot the INDEX(MATCH()):
=IF(ISERROR(MATCH(A1,[New.xls]Sheet1!$A:$A,0)),B1,INDEX([New.xls]
Sheet1!$B:$B,MATCH(A1,[New.xls]Sheet1!$A:$A,0)))

Regards,
Bernd

Hello Tom,

You are welcome. Thanks for your feedback.

Regards,
Bernd