ETA Calculation based on orders

[sanket_sk]

Hi All,



Hope you are doing good !!!



I need your help to develop formula / VBA where Excel will calculate Spare ETA( Expected Time of Arrival) based on To-be Conf Qty in Order Sheet and Order placed details in ETA Sheet.


Logic will be like this ..

Excel will pick code based on Order creation date, will go to To-be Conf Qty in Order sheet, loop in ETA sheet Order Qty,

Based on order date and Quantity formula / VBA will return value mentioned in ETA Week.

This formula will loop through Order Qty if Order Qty is less than To-be Conf Qty and find next order placed for the same spare, and it will continue to find ETA, in this formula should cover/take care of all the orders based on Order creation date in sheet order it should loop through Order Qty in ETA sheet and return correct ETA mentioned in ETA Week.

Return value examples..
2051 - To-be Conf Qty <= Order Qty
Partial ETA 2052 & 2102 - To-be Conf Qty <= Order Qty but 1+ orders are raised for this
Partial 2103 - To-be Conf Qty > Order Qty but some qty can be given for this order
Will Update ETA - To-be Conf Qty > Order Qty and no leftover qty is available in order qty

Please help me create dynamic formula / VBA for this

Sharing Sample excel for easy understanding

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ
2									1st Check Order Date							3rd Check To-be Conf Qty											2nd Check Material No																		8 Need result here
3	Month	Comp code	Plant Code	Req deliv date	Sold-to name	Order type	SO no	SO line no	Order creation date	Invoice no	Invoice date	Conf Delv Date	Qty	Req Qty	Conf Qty	To-be Conf Qty	Value Invoiced	Value returned	Confirmed w/o Delivery	Unconfirmed sales orders	Open Delivery before Goods Issue	In Transit after Goods Issue	After POD without invoice	Potential sales	Delv no	Delv line no	Material no	Material Description	CAN	Currency	Planned GI date	Actual GI date	Planned delv date	DN creation date	POD Date	Sold-to no	Cust PO no	Cust PO Date	AssignmentNo	Cust material no	Ship-to-name	Ship-to-no	Ship-to country	Ship-to- city	ETA	BU	BG	AG	MAG	PayerParty	PayerPartyName	Sales District	Sales Office	CPG	Sold-to Level 0	Sold-to Level 1	Sold-to Level 2	Storage Location	Item Category	Source	Min3NetPrice	Customer Expected Value
4	29-11-20 0:00	DO31	17DO	20201207	Vipul Motors	ROYA	212975670	110	29-11-20 0:00				200	200	180	20	120	0	0	0	0	0	0	36			843008265048	Button Gaskit									469-979							Lucknow	2051
5	07-12-20 0:00	DO31	17DO	20201207	Vipul Motors	ROYA	212941498	140	07-12-20 0:00				400	400	231	169	560	0	0	0	0	0	0	168			1993020136532	Handle									455-988							BANGALORE	Partial ETA 2052 & 2102
6	03-12-20 0:00	DO31	17DO	20201207	Vipul Motors	ROYA	212941498	150	03-12-20 0:00				100	100	76	24	500	0	0	0	0	0	0	150			843008265048	Button Gaskit									455-988							BANGALORE	2103
7	07-12-20 0:00	DO31	17DO	20201207	Vipul Motors	ROYA	212961872	20	07-12-20 0:00				350	350	25	325	2,000	0	0	0	0	0	0	600			1993020136532	Handle									455-990							BANGALORE	Will Update ETA
8	07-12-20 0:00	DO31	17DO	20201207	Vipul Motors	ROYA	212961872	170	07-12-20 0:00				90	90	7	83	400	0	0	0	0	0	0	120			843008304444	Oil Seal									455-990							BANGALORE	2104
9	30-11-20 0:00	DO31	17DO	20201207	Qutab Auto-Maruti Authorized Service Center	ROYA	212964634	90	30-11-20 0:00				50	50	2	48	475	0	0	0	0	0	0	142.5			843008265048	Button Gaskit									637-001							PURNEA	2101 & 2103
10	07-12-20 0:00	DO31	17DO	20201207	Competent	ROYA	212971568	30	07-12-20 0:00				50	50	1	49	1,740	0	0	0	0	0	0	522			1993020136532	Handle									399-329							Amritsar
11	08-12-20 0:00	DO31	17DO	20201207	Qutab Auto-Maruti Authorized Service Center	ROYA	212867616	10	08-12-20 0:00				50	50	2	48	2,100	0	0	0	0	0	0	630			843008265048	Button Gaskit									465-496							Ranchi	Partial 2103
12	08-12-20 0:00	DO31	17DO	20201207	DIGITAL INDIA PRIVATE LIMITED	ROYA	212976852	60	08-12-20 0:00				50	50	26	24	708	0	0	0	0	0	0	212.4			843008265048	Button Gaskit									583-450							Noida	Will Update ETA
13	07-12-20 0:00	DO31	17DO	20201207	Drona SERVICE POINT	ROYA	212965978	40	07-12-20 0:00				50	50	6	44	3,100	0	0	0	0	0	0	930			843008304444	Oil Seal									381-237							Nagaon	2106
Order

Cell Formulas
Range	Formula
I4:I13	I4	=A4
P4:P13	P4	=N4-O4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AN4:AN13	Cell Value	duplicates	text	NO

Book1
	A	B	C	D	E	F	G
4		5th Check Material No			4th Check Order Date	6th Check order Qty	7 Pick ETA from here
5	Purchasing Document	Material	Short Text	Item	Order Date	Order Qty	ETA Week
6		843008265048			30-11-20	20	2051
7		843008304444			28-Nov	100	2104
8		1993020136532			30-Nov	70	2052
9		843008265048			02-Dec	50	2101
10		1993020136532			05-Dec	50	2102
11		843008265048			07-Dec	40	2103
12		843008304444			05-Dec	20	2106
ETA