Stock Inventory from Daily Sales, when a single product has various descriptions

[barnesy1977]

Hello all,

OVERVIEW
I'm trying to compile a master stock spreadsheet that is updated on a daily basis by running a macro (or by whatever is best), which deducts sales from my ebay store from the previous day. The twist/added complication is that a single product could have several different titles-descriptions within my ebay store, so I need the macro to search several columns within the spreadsheet to find the correct product, and then reduce the quantity accordingly.

DETAILED EXPLANATION:
I run an ebay store with quite a large inventory of different products. Historically I've always kept track of my stock through the ebay inventory system (Seller Manager Pro). This is quite simple to do as I simply add stock to it when I have a delivery from my supplier, and any sales automatically deduct from the inventory system.

Over the last 12 months, the ebay system has changed by introducing multi-product listings. This now means that on a single listing I can have a number of different products (typically 10 to 20). Inevitably this means that a single product (lets say a large red rug) can be advertised on several different ebay listings of mine...1) on it's own single listing; 2) on a 'large rug' multi-product listing; 3) on a 'red rug' multi-product listing etc etc.

The problem is, that there is no facility on the ebay system, that enables you to 'link' sales of the same item (the large red rug) from 'all' of the listings it is advertised on, back to one place within the stock inventory. Until now, every week, I've been counting the sales of each product from all the listings it is on, and then manually deducting that number from the one place on the inventory that keeps the live stock for that product. It's fairly time consuming but it's worked so far. Only now, I have so many multi listings it's becoming an extremely difficult task to cope with.

What I'm hoping to achieve here, is to now keep my live stock on a spreadsheet, and each morning downloading from ebay, my sales from the previous day, and run a macro (or whatever is best) to deduct those quantities from the master stock spreadsheet (typically 30-100 sales per day). The macro or formula will need to search a range of columns and rows before it locates the correct product. It would then deduct the quantity of that sale from the stock quantity.

I've attached a spreadsheet with 2 worksheets on. The 1st worksheet would effectively be my master stock spreadsheet. The 2nd worksheet would be a summary of Daily Sales from the previous day (I would paste the sales into this worksheet each day, as the filename on the downloaded version would change every day so could be difficult.)

I've had a little bit of experience with vlookup etc, but I just can't get my head round how to ask the spreadsheet or compile a macro to (in this instance) search columns C-H for rows 4-34 in the Stock Worksheet, to match/lookup each entry in Column N of the Daily Sales worksheet, and then reduce the quantity by the appropriate amount. The last thing I would like to include is that when the Macro runs, it updates a field to say when it was last run (in case I forget whether I've done it or not!)

I'm not sure if it makes a difference, but the Daily Sales worksheet, does not 'summarise' all sales of the same item in one row - it just lists each transaction as its own entry. You'll notice I've added the same product twice on 2 separate lines just to test this.

Any advice, help or suggestions would be much appreciated. Or if there is an easier, more sensible way that's totally different too. I'm happy to pay for someone's time here - if that's the done thing - I'm not looking for a freebie necessarily!

Many thanks in anticipation.