Formula assistance - identifying full or part counts

[CaptainGravyBum]

Sorry, this title doesn't really explain anything, I'll try to explain my problem here.

I've had quite a lot of help from this forum to find full pallets on unique orders for an order information spreadsheet I have, which I'm grateful for. The last problem that was solved has ended up creating a new challenge.

From the sheet below, I now have unique order numbers calculating how many full pallets were dispatched based on the order count total (column N). This example is returning 3.64 full pallets were sent out, I need to get this so if there are part pallets, column G holds the full pallet qty and the remainder is used to identify how many cartons or pieces were sent in addition. In this example, it should be 3 full pallets and 14 cartons.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Part number	Order number	Order ID	Carton Qty	Pallet Qty	Count	Pallets	Cartons	Pieces				Full units	Order Count Total	Pieces
2	PBB12T	205595	109634	25	550	350									0
3	PBB12T	205595	109634	25	550	550									0
4	PBB12T	205595	109634	25	550	550									0
5	PBB12T	205595	109634	25	550	550	3.64							2000	0
Sheet1

Cell Formulas
Range	Formula
G2:G5	G2	=IF(N2="","",IFERROR(IF(AND(N2>=E2,N2/E2>0),N2/E2,""),""))
H2:H5	H2	=IFERROR(IF(INT(M2)=0,"",INT(M2)),"")
I2:I5	I2	=IF(O2=0,"",O2)
M2:M5	M2	=IFERROR(IF(N2<F2,F2/E2,""),"")
N2	N2	=IF(COUNTIFS($A:$A,A2,$B:$B,B2)=COUNTIFS($A2:$A$2,A2,$B2:$B$2,B2), SUMIFS(F:F,A:A,A2,B:B,B2),"")
O2:O5	O2	=IF(C2<>"",IFERROR(IF(N2<D2,N2,IF(OR(H2="",N2-P2=0),0,(N2-P2))),0),"")
P2:P5	P2	=IFERROR((H2*D2),"")
N3:N5	N3	=IF(COUNTIFS($A:$A,A3,$B:$B,B3)=COUNTIFS($A$2:$A3,A3,$B$2:$B3,B3), SUMIFS(F:F,A:A,A3,B:B,B3),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I2:I5	Expression	=I2<>""	text	NO
I2:I5	Expression	=$A2<>""	text	NO
H2:H5	Expression	=H2<>""	text	NO
H2:H5	Expression	=$A2<>""	text	NO
G2:G5	Expression	=G2<>""	text	NO
G2:G5	Expression	=$A2<>""	text	NO
J6:J8,L6:M6,M2:P5	Expression	=J2<>""	text	NO
J6:J8,L6:M6,M2:P5	Expression	=$A2<>""	text	NO

 

[RobP]

Hi,

in column H5, try this formula :

=mod(N5,f5)/d5

Rgds
Rob

 

[RobP]

(and you can correct your formula in col G by adding INT() to it like thus to give you only the number of pallets) :

=IF(N3="","",IFERROR(IF(AND(N3>=E3,N3/E3>0),INT(N3/E3),""),""))

 

[RobP]

Hi, forgive me, I missed off the last number of pieces, so heres the complete list :

Book1
	C	D	E	F	G	H	I	J	K	L	M	N	O
1					Pallet	Carton	Pcs
2		25	550		3	14	1					2001
3		25	550		2	2						1150
4
Sheet1

Cell Formulas
Range	Formula
G2:G3	G2	=IF(N2="","",IFERROR(IF(AND(N2>=E2,N2/E2>0),INT(N2/E2),""),""))
H2:H3	H2	=IF(INT(MOD(N2,E2)/D2)>0,INT(MOD(N2,E2)/D2),"")
I2:I3	I2	=IF(MOD(N2,D2) >0,MOD(N2,D2),"")

 

[CaptainGravyBum]

Perfect! Thanks @RobP this works a treat

 

[RobP]

you're welcome, glad to have been able to help.

Rob