Importing updated prices

[cornjoelio]

Hi there, some help please. I have tried searching for an answer but I can't seem to find the right search terms to return the answer I need.

I have two tables, for example:

Price List
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Product ID[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]10001[/TD]
[TD]$1.30[/TD]
[/TR]
[TR]
[TD]10002[/TD]
[TD]$5.10[/TD]
[/TR]
[TR]
[TD]10003[/TD]
[TD]$0.85[/TD]
[/TR]
[TR]
[TD]10004[/TD]
[TD]$0.99[/TD]
[/TR]
[TR]
[TD]10005[/TD]
[TD]$2.00[/TD]
[/TR]
[TR]
[TD]10006[/TD]
[TD]$9.99[/TD]
[/TR]
</tbody>[/TABLE]













Price Updates
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Product ID[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]10002[/TD]
[TD]$6.00[/TD]
[/TR]
[TR]
[TD]10003[/TD]
[TD]$1.00[/TD]
[/TR]
[TR]
[TD]10006[/TD]
[TD]$12.99[/TD]
[/TR]
</tbody>[/TABLE]








I need to do this on a much larger scale, e.g. 200 price changes out of 18000 products. Is there a function that will easily allow me to update the prices in Price List with those that appear in Price Updates?

My amateur workaround involved creating a new column in Price List called Price Import with which to VLOOKUP the prices, filtering out the blanks, copying over the new prices into the Prices column and then deleting the Price Import column. I'd be glad to know of a more advanced solution!

Many thanks
Joel

 

[DHayes]

Hi,
I think this what you may be looking for.
In your price import column Put this formula in.

=IFERROR(INDEX('Price Updates'!$A$2:$B$4,MATCH(A2,'Price Updates'!$A$2:$A$4,0),2),B2)

 

[Scott T]

You can run the macro to update the prices. This assumes that the price list has headers in row 1 with product id in column A and price in B and the same with the new price list. Change the sheet names and ranges to match your data.

Sub priceupdate()
Dim lr As Long
Dim wsoldp As Worksheet
Dim wsnewp As Worksheet
Set wsoldp = Sheets("Sheet1")[COLOR=#00FF00] 'sheet with price list[/COLOR]
Set wsnewp = Sheets("sheet2") [COLOR=#00FF00]'sheet with new prices[/COLOR]
lro = wsoldp.Cells(Rows.Count, 1).End(xlUp).Row
lrn = wsnewp.Cells(Rows.Count, 1).End(xlUp).Row

For x = 2 To lro 'price list starts in row 2 with headers in row 1. If list starts in different row then change 2 to the row it starts in
    Change = Application.Index(wsnewp.Range("B2:B" & lrn), Application.Match(wsoldp.Cells(x, 1), wsnewp.Range("A2:A" & lrn), 0))
    If IsNumeric(Change) Then
        [COLOR=#00FF00]'new price[/COLOR]
        wsoldp.Cells(x, 2) = Change
    Else
        [COLOR=#00FF00]'no new price[/COLOR]
    End If
Next x
End Sub

 

[Fennek]

Hi,

a pritty fast method should be to load table 1 in a dictionary and then replace the prices (items) with table 2.

regards

 

[cornjoelio]

Thanks for all your suggestions. Will give them a go!