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

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.

Hi John, thanks for your reply. I actually posted this in 2 forums as I wasn't sure which forum was more appropriate. It was kindly solved by someone on the following post although if you do have any ideas on automating the 'daily sales' file that would be great.

http://www.excelforum.com/excel-form...html?p=2908581

The structure of my uploaded file is correct. Although I've blanked out the other columns within the worksheets, the relevant info shown on there is in the correct columns.
Many thanks
David

No, as I have already explained I posted it in 2 forums because I wasn't sure which one was more appropriate: excel programming vba coding etc; or excel formuas and functions. In my mind if I posted it in one of them and it was the wrong one, then it would get ignored. If I was doing as you suggested and abusing the system in order to get it resolved quicker, then I would have posted it in all forums, and then posted it again the next day etc.

There's absolutely no need to take that poor and patronising attitude in your reply to me!

Hello barnesy.

You have, as jaslake pointed out, inadvertently broken one of the forum rules. I fail to see the "poor and patronizing attitude" you speak of. After all, jaslake suggested that you PM him with a link to any new thread you start so that he could look at it with a view towards helping you. Please look at it from the view of those volunteering to help. When duplicate posts are made there is a chance they won't be noticed (as in this case) and that could lead to different volunteers working on the same issue at the same time unaware of each other's work. Hardly an appropriate situation and that is why there is a rule in place to prohibit posting duplicate threads. The following is that rule:

This is a duplicate post and as such does not comply with Rule 5 of our forum rules. This thread will now be closed, you may continue in your other thread.

Thread Closed.