copying dates based on sum of units

[logisticsexcel]

Hello experts,

I am trying to solve below problem. Wondering if macro or a formula can help.

Column A is the shipping date, Col B is the receiver ID, col C is the number of units shipped.

Now if the number of units on a day is 4 or more then the date entered in result column must be same as in col A within the same row.

If the number of units is less than 4 than check units in next row(date). if sum becomes 4 or more then enter the date corresponding to the row in the previous row.
So until the sum of units becomes 4 or more keep checking next rows and then enter max date for those rows.
Once the sum is 4 or more units go to the next row and check.

max 4 rows will need to be looked because within 4 rows the sum should come to 4.

Thanks

[JBeaucaire]

My suggestion:

=IF(D2="morethan4", A2, INDEX($A3:$A$1000000, MATCH("morethan4", $D3:$D$1000000, 0)))

[logisticsexcel]

Hi Jerry,

Thanks for your repsonse. It seems to be working in most instances.
It may need a bit tweaking.

I noticed if there are 2 consecutive less than 4 then the formula is not summing them
In row 114 & 115 the date should be 14/10/2016 and not 17/10/2016.
Because the sum of units for row 114 & 115 is (3+3 =6) which is already more than 4


summing.JPG

[Phuocam]

Try this ...

=IF(C2>=4,A2,IF(B2=B3,IF(C2+C3>=4,A3,IF(B2=B4,IF(C2+C3+C4>=4,A4,IF(B2=B5,IF(C2+C3+C4+C5>=4,A5,""),""))))))

[logisticsexcel]

Hi Phuocam,

Again when there are 2 consecutive less than 4 values the formula doesnt give result as expected.
maybe there needs to be another check added in the formula
Row 114 & 115 should have date as 14/10/2016 because the units in them sum up to 4 and more.
And then the next row 116 needs to be treated as a new value and hence date as 17/10/2016.


Thanks for your response

summing.JPG