maintain pricelist with price changes

[Guest]

Hi there,

Using a macro to update values that changed;

In a workbook (PRICELIST) I keep multiple sets of three columns holding data;
Interesting now is the second and third column which hold product and price.

A separate workbook (PRODUCT MANAGMENT) holds the products and prices for
changed products only, but separated with some more colmns in between prod-no
and price. (description/weight/size etc) The later file has management of the
prices and I want to update the first workbook (PRICELIST) with the changed
prices from the second file (PRODUCT MANAGEMENT)
There may be only three lines (three changes) or 100 lines depending on the
volume of changes.
Products starting cell B7 (due to headings etc) and prices starting H7

I want PRODUCT MANGEMENT to update PRICELIST with the PRICE.
Could a macro help here? And how?
Nowadays I manually do finds on productnumber and type the new price behind
it.

Thanks for helping out

 

[Dave Peterson]

Personally, with this kind of stuff, I wouldn't use a macro to just update the
prices in place. In fact, I don't think I'd use a macro at all.

I'd insert a couple of new columns (temporarily) in the Pricelist
workbook--adjacent to the current prices.

The first column would contain an =vlookup() to retrieve any price change from
the Product management workbook:

=VLOOKUP(A2,'[product management.xls]Sheet1'!$B:$H,7,FALSE)

This will return an #n/a if the product key doesn't find a match or it will
return the new price from that product management worksheet.

And drag down.

Then in the next inserted column, I'd compare the old price with the new price.

Say that returned value was in column C, then I'd use this formula:

=if(isna(c2),"No price on PM",if(b2=c2,"No change","Change"))

Then I'd filter on that column to look at the results.

I'd also spend some time in the PM workbook. Make sure that there are matches
for product codes and no duplicates codes--with different prices.

If I had to "macro-ize" this, about the only thing I'd do is insert the extra
columns, add the formulas and apply the filter. The checking would still be
done manually (well, for me anyway).

(From someone who worked in product management and learned not to trust those
people updating price lists. <vbg>.)

 

[Guest]

Dave,

Appreciate the approach and result, apart from the manual checking!!

Leo

Personally, with this kind of stuff, I wouldn't use a macro to just update the
prices in place. In fact, I don't think I'd use a macro at all.

I'd insert a couple of new columns (temporarily) in the Pricelist
workbook--adjacent to the current prices.

The first column would contain an =vlookup() to retrieve any price change from
the Product management workbook:

=VLOOKUP(A2,'[product management.xls]Sheet1'!$B:$H,7,FALSE)

This will return an #n/a if the product key doesn't find a match or it will
return the new price from that product management worksheet.

And drag down.

Then in the next inserted column, I'd compare the old price with the new price.

Say that returned value was in column C, then I'd use this formula:

=if(isna(c2),"No price on PM",if(b2=c2,"No change","Change"))

Then I'd filter on that column to look at the results.

I'd also spend some time in the PM workbook. Make sure that there are matches
for product codes and no duplicates codes--with different prices.

If I had to "macro-ize" this, about the only thing I'd do is insert the extra
columns, add the formulas and apply the filter. The checking would still be
done manually (well, for me anyway).

(From someone who worked in product management and learned not to trust those
people updating price lists. <vbg>.)

Hi there,

Using a macro to update values that changed;

In a workbook (PRICELIST) I keep multiple sets of three columns holding data;
Interesting now is the second and third column which hold product and price.

A separate workbook (PRODUCT MANAGMENT) holds the products and prices for
changed products only, but separated with some more colmns in between prod-no
and price. (description/weight/size etc) The later file has management of the
prices and I want to update the first workbook (PRICELIST) with the changed
prices from the second file (PRODUCT MANAGEMENT)
There may be only three lines (three changes) or 100 lines depending on the
volume of changes.
Products starting cell B7 (due to headings etc) and prices starting H7

I want PRODUCT MANGEMENT to update PRICELIST with the PRICE.
Could a macro help here? And how?
Nowadays I manually do finds on productnumber and type the new price behind
it.

Thanks for helping out

 

[Dave Peterson]

I never liked the manual checking, either. But with something as sensitive as
prices, I thought the time and effort were worth it.

I guess if you expected each change to be within a percentage or always up
(prices hardly ever go down???), you could include that in your check (maybe
another column with percent increase/decrease???).

Numbers are just too easily entered incorrectly.

Dave,

Appreciate the approach and result, apart from the manual checking!!

Leo

Personally, with this kind of stuff, I wouldn't use a macro to just update the
prices in place. In fact, I don't think I'd use a macro at all.

I'd insert a couple of new columns (temporarily) in the Pricelist
workbook--adjacent to the current prices.

The first column would contain an =vlookup() to retrieve any price change from
the Product management workbook:

=VLOOKUP(A2,'[product management.xls]Sheet1'!$B:$H,7,FALSE)

This will return an #n/a if the product key doesn't find a match or it will
return the new price from that product management worksheet.

And drag down.

Then in the next inserted column, I'd compare the old price with the new price.

Say that returned value was in column C, then I'd use this formula:

=if(isna(c2),"No price on PM",if(b2=c2,"No change","Change"))

Then I'd filter on that column to look at the results.

I'd also spend some time in the PM workbook. Make sure that there are matches
for product codes and no duplicates codes--with different prices.

If I had to "macro-ize" this, about the only thing I'd do is insert the extra
columns, add the formulas and apply the filter. The checking would still be
done manually (well, for me anyway).

(From someone who worked in product management and learned not to trust those
people updating price lists. <vbg>.)

Hi there,

Using a macro to update values that changed;

In a workbook (PRICELIST) I keep multiple sets of three columns holding data;
Interesting now is the second and third column which hold product and price.

A separate workbook (PRODUCT MANAGMENT) holds the products and prices for
changed products only, but separated with some more colmns in between prod-no
and price. (description/weight/size etc) The later file has management of the
prices and I want to update the first workbook (PRICELIST) with the changed
prices from the second file (PRODUCT MANAGEMENT)
There may be only three lines (three changes) or 100 lines depending on the
volume of changes.
Products starting cell B7 (due to headings etc) and prices starting H7

I want PRODUCT MANGEMENT to update PRICELIST with the PRICE.
Could a macro help here? And how?
Nowadays I manually do finds on productnumber and type the new price behind
it.

Thanks for helping out