Help subtracting sales from order

[BrutalDawg]

Hello All,

I have spent the last few hours trying to find something similar to the issue I am having and currently have smoke coming out of my ears!

I have two sheets, one with orders the second with sales. I need help automatically subtracting from orders based on sales. I am trying to develop a script that would run through every sale and if location and item are exactly the same, subtract quantity sold from the quantity ordered to give a "new due" quantity.

I have attached a sample I created manually for the results tab. There will be sales with no order which is a non issue, but would be nice if those results could go to a different tab.

Thanks for any help!

[Leith Ross]

Hello BrutalDawg,

Your example workbook is a little short on details. From your post it sounds like this an inventory tracker. In which case, you will also need to match a Receiving Ticket to a PO Number along with a Sales Order Number to determine your Quantity on hand. You should probably think about flagging the Quantity when a certain level is reached for re-ordering.

[BrutalDawg]

Thanks for the response Leith,

I am actually trying to track over/under shipping to the order. I am trying to update the order to reflect what has already been shipped out.

What I think I am trying to get towards is a macro that goes row by row through sold sheet, and if Ship To Address1(D1) = !order Plant AND if !sold Item = !Order PartNumber then subtract !sold quantity (from same row) from !order quantity. (thus letting me know if I over or under shipped and by how many for next weeks order etc.)

I can change the headings to anything convenient, and could convert all sold to a negative quantity so sumif could work.

Thanks for your help!

[Leith Ross]

Hello BrutalDawg,

Do you still need help with this? I could not tell from your last post.

[BrutalDawg]

Hello Leith,

Yes please! Any advise at this point would be greatly appreciated.

[BrutalDawg]

I have uploaded a better example that I hope makes this a little easier to understand.

On the results tab everything highlighted in yellow is what would have changed. Items not highlighted in yellow there was no sale. The green and red formatting was for reference and is not required if to difficult. (would be ideal) column H, is illustrating, if this is even possible, once the order quantity reaches 0, for the script to keep searching sheet for next order with same criteria and subtract remain quantity. Anything will help, I am currently doing this manually over the weekend and generally 150+ sales per week and is very time consuming.
In simple terms, I am trying to have a script go through each sale row and subtract quantity from the order sheet if the plant and part number are the same. I will sort the order sheet based on when they are do so it removes from correct order.
Thanks for any help!