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
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):
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:
This will return
FALSE every time that the product is found in the EOL list and
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:
(This assumes that the old price is stored in D2)