Updating sheet daily for Shopify

[Sydboss623]

My husband is starting a website on Shopify. The wholesaler who is supplying the merchandise will send an Excel sheet every day with their updated inventory list. We are supposed to upload this list every morning, which is simple.

However, he only wants to sell about a third of what they offer. He has copied only the items he wants into a new sheet, and the items he specifically does not into another.

My question is:. How do I take the new inventory sheet every day and use it to only update the items we wish to sell, without sorting through and filtering out 4,000 items every morning? I only have a basic understanding of Excel, but I feel like I should be able to apply some sort of template or if/then formula to only pull out the information I need.

 

[mrshl9898]

It may be simplest to do a VLOOKUP and delete the #N/A lines.

Is all this going to happen in the same workbook? What columns are details in? What are the sheet names?

 

[Sydboss623]

There are about 25 columns but I'm most concerned with the vendor and item columns. Some vendors we won't use at all. Some, we will only use a few products.

Right now I just have sheet 1 with the stuff we want to keep, and sheet 2 with the stuff we don't. Each day we will get a new sheet from the wholesaler that has all the items alphabetically by vendor then item name. I have to use that new sheet to update the website, but filter out everything that we don't sell. And update the inventory number of everything we do sell.

Ex. If we sell a speaker that the wholesaler had 4 of yesterday, and now they have 10 (we do drop shipping so we don't have our own inventory), then I need to update that line.

 

[mrshl9898]

Are the Item numbers unique? Or do you need to add the vendor number to make it unique?

 

[mrshl9898]

If unique then VLOOKUP, if you need both then add a row with itemnumber and supplier eg. =A2&B2 then use the vlookup.

You can either lookup from Sheet1 to the new sheet to look at quantities, or vlookup from the new sheet to Sheet1 to see if the item is listed.

Here's a simple tutorial on how to create the function.

https://www.guru99.com/excel-vlookup-tutorial.html