Stock Deduction for Sale Order Item

%3CLINGO-SUB%20id%3D%22lingo-sub-2021834%22%20slang%3D%22en-US%22%3EStock%20Deduction%20for%20Sale%20Order%20Item%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2021834%22%20slang%3D%22en-US%22%3E%3CTABLE%20width%3D%22341%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2262.4px%22%3EItem%3C%2FTD%3E%3CTD%20width%3D%2264px%22%3ESO%20ID%3C%2FTD%3E%3CTD%20width%3D%2264px%22%3ESO%20Qty%3C%2FTD%3E%3CTD%20width%3D%2264px%22%3EStock%3C%2FTD%3E%3CTD%20width%3D%2285.6px%22%3EDifference%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2262.4px%22%3EA%3C%2FTD%3E%3CTD%20width%3D%2264px%22%3EA123%3C%2FTD%3E%3CTD%20width%3D%2264px%22%3E10%3C%2FTD%3E%3CTD%20width%3D%2264px%22%3E12%3C%2FTD%3E%3CTD%20width%3D%2285.6px%22%3E0%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2262.4px%22%3EB%3C%2FTD%3E%3CTD%20width%3D%2264px%22%3EA124%3C%2FTD%3E%3CTD%20width%3D%2264px%22%3E5%3C%2FTD%3E%3CTD%20width%3D%2264px%22%3E5%3C%2FTD%3E%3CTD%20width%3D%2285.6px%22%3E0%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2262.4px%22%3EA%3C%2FTD%3E%3CTD%20width%3D%2264px%22%3EA125%3C%2FTD%3E%3CTD%20width%3D%2264px%22%3E7%3C%2FTD%3E%3CTD%20width%3D%2264px%22%3E12%3C%2FTD%3E%3CTD%20width%3D%2285.6px%22%3E-5%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20unable%20to%20perform%20operation%20in%20excel%20for%20stock%20inventory.%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20above%20table%2C%20Item%20A%20has%20two%20sale%20orders%20and%20Stock%20column%20was%20done%20VLookup.%20Hence%20same%20qty%20shown%20for%20Item%20A.%20I%20like%20to%20find%20difference%20where%20Item%20A%20SO%20ID%20A123%20will%20be%20allocated%2010%20Qty.%20The%20Remaining%202%20Qty%20will%20be%20allocated%20to%20Order%20ID%20A125.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKindly%20help%20us%20solution%20for%20it.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2021834%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2022688%22%20slang%3D%22en-US%22%3ERe%3A%20Stock%20Deduction%20for%20Sale%20Order%20Item%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2022688%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F914053%22%20target%3D%22_blank%22%3E%40vangashiva%3C%2FA%3E%26nbsp%3BI%20think%20we%20need%20more%20information%20about%20what%20you%20want%20because%20there%20are%20many%20solutions%20but%20I%20think%20most%20will%20not%20give%20you%20what%20you%20really%20want.%26nbsp%3B%20For%20example%20you%20could%20just%20use%20%3DD2-C2%20but%20I%20assume%20that%20is%20too%20specific%20for%20this%201%20case.%26nbsp%3B%20I%20believe%20you%20say%20you%20have%20a%20VLOOKUP%20formula%20in%20the%20Stock%20cell%2C%20so%20maybe%20tweak%20that%20formula%20to%20something%20like%3C%2FP%3E%3CP%3E%3DVLOOKUP(what%20you%20have%20now)%20-%20SUMIF(%24A%241%3A%24A1%2CA2%2C%24C%241%3A%24C1)%3C%2FP%3E%3CP%3Eso%20it%20will%20still%20do%20the%20stock%20lookup%20based%20on%20ITEM%20but%20then%20subtract%20all%20previous%20SO%20QTY%20found%20in%20the%20table.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor
ItemSO IDSO QtyStockDifference
AA12310120
BA124550
AA125712-5

 

I'm unable to perform operation in excel for stock inventory. 

As above table, Item A has two sale orders and Stock column was done VLookup. Hence same qty shown for Item A. I like to find difference where Item A SO ID A123 will be allocated 10 Qty. The Remaining 2 Qty will be allocated to Order ID A125. 

 

Kindly help us solution for it. 

3 Replies

@vangashiva I think we need more information about what you want because there are many solutions but I think most will not give you what you really want.  For example you could just use =D2-C2 but I assume that is too specific for this 1 case.  I believe you say you have a VLOOKUP formula in the Stock cell, so maybe tweak that formula to something like

=VLOOKUP(what you have now) - SUMIF($A$1:$A1,A2,$C$1:$C1)

so it will still do the stock lookup based on ITEM but then subtract all previous SO QTY found in the table.

@mtarler 

 

I cannot subtract directly as multiple times it will repeat the value. I like to display row wise difference which is not happening. In real practical scenario, there will be multiple columns available apart from above table. 

 

Please suggest some solution. 

@vangashiva Sorry I don't know what you are looking for.  We need more information.  Maybe attach a sample sheet.  What you have said just isn't enough for me to understand.