Formula to allocate one column to a variable number of columns

[eaber616]

Hi all,
Looking for a formula to allocate the balance by person
to the Alloc accts, using up the smallest balance first.

The file starts with the data in column B. First is to allocate the
up to the Account Max until the Alloc Acct bal is exhausted.

Then allocate to the next Alloc acct until that is used
up, and so on.

Master column contains any excess unallocated
amount (Balance - sum(Alloc 1 - 3))

Variables include:
Number of alloc accounts (columns) could be 0 - 100+
Number of persons (rows) 0 - 50,000+
Alloc Acct bal
Acct max.

I'm looking for a formula(s) that will achieve this
allocation.

Then need to add the allocation formula to Macros
to run daily to create the file. There are subsequent
files generated daily of this one file.

Thanks for your help!

Excel Workbook
	A	B	C	D	E	F	G
1		Acct max			300,000.00	100,000.00	50,000.00
2		Alloc acct bal			1,000,000.00	500,000.00	400,000.00
3				Master	Alloc 1	Alloc 2	Alloc 3
4		Balance		2,953,253.01	1,000,000.00	500,000.00	400,000.00
5
6	person 1	1,125,890.00		675,890.00	300,000.00	100,000.00	50,000.00
7	person 2	750,890.00		300,890.00	300,000.00	100,000.00	50,000.00
8	person 3	500,900.00		50,900.00	300,000.00	100,000.00	50,000.00
9	person 4	250,340.00		340.00	100,000.00	100,000.00	50,000.00
10	person 5	125,040.00		0.00	0.00	75,040.00	50,000.00
11	person 6	75,009.00		49.00	0.00	24,960.00	50,000.00
12	person 7	62,094.00		12,094.00	0.00	0.00	50,000.00
13	person 8	48,080.00		0.00	0.00	0.00	48,080.00
14	person 9	15,010.00		13,090.00	0.00	0.00	1,920.00
15	person 10	0.01		0.01	0.00	0.00	0.00
Sheet1

Excel 2007

 

[eaber616]

New question, same file.

I want to distribute multiple workbooks using the results of this file. The first workbook would contain Column A (all workbooks will contain this), then each workbook book will contain only 1 allocation column.

So workbook 1 is only Column A and Alloc 1
workbook 2 is only Column A and Alloc 2

and so on until each alloc column has it's own file.

Any suggestions on VBA code to achieve this? Thanks!