Hello there,
I have an excel sheet which contains an inventory purchase & sell details of a stock.
In first column I have date in ascending order
In second column I have purchased stock quantity.
In last column I have sold stock quantity.
So what I want is that whenever there is a sell it should get deducted from the first purchase amount.
1-if the sale makes the purchase zero then the remaining sell amount should reduce the next purchase amount.
2- if the sell amount is not enough to reduce the purchase down to zero then the next sale should be deducted from that purchase untill it's zero and then move on to next purchase deduction from the remaining sell amounts
3. I want the formula added to should show how much of each purchase is remaining in stock.
Note:- 1)The data sheet I have got is static, i.e. there will be no more addition in purchase or sale in that data. Whatever I have got is final sheet.
2) before the first buy, the opening Balance is always zero.
Sample data
Date. buy sell Balance
3/4/19 1500 200 0
3/5/19 2400 1400 0
4/17/19 400 2300 0
5/14/19 480 120 0
6/20/19 0 100 0
7/30/19 1003 1000 663
9/30/19 20 0 20
10/4/19 100 0 100
Total 5903 5120 783
Here the balance column is manually calculated as-
1500(first buy)-200(first sale) so still 1300 remains then out of 1400(next sale) that 1300 gets deducted and the balance column for the first purchase becomes zero.
The 100 amount of sell which is remaining from the 1400 sale then gets deducted from 2400 which is next purchase and then 2300 remains of that purchese so then 2300 of next sales gets deducted from that remaining purchase and second row of balance column becomes zero. And so on.
So what I want is that Balance column to be calculated by a formula or a vba or any other method.
I know this sounds complex. But kindly help me this as tha data that I get are in thounds of rows daily.
Thanks in advance
I have an excel sheet which contains an inventory purchase & sell details of a stock.
In first column I have date in ascending order
In second column I have purchased stock quantity.
In last column I have sold stock quantity.
So what I want is that whenever there is a sell it should get deducted from the first purchase amount.
1-if the sale makes the purchase zero then the remaining sell amount should reduce the next purchase amount.
2- if the sell amount is not enough to reduce the purchase down to zero then the next sale should be deducted from that purchase untill it's zero and then move on to next purchase deduction from the remaining sell amounts
3. I want the formula added to should show how much of each purchase is remaining in stock.
Note:- 1)The data sheet I have got is static, i.e. there will be no more addition in purchase or sale in that data. Whatever I have got is final sheet.
2) before the first buy, the opening Balance is always zero.
Sample data
Date. buy sell Balance
3/4/19 1500 200 0
3/5/19 2400 1400 0
4/17/19 400 2300 0
5/14/19 480 120 0
6/20/19 0 100 0
7/30/19 1003 1000 663
9/30/19 20 0 20
10/4/19 100 0 100
Total 5903 5120 783
Here the balance column is manually calculated as-
1500(first buy)-200(first sale) so still 1300 remains then out of 1400(next sale) that 1300 gets deducted and the balance column for the first purchase becomes zero.
The 100 amount of sell which is remaining from the 1400 sale then gets deducted from 2400 which is next purchase and then 2300 remains of that purchese so then 2300 of next sales gets deducted from that remaining purchase and second row of balance column becomes zero. And so on.
So what I want is that Balance column to be calculated by a formula or a vba or any other method.
I know this sounds complex. But kindly help me this as tha data that I get are in thounds of rows daily.
Thanks in advance
