Allocating multiple selections to multiple people

[redneckopen]

Lets say in A2 thru A5 I have 4 different codes for one product all with different amounts represented in B2 thru B5. I then have 7 different people I need to allocate those amounts to in columns C1 thru I1 evenly against their order in C2 thru I2. What would be the algorithm that could be used?

eg.
GROUP 1 GROUP 2 GROUP 3 GROUP 4 GROUP 5 GROUP 6 GROUP 7
7,500 18,122 3,545 21,776 12,124 5,488 7,210
SCB 26,542
SCW 15,885
SCA 7,122
SCV 65,124

 

[sheetspread]

Something like:

Excel 2010
	A	B	C	D	E	F	G	H	I
1	GROUP1	GROUP2	GROUP3	GROUP4	GROUP5	GROUP6	GROUP7
2	7,500	18,122	3,545	21,776	12,124	5,488	7,210	75,765
3	0.09899	0.239187	0.046789	0.287415	0.160021	0.072435	0.095163	1
4			GROUP1	GROUP2	GROUP3	GROUP4	GROUP5	GROUP6	GROUP7
5	SCB	26,542.00	2,627.40	6,348.50	1,241.88	7,628.57	4,247.28	1,922.56	2,525.81
6	SCW	15,885.00	1,572.46	3,799.48	743.25	4,565.59	2,541.94	1,150.62	1,511.66
7	SCA	7,122.00	705.01	1,703.49	333.23	2,046.97	1,139.67	515.88	677.75
8	SCV	65,124.00	6,446.64	15,576.81	3,047.11	18,717.62	10,421.22	4,717.22	6,197.37
Sheet8

Cell Formulas
Range	Formula
H2	=SUM(A2:G2)
A3	=A2/$H$2
C5	=A$3*$B5

 

[redneckopen]

That isn't quite correct, Group1 order is 7,500 but it is allocating 11,351.51 to group1 from the 4 codes.

 

[sheetspread]

Do you want an equal percentage to each? Or does the 26,542, 15,885, 7122, and 65,124 determine which goes where?

 

[redneckopen]

Yes, it needs to be allocated against the totals available. The reason (if it matters) is to minimize and spread out risk between customers.

 

[sheetspread]

This?

Excel 2010
	A	B	C	D	E	F	G	H	I	J
1	GROUP1	GROUP2	GROUP3	GROUP4	GROUP5	GROUP6	GROUP7
2	7,500	18,122	3,545	21,776	12,124	5,488	7,210
3
4				GROUP1	GROUP2	GROUP3	GROUP4	GROUP5	GROUP6	GROUP7
5	SCB	26,542.00	0.23	1,735.94	4,194.48	820.52	5,040.23	2,806.20	1,270.24	1,668.81
6	SCW	15,885.00	0.14	1,038.93	2,510.34	491.07	3,016.51	1,679.47	760.22	998.76
7	SCA	7,122.00	0.06	465.80	1,125.50	220.17	1,352.44	752.99	340.84	447.79
8	SCV	65,124.00	0.57	4,259.33	10,291.67	2,013.24	12,366.82	6,885.35	3,116.69	4,094.63
9		114,673.00
Sheet8

Cell Formulas
Range	Formula
C5	=B5/$B$9
D5	=$C5*A$2
B9	=SUM(B5:B8)

 

[redneckopen]

Perfect, thank you much!!!