Request for a Macro solution for my complicated task

[skavinkumar]

Hello Experts,

I Kindly request your support to provide me an solution (Macro / excel formula / tool) for a complicated routine task of mine. because I have do the below example in day to day life like 100s of time.

Description of the requirement

I have 3 lines with negative quantity in "Qty" column. In total (-18) Qty.

The delivery date for these quantities is comment (22-Oct-2020)

I need to fill the delivery date for the additional Qty, which are the positive Qty in "Qty" column, on priority basis based on the "Item Code" column. Priority item codes listed in order in last column of the source table. this Priority list is fixed one.

if in case the qty in a single line exceeds the remaining negative qty available then the line should be duplicated and Qty should be spited accordingly. Please refer lines in green on the "Results I want to Have" Table.

Then on the remark column, I need to have the Remark in the negative qty lines as "Moved to so and so Item code" refer "Result I want to have" Table. And on the delivery dated filled lines I need to have the Remark as "Coming from so and so item code"

Source Table

Position	Sub Position	Item code	OEM CODE	Qty	Delivery Date	Remarks		Order of priority
3​	1​	---	A123BCDE	0				DEF1
3​	2​	ABC1	A123BCDE	9				CDE2
3​	3​	BCD1	A123BCDE	-15				FGH1
3​	4​	ABC2	A123BCDE	32				EFG2
3​	5​	CDE2	A123BCDE	3				FGH2
3​	6​	DEF1	A123BCDE	0				BCD2
3​	7​	EFG2	A123BCDE	5				HIJ1
3​	8​	EFG1	A123BCDE	0				GHI1
3​	9​	FGH2	A123BCDE	6				GHI2
3​	10​	FGH1	A123BCDE	6				IJK2
3​	11​	BCD2	A123BCDE	53				GHI3
3​	12​	HIJ1	A123BCDE	9				IJK1
3​	13​	GHI1	A123BCDE	19				BCD1
3​	14​	GHI3	A123BCDE	-1				DEF2
3​	15​	IJK1	A123BCDE	-3				CDE3
3​	16​	GHI2	A123BCDE	1				ABC1
3​	17​	IJK2	A123BCDE	5				EFG1
3​	18​	DEF2	A123BCDE	12				ABC2
3​	19​	CDE3	A123BCDE	189

Result I Want to Have

Position	Sub Position	Item code	OEM CODE	Qty	Delivery Date	Remarks
3​	1​	---	A123BCDE	0
3​	2​	ABC1	A123BCDE	9
3​	3​	BCD1	A123BCDE	-15		Moved to CDE2, EFG2, FGH2, FGH1
3​	4​	ABC2	A123BCDE	32
3​	5​	CDE2	A123BCDE	3	22-Oct-20	Coming from BCD1
3​	6​	DEF1	A123BCDE	0
3​	7​	EFG2	A123BCDE	5	22-Oct-20	Coming from BCD1
3​	8​	EFG1	A123BCDE	0
3​	9​	FGH2	A123BCDE	5	22-Oct-20	Coming from BCD1, GHI1 & IJK1
3​	9​	FGH2	A123BCDE	1
3​	10​	FGH1	A123BCDE	6	22-Oct-20	Coming from BCD1
3​	11​	BCD2	A123BCDE	53
3​	12​	HIJ1	A123BCDE	9
3​	13​	GHI1	A123BCDE	19
3​	14​	GHI3	A123BCDE	-1		Moved to FGH2
3​	15​	IJK1	A123BCDE	-3		Moved to FGH2
3​	16​	GHI2	A123BCDE	1
3​	17​	IJK2	A123BCDE	5
3​	18​	DEF2	A123BCDE	12
3​	19​	CDE3	A123BCDE	189