Updating lists
 

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

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 wrote:

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

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" wrote in message
...
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 wrote:

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

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,She et2!A:B,2,0))
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Prixton" wrote in message
...
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" wrote in message
...
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 wrote:

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

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).



Prixton wrote:

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" wrote in message
...
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 wrote:

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

--

Dave Peterson