How to update list data

[tom]

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

 

[Bernd P]

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

 

[tom]

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]

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

 

[tom]

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]

Hello Tom,

You are welcome. Thanks for your feedback.

Regards,
Bernd