Master Price List update

[danturv]

Hi All

I have a master price list I can download in csv (appox 6000 lines) and I receive price updates from many suppliers in Excel. What would be the best way to to update prices where the product code matches between the two sheets? Sorry I am a bit excel rusty!

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not pictures) of your two sheets. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[RobP]

Hi,

assuming the csv data is on sheet1 (product code in COL A, associated price in COL B), and your master part numbers are all on sheet2 (again with product no in Col A and price in Col B - you could simply use a VLOOKUP like such in the price column of sheet2 to get the prices from sheet 1:

=VLOOKUP(A1,Sheet1!A:B,2,FALSE)

Cheers
Rob

 

[danturv]

Thanks for the responses and apologies for the vagueness of my initial post, it has been a while since doing anything meaningful with Excel and I hadn't thought it through properly!

Rob, I am not sure the VLOOKUP route will work as the price update list will only have some of the items from the master list. Is that correct? Ideally I would need to scan the product codes from the update list and apply the price updates to the relevant products on the master list, leaving the ones witout updates alone.

Here is a link to a sample file showing only the product code and price, there would normally be a lot more columns. The PriceUpdate sheet would be populated with any price updates from suppliers which we would need to update the relevant product prices on the MasterPrice sheet. The price update sheet would vary in the amount of products listed as per each supplier update. Hopefully this makes sense and you can help

pricelistupdate-sample.xlsx

Shared with Dropbox

www.dropbox.com

 

[mumps]

There are duplicate values in column A of the Master sheet. How do you want those values handled?

 

[mumps]

Give this macro a try:

Sub Updateprices()
    Application.ScreenUpdating = False
    Dim i As Long, srcWS As Worksheet, desWS As Worksheet, v1 As Variant, v2 As Variant, arr() As Variant, dic As Object, cnt As Long: cnt = 0
    Set srcWS = Sheets("PriceUpdate")
    Set desWS = Sheets("MasterPrice")
    v1 = srcWS.Range("A2", srcWS.Range("A" & Rows.Count).End(xlUp)).Resize(, 2).Value
    v2 = desWS.Range("A2", desWS.Range("A" & Rows.Count).End(xlUp)).Resize(, 2).Value
    Set dic = CreateObject("Scripting.Dictionary")
    For i = LBound(v1) To UBound(v1)
        dic.Add v1(i, 1), Nothing
    Next i
    For i = LBound(v2) To UBound(v2)
        If dic.exists(v2(i, 1)) Then
            desWS.Range("B" & i + 1) = v2(i, 2)
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

 

[danturv]

Amazing, thank you! I will give it a test in the morning and let you now. I hadn't spotted the duplicates though it looks like they shouldn't be there

 

[danturv]

Given it a test this morning and I must be doing something wrong as no changes are made, I am definitely quite rusty as I cant see what I am missing

 

[mumps]

Are you testing the macro using the sample file you posted?

 

[danturv]

Yes, it must be me doing something wrong as the code looks sound