Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Finding Cumulative Inventory Availability | Running Balance Total

0

Greetings TeachExcel.com & Forum Members:

I am trying to find the available balance of inventory for multiple and sometimes duplicate products down a column. 

COLUMN A: Order #

COLUMN B: Product

COLUMN C: Order Quantity

COLUMN D: Available Inventory

COLUMN E: (Helper column) Balance of C & D

COLUMN F: Is where I am trying to show the quantity of products available minus the quantity ordered for that product, as I go down the row, some products are duplicated, and need to reflect the previous remaining balance of that product and subtract it for the next use case of that same product down the column.

EXAMPLE: is the first product in cell B2 is CALCZO. This product is replicated on the last order: cell B62, CALCZO. 

SO: Cell F2 should equal 652, Cell F62 should equal 302 | or the balance of the last order in E2, minus the next ordered quantity on C62 of 350. 

exceldata-validation
MS Office Version: 365 Business
posted by: jonsully22 (rep: 2) on Wed Jun 19, 2019 at 3:32 pm
Answer
Discuss

Answers

1
Selected Answer

Please try this formula in F2.

=$D2-SUMIF($B$2:$B2,$B2,$C$2:$C2)

Observe that the criteria and sum ranges expand as the formula is copied down to always start in row 2 and end in the row of the formula. Therefore otherwise qualifying entries below the formula row are not included in the total drawn.

posted by: Variatus (rep: 4889) on Wed Jun 19, 2019 at 10:13 pm
Discuss

Discussion

ahhh you beat me! AND your solution is considerably more elegant than mine was going to be haha :P.  Simple and elegent is always the best way!
don (rep: 1989) Jun 19, '19 at 10:27 pm
Thank you Don. This praise definitely made my morning, coming from you :P
BTW you seem to have missed the off-topic comment I made below one of my answers a while ago. I pointed out that pasting VBA code into the edit pane inserts blank rows which disappear upon uploading if code tags were used but not if they were omitted. There should be an easy cure for this. I guess it's how VBA's CRs are interpreted. Please take a look at it.
Variatus (rep: 4889) Jun 19, '19 at 10:39 pm
This saved the day for my team! Thank you guys so much. I was sniffing around and very close, but your solution is perfect, and elegantly explained.

Thank You Variatus + Don :)
jonsully22 (rep: 2) Jun 20, '19 at 1:23 pm
Glad everything worked out :)

Variatus, I definitely missed that and will look into it tomorrow!
don (rep: 1989) Jun 23, '19 at 4:52 pm
Variatus, just checked on that and there are a couple things at play. First, I wasn't able to produce the result that you mentioned when pasting code into the window in and out of CODE tags, so if you can give me a specific example I will use that. Second, the thing is that this edit window is made to make it easier to input formatted text and so if you do not use CODE tags, it will try to 'clean' things up sometimes, which is very difficult to get right for all cases; that's why the CODE tags actually insert <pre></pre> tags around the code to keep it's formatting. To that, I'd say to just always use the CODE tags to ensure proper formatting.
don (rep: 1989) Jun 25, '19 at 6:31 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login