Distributing Records Equally by Value

[JerBearMO]

I have a table in which each record represents an account. Within each record is a number of contracts that is associated with this account, along with the total dollar value of these contracts.

What I need to do is to assign a bucket to each of these records so that the number of accounts and the total dollars in each bucket is as equal as possible. The number of buckets will be supplied by the user. (It will likely range from 1 (yay, easy!) to 10 or so.)

I'm not looking for all of the code involved, just a general pointer so that I can do this with as little error rate as possible.

Any suggestions would be most welcome!

[June7]

Not understanding. As equal as possible to what?

Record, account, bucket are all synonyms for the same item? Each record/account/bucket has contracts. How can a number of contracts be within a single record?

Show examples of source data and desired output.

[JerBearMO]

Sorry for the confusion. Let me explain more clearly. :-)

The accounts table contains the fields AccountNumber, NumberOfContracts, DollarValue, and a blank field, GroupNumber.

Example data:
AccountNumber, NumberOfContracts, DollarValue, GroupNumber
10000, 2, $500,
10001, 4, $725,
10002, 2, $225,
10003, 1, $700,
10004, 3, $25,

The user will input an integer indicating the number of GroupNumber to be used. For this example, let's say three. I then want to assign each of these records a group number between 1 and 3. When all GroupNumber have been assigned, I want the sum of NumberOfContracts and the sum of DollarValue to be as close as possible between each of the three groups. Example:

10000, 2, $500, 1
10001, 4, $725, 2
10002, 2, $225, 1
10003, 1, $700, 3
10004, 3, $25, 3

In this example, each group number contains records where the sum of the NumberOfContracts is now 4 and the sum of the DollarValue is now $725. Of course, I'm doing this with a much larger table.

Does this help?

[June7]

Pseudocode:

1. sum DollarValue, divide by input to get the target dollar value for each group

2. sum sequential records and assign group number until target met

3. continue summing records with next group number

4. repeat 3 as often as needed to reach given number of groups target

This will require VBA code. Not a task I wish to tackle.