Count unique order splits

[CaptainGravyBum]

Hello,
Please can you help, I have the sheet below where the count (column C) can only be a maximum of 80 items, at which point the remainder ends up in the splits column (column D). I need column E to show if the split count for the total order rows is 80 items combined then it shows 80 on one row per order and the remainder follows afterwards.
so, to try and make it clearer, when the same order number has 80 items it counts as 80 on the row where the total is reached and starts counting again on subsequent rows.
I've put in example data to show how it should look. Sorry for the table, xl2bb wouldn't paste the information.

Order ID	Way of delivery	Count	Split down units	Combined number or surplus
125838	Van	100	20	20
121994	Van	8	0
121994	Van	100	20
121994	Van	100	20
121994	Van	100	20
121994	Van	100	20	80
121994	Van	100	20	20
123151	Customer collection	9	0
123151	Customer collection	45	0

 

[Peter_SSs]

xl2bb wouldn't paste the information.

What happened?
Did it give an error?
Did it paste incorrect values?
Something else?

Try to describe in as much details as you can what you did and what happened.

 

[dermie_72]

Try this formula in D2:

=MAX(0,C2-80)

And this formula in E2:

=IF(A2<>A3,SUMIFS(D$1:D2,A$1:A2,A2)-SUMIFS(E$1:E1,A$1:A1,A2),FLOOR(SUMIFS(D$1:D2,A$1:A2,A2),80)-SUMIFS(E$1:E1,A$1:A1,A2))

Then fill them down your sheet. See if that does what you're after

 

[CaptainGravyBum]

What happened?
Did it give an error?
Did it paste incorrect values?
Something else?

Try to describe in as much details as you can what you did and what happened.

It just wouldn't paste, copied ok, but the ctrl-v or right click paste didn't do anything. Probably my end tbf

 

[Peter_SSs]

Probably my end tbf

No, it looks like you might be experiencing what was discussed throughout this thread.

 

[CaptainGravyBum]

I have had success today with the xl2bb, see below with the suggested formulas. I have an additional query, on the combined total column, it counts up to 80 units and puts the surplus on the next line.
Is there a way to make it the same when it is 80 exactly, so E7 for example should show zero because it is a total of 80.

Book2.xlsx
	A	B	C	D	E
1	Order ID	Way of delivery	Count	Split down units	Combined number or surplus
2	125838	Van	100	20	20
3	121994	Van	8	0	0
4	121994	Van	100	20	0
5	121994	Van	100	20	0
6	121994	Van	100	20	0
7	121994	Van	100	20	80
8	121994	Van	100	20	20
9	123151	Customer collection	9	0	0
10	123151	Customer collection	45	0	0
Sheet1

Cell Formulas
Range	Formula
D2:D10	D2	=IF(AND(B2="Van",C2>80),C2-80,0)
E2:E10	E2	=IF(A2<>A3,SUMIFS(D$1:D2,A$1:A2,A2)-SUMIFS(E$1:E1,A$1:A1,A2),FLOOR(SUMIFS(D$1:D2,A$1:A2,A2),80)-SUMIFS(E$1:E1,A$1:A1,A2))

 

[CaptainGravyBum]

Sorry, that was really lazy of me, I've adapted the formulas to deal with this eventuality, thanks for the help @dermie_72