Complicated situation - have to repack goods in VBA

[mehidy1437]

Hi Guys,

Here I'm with a complicated situation, I have many styles packed goods in carton, now I have to re-pack again for air shipment, with below condition.

FILE IN GOOGLE DRIVE
Sheets are;
01.AIR-QTY
02.CURRENT-PACKING
03.DESIRED-PACKING

We will turn sheet "CURRENT-PACKING" to sheet "DESIRED-PACKING" (I have shown desired output for order no 599 & color B94, same will follow for others color accordingly).

Conditions are;
***We will match the Style no#, Order no & Col between sheet AIR-QTY & CURRENT-PACKING.
***In some cartons there are mixed sizes of goods, we won't repack those cartons.
***We will reduce/remove only complete carton from current-packing & won't break any carton.
***Quantity can't be less than the required AIR-QTY, we can keep more qty as we have to keep complete carton & we are not allowed to broke any complete carton.
***If the same size has in multiple rows (example in "desired-packing" order no-599, col-B94), then we have to calculate those (rows) qty to match with required aired qty in sheet AIR-QTY.

I want to have solutions in VB to do it faster.
How can I start?
Any help with the idea or with some code would be highly appreciated.

AIR-QTY

RE-PACKING..xlsx
	A	B	C	D	E	F	G	H	I	J
4	ORDER	STYLE	COLOUR	XS	S	M	L	XL	XXL	TOT
5	517	ALL								ALL
6	518	ALL								ALL
7	519	ALL								ALL
8	525	ALL								ALL
9	527	ALL								ALL
10	529	ALL
11	532	ALL
12	536	ALL								ALL
13	540	ALL								ALL
14	583	ALL								ALL
15	604	ALL								ALL
16
50	583	PMKTRP5	B94-	2	3	4	2	2	0	13
51	583	PMKTRP5	NS-	4	8	12	8	3	0	35
52	583	PMKTRP5	R11-		3	5	2		0	10
53	599	PMKTRP5	B94-	175	430	515	450	260	0	1,830
54	599	PMKTRP5	NS-	200	460	560	485	290	0	1,995
55	599	PMKTRP5	R11-	15	25	30	20	10	0	100
AIR-QTY

CURRENT-PACKING

RE-PACKING..xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
8	Style	Order		Carton No.			Col													Ctns qty	Per Ctn	Tot Qty
9								6	8	10	12	14	XXS	XS	S	M	L	XL	XXL
10	PMKTRP5	583		1	-	1	B94-							2	3	4	2	2		1	13	13
11	PMKTRP5	583		2	-	2	NS-							4	8	12	8	3		1	35	35
12	PMKTRP5	583		3	-	3	R11-								3	5	2			1	10	10
13	PMKTRP5	599		4	-	9	B94-							46						6	46	276
14	PMKTRP5	599		10	-	22	B94-								45					13	45	585
15	PMKTRP5	599		23	-	37	B94-									42				15	42	630
16	PMKTRP5	599		38	-	38	B94-									47				1	47	47
17	PMKTRP5	599		39	-	50	B94-										42			12	42	504
18	PMKTRP5	599		51	-	57	B94-											43		7	43	301
19	PMKTRP5	599		58	-	58	B94-							22			19	4		1	45	45
CURRENT PACKING

DESIRED-PACKING

RE-PACKING..xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
8	Style	Order		Carton No.			Col													Ctns qty	Per Ctn	Tot Qty
9								6	8	10	12	14	XXS	XS	S	M	L	XL	XXL
10	PMKTRP5	583		1	-	1	B94-							2	3	4	2	2		1	13	13
11	PMKTRP5	583		2	-	2	NS-							4	8	12	8	3		1	35	35
12	PMKTRP5	583		3	-	3	R11-								3	5	2			1	10	10
13	PMKTRP5	599		4	-	7	B94-							46						4	46	184
14	PMKTRP5	599		8	-	17	B94-								45					10	45	450
15	PMKTRP5	599		18	-	29	B94-									42				12	42	504
16	PMKTRP5	599		30	-	30	B94-									47				1	47	47
17	PMKTRP5	599		31	-	41	B94-										42			11	42	462
18	PMKTRP5	599		42	-	47	B94-											43		6	43	258
19	PMKTRP5	599		48	-	48	B94-							22			19	4		1	45	45
DESIRED-PACKING

Thanks in advance.

 

[mehidy1437]

bump

 

[mehidy1437]

re bump

 

[mehidy1437]

Re Bump