Updateing Master File Pricing

[phjohnso]

I'm starting here because I'm sure that everyone here has way more Exce
experience than I could ever dream of. Here is my task..

I have a master price file which I use to update my website tha
contains about 4000 products (give or take a few) and our vendor issue
a daily pricing update of just price changes.

Is there a way with excel that I can take the product number in th
master sheet and lookup the same part number on the price update shee
and then copy the updated price into the price field in the maste
sheet?

Any help would be greatly appreceated.

Sincerely

p

 

[Pete_UK]

To give you specific advice, we would need some more details of the
fields involved etc, so here's some general advice:

In your Master sheet, you could introduce a new column "Updated Price"
and use this to bring forward the price from the update sheet - if
there isn't one for a particular product code, then take the "Current
Price" from within the master sheet. Once you have the updates and have
applied the formula, then you could fix the values and use them to
overwrite "Current Price", thereby not having to change anything you do
to link to your website.

So, assuming you have been sent a file with an Update sheet in it. Open
it and your master file - adjust window sizes so that you can see both
on screen at the same time. Select the Update sheet and do a CTRL-drag
of the tab into your master file - this will copy the Update sheet into
your master file. You can close the window for the Update file, as you
don't need it anymore. (Incidentally, if the file only contains the
Update sheet, then it will be a bit quicker to just drag the tab into
the master file and then the Update file window will automatically
close).

In your "Updated Price" column on the master file, you would need a
formula along the lines of:

=IF(ISNA(VLOOKUP(A2,Update!A$2:B$500,2,0)),C2,VLOOKUP(A2,Update!A$2:B$500,2,0))

and copy this formula down your 4000 rows. Here I have assumed that
your Product_ID is in column A of both sheets and that each sheet has a
header row so that the data begins on row 2. I have also assumed that
your current price is in column C of the master sheet, and that your
update sheet has only 2 columns, with the price in column B - you will
have to amend these to suit your data.

Basically the formula says "if there is not an update price for this
Product ID then take the current price otherwise take the updated
price".

As mentioned earlier, you can fix these values in place (highlight all
the cells with the formula in, click <copy>, click Edit | Paste Special
| Values (check) | OK then <Esc>. This would then allow you to delete
the Update sheet, as it is no longer needed, and you can also copy the
values into column C to overwrite the Current Price (which is actually
yesterday's price at that point in time).

The next time you get an update of prices, you can repeat the
procedure.

Hope this helps.

Pete

 

[phjohnso]

I Thank you so much for your explanation but I'm lost reading formulas
perhaps if I detail exactly what my fields are you could tailor tha
formula for my application.

the layout below is a small shot of my masterfile - I understand fro
what you detailed that in this case the formula would be inserted int
the Customer_Price cells as these are what need to be updated based o
a Part_Number match from the daily price update file.

A B C
D
Product_DataPort_Id Part_Number List_Price Customer_Price
PRD-EEAD4561 41007 5.45 3.89
PRD-BD934562 41008 3.63 2.59
PRD-15AB6614 43048 3.49 2.49
PRD-12EE4663 43049 4.19 2.99
PRD-91146615 43051 12.59 8.99

The file sample below is from the daily price file update. The sk
below is the Part_Number above. The msrp below is what needs to b
updated in the Customer_Price cell above. I included the colum name
and their actul position A B C D to better detail for the formula.

Thanks so much in advance.

Sincerely

Paul Johnson

A B C
sku msrp your cost
43038 1.99 0.38
41007 1.99 0.31
43051 1.99 0.42
41008 1.99 0.4

 

[Pete_UK]

Your match will be on the Part_Number (master file, column B) and sku
(Update file, column A). You need to put this formula in E2 of the
master file (NOT in D2):

=IF(ISNA(VLOOKUP(B2,Update!A$2:B$500,2,0)),C2,VLOOKUP(B2,Update!A$2:B$500,2­,0))

The formula assumes that you have 500 items in your updated price list
- if you have more, then you must change the reference to 500 (i.e.
twice) to whatever number of updates you have. You can then copy the
formula down column E - double-click the fill handle (the small black
square in the botton right corner of the cursor with E2 selected) to
achieve this quickly.

Then you can fix the values in column E and copy/paste them to
over-write the values in column D, as detailed in my earlier response.
Column E can then be deleted.

Hope this explains things more clearly.

Pete