-1

Good day

I have a huge problem

I have an online store and need to update products and prices I have an excel sheet with all of the current products online and recieved a new pricelist Now the problem is that Im dealing with over 5000 products and I cant update them one by one

What I need now is code to copy the prices from the one sheet and move it to the coresponding SKU/product code on the other sheet

I also have a list of the EOL products which I need to remove from the current Excel sheet So I need a code to find the product code/SKU on the EOL sheet and then delete the whole row with the corresponding SKU on the other sheet

Thank you

pnuts
  • 58,317
  • 11
  • 87
  • 139
Francois Vosloo
  • 3
  • 1
  • 2

1 Answers1

1

Assuming that your products have a unique ID that is available in all three tables, this should be an easy task that does not require any programming.

1. Update prices

All you need is a VLOOKUP formula. I assume your list with all products and prices have the product ID in column A and the (old) price in column B. I your new pricelist has the same format. Then place the following formula in cell B2 (where you currently have your old price):

=VLOOKUP(A2,[NewPriceFile]Sheet1!$A:$B,2,0)

This will return you the new price.

2. Delete old products Here I assume you simply have a list of EOL product IDs. Place the following formula in column C:

=ISERROR(MATCH(A2,[EOLFile]Sheet1!$A:$A,0))

This will return FALSE every time that the product is found in the EOL list and TRUE else.

Now you only need to apply an AutoFilter to the full list (Data tab->Sort & Filter->Filter) and filter column C for FALSE. Select all rows and delete them (Ctrl--).

In the same way you can also check column B if all products were found. In case no product was found, #N/A!is shown, which you can filter. Alternatively, you can combine your formula with an IFERROR and use the old price if no new price was found:

=IFERROR(VLOOKUP(A2,[NewPriceFile]Sheet1!$A:$B,2,0),D2)

(This assumes that the old price is stored in D2)

Peter Albert
  • 16,917
  • 5
  • 64
  • 88
  • Didnt have to use the Iferror, when I did the =vlookup formula, the products that were not on the new pricelist where it didn't find a price it just showed NA so I know that that product is then EOL – Francois Vosloo Feb 25 '13 at 14:51
  • Just one last thing, now there are new products on the new pricelist, I want to know which are new and which not. (the new ones will not be in the old pricelist) how will I go about doing that – Francois Vosloo Feb 25 '13 at 14:53
  • Figured it out, Thank you very much – Francois Vosloo Feb 25 '13 at 15:27