Assigning work

[Sheridan1712]

I have a list of accounts that need to be worked by a specific number of people. I need them to be worked taking care of the oldest first and I need them assigned by the work load each person can handle. For this case I need to have Jane work 12 of the accounts and Tom 9. I need each to start at the oldest claim and work there way down. I also need Jane to work 12 sicne she is faster. This list will change daily and the number of people I have workign also changes as does the amount that each on can handle...

Claim RepAge Rep
12566 36 Jane 12
12566 33 Tom 9
12566 32
12566 32
12566 18
12566 18
12566 18
12566 16
12566 15
12566 15
12566 15
12566 15
12566 15
12566 15
12566 15
12566 15
12566 15
12566 15
12566 15
12566 15
12566 15


Is there some formula or macro that can assign the work taking in account the criteria I mentioned? Thanks.

[shg]

One way:

	B	M	N	O	P	Q
1			12	9
2		Rep	Jane	Tom
3	Claim		0.00	0.00
4	12566	Jane	0.08	0.00		M4 and down: =INDEX($N$2:$O$2, MATCH(MIN(N3:O3), N3:O3, 0))
5	12566	Tom	0.08	0.11		N4 and across and down: =N3 + ($M4=N$2) / N$1
6	12566	Jane	0.17	0.11
7	12566	Tom	0.17	0.22
8	12566	Jane	0.25	0.22
9	12566	Tom	0.25	0.33
10	12566	Jane	0.33	0.33
11	12566	Jane	0.42	0.33
12	12566	Tom	0.42	0.44
13	12566	Jane	0.50	0.44
14	12566	Tom	0.50	0.56
15	12566	Jane	0.58	0.56
16	12566	Tom	0.58	0.67
17	12566	Jane	0.67	0.67
18	12566	Jane	0.75	0.67
19	12566	Tom	0.75	0.78
20	12566	Jane	0.83	0.78
21	12566	Tom	0.83	0.89
22	12566	Jane	0.92	0.89
23	12566	Tom	0.92	1.00
24	12566	Jane	1.00	1.00

[Sheridan1712]

When I entered the formula in M4 I am getting #N/A.

[hicks1ch]

Index Formulas with array's need you to hit Ctrl+Shift+Enter. Try that it should work

[shg]

See attached.