Updating lists

[Prixton]

Hi,
In our company we have a very long pricelist in an Excel-file. Once a week
we get a list with updated prices, also in Excel-format. Is there an easy
way to update our list with the prices from the updating list?

We have a Product ID that is the identification in both lists. However we do
not want our list to be updated with products that are not already in our
list.

Is it also possible to add information to a column "Modified date"?

Thanks in advance

 

[Dave Peterson]

There's a nice worksheet function that can be used to bring over values from
another worksheet when there's a unique key that can be matched on.

I'd insert a new column with a nice label to retrieve that newer value and then
use a formula like:

=vlookup(a1,sheet2!a:b,2,false)
to match up the key in A1 with sheet2's column A. It brings back column 2 for
that exact match.

Then you can compare the values to see if you really, really want to overwrite
your values. (I've never seen data come in that I would trust without
checking.)

=vlookup() requires that the key value in the table be in the leftmost column of
the range (doesn't have to be column A, though. It could be column E in a range
of E:G.)

If your key value is to the right of the new price, you can use =index(match()).

Debra Dalgleish has nice instructions on both these (with pictures!) at:
http://www.contextures.com/xlFunctions02.html
http://www.contextures.com/xlFunctions03.html

 

[Prixton]

Thank you, Dave!
I have used the VLOOKUP. However, When having used the VLOOKUP I get #N/A
when there is no match..
I have tried Copy/Paste special/values and after that Replace #N/A with ""
and after that I have pasted it into the original Price Column with Paste
special/Values and Skip blanks. It works when doing it manually, but when I
do it recording a macro it does not work. There I get the #N/A again.

I cannot find out what I am doing wrong and I have tried so many times.

Is there something you or someone else can help me with

 

[Ragdyer]

Using Dave's original formula as the basis, this would replace #N/A errors
with a null:

=IF(ISNA(MATCH(A1,Sheet2!A:A,0)),"",VLOOKUP(A1,Sheet2!A:B,2,0))
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

Thank you, Dave!
I have used the VLOOKUP. However, When having used the VLOOKUP I get #N/A
when there is no match..
I have tried Copy/Paste special/values and after that Replace #N/A with ""
and after that I have pasted it into the original Price Column with Paste
special/Values and Skip blanks. It works when doing it manually, but when I
do it recording a macro it does not work. There I get the #N/A again.

I cannot find out what I am doing wrong and I have tried so many times.

Is there something you or someone else can help me with

 

[Dave Peterson]

RagDyer gave you an alternate formula, but my guess about why your macro still
returned #n/a's is that you didn't convert to values correctly. And then you're
replace wouldn't work.

But that's just a wild guess.

If you can't get your code working, you may want to post the relevant portions
(not the workbook--just the code).