+ Reply to Thread
Results 1 to 5 of 5

Subract If multiple conditions met

  1. #1
    Registered User
    Join Date
    01-28-2017
    Location
    Miami, Florida
    MS-Off Ver
    2016
    Posts
    7

    Subract If multiple conditions met

    I could use some help solving this problem. I have two sheets, one with orders the second with sales. I need help automatically subtracting from orders based on sales.

    Order sheet: Column A is location, D is SKU, and F is Quantity.
    Sold Sheet: Coulmn E is location, H is SKU, and I is Quantity.

    Is it possible to have a script to 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?

    Worth noting, the order sheet is 18 months out for weekly dispatch. I need a way to limit the subtraction to only what is due before a certain date (column G in order sheet) There could be multiple sales to cover order in a given week.

    I am essentially trying to find out what I still owe or am late on shipping. Sale is > Order, a negative number would work just fine.

    Anything helps!

    Thanks

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Subract If multiple conditions met

    Hello and welcome to the forum.

    Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

    Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Valued Forum Contributor
    Join Date
    05-08-2015
    Location
    Uvalde, TX
    MS-Off Ver
    2010
    Posts
    720

    Re: Subract If multiple conditions met

    Hi,

    As Richard stated a working sample of your workbook would be very helpful in providing a solution to your inquiry.
    Without that only guesses can be provided.

    From your description I generated a working sample to demonstrate a possible solution.

    Enter into cell G2 and copy down:

    Please Login or Register  to view this content.
    Attached is an example.

    Hope this is helpful.

    Cheers
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    01-28-2017
    Location
    Miami, Florida
    MS-Off Ver
    2016
    Posts
    7

    Re: Subract If multiple conditions met

    Thanks Richard.

    Sorry for being vague, by the when I meant when the order was due. For example last week, we shipped for the order due 1/30 or before.

    Southward, I tried your formula but kept getting a value error. Not sure what the issue was exactly.


    I have manually created a sheet in which I hope it will help.

    Sold is all of the sold items from the week. Some items may not be on the actual order as customers call and ask for different items.
    Order is what they have sent to me.
    Results is what I hope to gain from this sheet. There is a column for ideal results, which means if over shipped this week, it would remove the overage from the next orders quantity but that is not required. A negative number in this weeks order would suffice. I need the sold to subtract the earliest possible when date.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    01-28-2017
    Location
    Miami, Florida
    MS-Off Ver
    2016
    Posts
    7

    Re: Subract If multiple conditions met

    I have updated my sheet in hopes it will clarify what I am hoping to be able to achieve.

    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!
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 01-14-2016, 03:53 PM
  2. Subract Cell if Older Than a Year With Other Conditions
    By scubasteveku in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-01-2014, 12:21 PM
  3. Enter in Value A, Have A subract from B while adding A to C
    By Timmart09 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-30-2013, 01:06 PM
  4. How to subract cell by fill color
    By HPIMICHAEL02 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-31-2013, 03:13 AM
  5. buttons to add/subract from inventory.
    By Samantha1123 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 11-12-2012, 11:18 AM
  6. How to subract from a grand total
    By RachelW in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 07-29-2009, 10:45 AM
  7. [SOLVED] add and subract in one sum
    By k1ngy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-17-2006, 05:10 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1