I have a worksheet with 15 columns, one of the columns contains Part Numbers
and another column contains the cost of the associated PN. I need to update
the cost information based on a worksheet received weekly from costing. This
new worksheet only has 4 columns with updated costs, but only for PNs that
have new cost. How do I update worksheet "A" with the data from "B" without
having to search some 3000 rows?

example:
wks"A":
colA colD colE
A231 $45 apples
A452 $35 pears
B789 $23 bananas


wks"B"

A452 $32

In a 'helper' column, you can use a combination of the 'vlookup' and
'iserror' functions as I did in this formula:
=IF(ISERROR(VLOOKUP(A3,newcost,2,FALSE)),B3,VLOOKU P(A3,NEWCOST,2,FALSE)).
Where A3 contains your part number, 'newcost' is a table name I created to
refer to the data you would receive from your costing department, the '2'
refers to the cost column in the 'newcost' table, and B3 refers to the cost
of your original part number.

The end result is that if the vlookup function does not find a match in your
table of new costs it simply displays the old cost, if it finds a match it
displays the new cost. After you are done you can copy and paste the updated
coss as values.
--
T Tipsy


"BRB" wrote:

I have a worksheet with 15 columns, one of the columns contains Part Numbers
and another column contains the cost of the associated PN. I need to update
the cost information based on a worksheet received weekly from costing. This
new worksheet only has 4 columns with updated costs, but only for PNs that
have new cost. How do I update worksheet "A" with the data from "B" without
having to search some 3000 rows?

example:
wks"A":
colA colD colE
A231 $45 apples
A452 $35 pears
B789 $23 bananas


wks"B"

A452 $32

Terry, thanks. This helps.

But is there a way to write a macro to do this update? I would like to avoid
toomany manual steps. Thanks again.

"Terry Tipsy" wrote:

In a 'helper' column, you can use a combination of the 'vlookup' and
'iserror' functions as I did in this formula:
=IF(ISERROR(VLOOKUP(A3,newcost,2,FALSE)),B3,VLOOKU P(A3,NEWCOST,2,FALSE)).
Where A3 contains your part number, 'newcost' is a table name I created to
refer to the data you would receive from your costing department, the '2'
refers to the cost column in the 'newcost' table, and B3 refers to the cost
of your original part number.

The end result is that if the vlookup function does not find a match in your
table of new costs it simply displays the old cost, if it finds a match it
displays the new cost. After you are done you can copy and paste the updated
coss as values.
--
T Tipsy


"BRB" wrote:

I have a worksheet with 15 columns, one of the columns contains Part Numbers
and another column contains the cost of the associated PN. I need to update
the cost information based on a worksheet received weekly from costing. This
new worksheet only has 4 columns with updated costs, but only for PNs that
have new cost. How do I update worksheet "A" with the data from "B" without
having to search some 3000 rows?

example:
wks"A":
colA colD colE
A231 $45 apples
A452 $35 pears
B789 $23 bananas


wks"B"

A452 $32

You probably can, but macros is an area I'm not to comfortable with yet.
Hopefully someone else will respond with a better option for you. Good luck.
--
T Tipsy


"BRB" wrote:

Terry, thanks. This helps.

But is there a way to write a macro to do this update? I would like to avoid
toomany manual steps. Thanks again.

"Terry Tipsy" wrote:

In a 'helper' column, you can use a combination of the 'vlookup' and
'iserror' functions as I did in this formula:
=IF(ISERROR(VLOOKUP(A3,newcost,2,FALSE)),B3,VLOOKU P(A3,NEWCOST,2,FALSE)).
Where A3 contains your part number, 'newcost' is a table name I created to
refer to the data you would receive from your costing department, the '2'
refers to the cost column in the 'newcost' table, and B3 refers to the cost
of your original part number.

The end result is that if the vlookup function does not find a match in your
table of new costs it simply displays the old cost, if it finds a match it
displays the new cost. After you are done you can copy and paste the updated
coss as values.
--
T Tipsy


"BRB" wrote:

I have a worksheet with 15 columns, one of the columns contains Part Numbers
and another column contains the cost of the associated PN. I need to update
the cost information based on a worksheet received weekly from costing. This
new worksheet only has 4 columns with updated costs, but only for PNs that
have new cost. How do I update worksheet "A" with the data from "B" without
having to search some 3000 rows?

example:
wks"A":
colA colD colE
A231 $45 apples
A452 $35 pears
B789 $23 bananas


wks"B"

A452 $32