New Contributor

# Stock Deduction for Sale Order Item

 Item SO ID SO Qty Stock Difference A A123 10 12 0 B A124 5 5 0 A A125 7 12 -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

# Re: Stock Deduction for Sale Order Item

@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.

# Re: Stock Deduction for Sale Order Item

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.