I have list of IDs with warehouse stocks and list of shipments to customers which might have same ID.
Attached screen with example, so for 29.08.2022 with hour 08:00 we need to have 100 carrots, on warehouse we have 375pcs = 275pcs are left. Then few hours later we have shipment to other customer which requires 500pcs of carrots, 275-500 = -225pcs -> it shows info we need to harvest that much more untill 11:00.
- needed vlookup for value from stock
- then subtract every repeated value from top to bottom
Looking for formula or vba which could handle this calculation without manual addition of many columns - currently I have something done with countif for as much as 10 duplicate ID - it just adds "1-"&ID then subtract this and takes it as "2-"&ID.. etc. It's very slow on performance.
As for VBA there is probably no possibility to dynamically declare ID from range as variable from stocks and then simply subtract in loop?
CodePudding user response：
Something like this should work (paste in B2 and use autofill) :
=VLOOKUP($B2,$K$3:$L$17,2,FALSE)-SUMIF(INDIRECT("$B$2:$B$" & ROW()),$B2,INDIRECT("$C$2:$C$" & ROW()))
VLOOKUP($B2,$K$3:$L$17,2,FALSE)return the stock value of the item of the current row (777 for row 2)SUMIF(INDIRECT("$B$2:$B$" & ROW()),$B2,INDIRECT("$C$2:$C$" & ROW()))return the sum of qty ship (of the current row and rows above) of the item of the current row (250 for row 2, 350 for row 5)
CodePudding user response：
what about simple sumifs formula like below?
=L3-SUMIFS(C:C,A:A,J3)
place in cell M3 add drag/copy to the bottom