Based on the Location & Quantity, Locations to be grouped & corresponding sum to be added

[vasu2007]

Hello Friends,

I need to summarize data based on the locations & Quantity.
Truck will go to different locations & loads the Quantity.
Requirement will better understood by looking at the picture.
Explanation is given in the comments column.

Sorry guys, I have searched lot of forums but unable find the solution.
As I am a beginner in vba, I know, I cant do this as it is a complicated logic involved.
Seeking your help.

Thanks in advance.

 

[etaf]

would a pivot table help, as that would Group items and sum

Perhaps use the XL2BB add-in - see signature
OR load a sample sheet - no private data - onto a share like dropbox or onedrive

 

[vasu2007]

Trial work-vba.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	My Data:							Desired Output
2	SL NO	Location	Quantity	Truck Type	No.of Trucks	Per Truck (helper column)		SL NO	Storage Location	QTY	NO. OF TRUCKS	Comments
3	1	A	1	16 MT	1	17.6		1	A+B+C		1	If Truck is 1 in E column, It goes to location A+B+C & Qty is 17.6
4	1	C	0.8					2	A		1	If Truck is 2 in E column, 1 truck will go to location A and loads 24.9 Qty(Total Qty/2)
5	1	A	4.7					2	A+B+C+D		1	2nd Truck will goes to location A & takes remaining 6.5 qty & goes to another locations(B+C+D) for remaining qty
6	1	B	11					3	A		1	if truck is 3 in E col, It goes to location A & takes Qty 21.9
7			17.6					3	B+C		1	B+C sum is coming around 21 tons, so truck 2 goes to B+C location
8	2	D	1.3	21 MT	2	24.9		3	A+D		1	3rd Truck goes to Location A+D for taking remaining Qty.
9	2	D	4.5
10	2	C	4.8
11	2	A	31.4	balance Qty: 31.4-24.9=6.5
12	2	B	4.9
13	2	B	1.3
14	2	B	1.7
15			49.8
16	3	C	4.5	21 MT	3	21.97
17	3	C	0.7
18	3	B	15.9
19	3	D	3.7
20	3	D	2.2
21	3	D	5.8
22	3	D	5
23	3	D	3
24	3	D	1
25	3	A	24.1	Bal 2.133
26			65.9
Trucks (2)

Cell Formulas
Range	Formula
F3	F3	=C7/E3
F8	F8	=C15/E8
F16	F16	=C26/E16

 

[vasu2007]

Trial work-vba.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	My Data:							Desired Output
2	SL NO	Location	Quantity	Truck Type	No.of Trucks	Per Truck (helper column)		SL NO	Storage Location	QTY	NO. OF TRUCKS	Comments
3	1	A	1	16 MT	1	17.6		1	A+B+C	17.6	1	If Truck is 1 in E column, It goes to location A+B+C & Qty is 17.6
4	1	C	0.8					2	A	24.9	1	If Truck is 2 in E column, 1 truck will go to location A and loads 24.9 Qty(Total Qty/2)
5	1	A	4.7					2	A+B+C+D	25.0	1	2nd Truck will goes to location A & takes remaining 6.5 qty & goes to another locations(B+C+D) for remaining qty
6	1	B	11					3	A	21.9	1	if truck is 3 in E col, It goes to location A & takes Qty 21.9
7			17.6					3	B+C	21.1	1	B+C sum is coming around 21 tons, so truck 2 goes to B+C location
8	2	D	1.3	21 MT	2	24.9		3	A+D	22.8	1	3rd Truck goes to Location A+D for taking remaining Qty.
9	2	D	4.5
10	2	C	4.8
11	2	A	31.4	balance Qty: 31.4-24.9=6.5
12	2	B	4.9
13	2	B	1.3
14	2	B	1.7
15			49.8
16	3	C	4.5	21 MT	3	21.97
17	3	C	0.7
18	3	B	15.9
19	3	D	3.7
20	3	D	2.2
21	3	D	5.8
22	3	D	5
23	3	D	3
24	3	D	1
25	3	A	24.1	Bal 2.133
26			65.9
Trucks

Cell Formulas
Range	Formula
F3	F3	=C7/E3
F8	F8	=C15/E8
F16	F16	=C26/E16

 

[vasu2007]

Trial work-vba.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	My Data:							Desired Output
2	SL NO	Location	Quantity	Truck Type	No.of Trucks	Per Truck (helper column)		SL NO	Storage Location	QTY	NO. OF TRUCKS	Comments
3	1	A	1	16 MT	1	17.6		1	A+B+C		1	If Truck is 1 in E column, It goes to location A+B+C & Qty is 17.6
4	1	C	0.8					2	A		1	If Truck is 2 in E column, 1 truck will go to location A and loads 24.9 Qty(Total Qty/2)
5	1	A	4.7					2	A+B+C+D		1	2nd Truck will goes to location A & takes remaining 6.5 qty & goes to another locations(B+C+D) for remaining qty
6	1	B	11					3	A		1	if truck is 3 in E col, It goes to location A & takes Qty 21.9
7			17.6					3	B+C		1	B+C sum is coming around 21 tons, so truck 2 goes to B+C location
8	2	D	1.3	21 MT	2	24.9		3	A+D		1	3rd Truck goes to Location A+D for taking remaining Qty.
9	2	D	4.5
10	2	C	4.8
11	2	A	31.4	balance Qty: 31.4-24.9=6.5
12	2	B	4.9
13	2	B	1.3
14	2	B	1.7
15			49.8
16	3	C	4.5	21 MT	3	21.97
17	3	C	0.7
18	3	B	15.9
19	3	D	3.7
20	3	D	2.2
21	3	D	5.8
22	3	D	5
23	3	D	3
24	3	D	1
25	3	A	24.1	Bal 2.133
26			65.9
Trucks (2)

Cell Formulas
Range	Formula
F3	F3	=C7/E3
F8	F8	=C15/E8
F16	F16	=C26/E16

Pls ignore this image. Qty column has no values