how to search for multiple values in one excel sheet and copy it to another sheet

excel sqlbulkcopy

Question

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

Accepted Answer

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)



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why