Hi everyone,

I have a worksheet with over 20000 records, i did a dcount based on amounts
from 500-5000 and i got a result of 250 records, and dsum based on the same
criteria (500-5000) i got 2,000,000. further i want to distribute the number
of contracts and amount into 4 buckets as follow

Dsum result 10% 10% 30% 50%
total
1,000,000 100,000 100,000 300,000 500,000 1,000,000

Dcount result 10% 10% 30% 50% total
250 25 25 75 125 250

Can i somehow exctract, copy list of contracts in each bucket that will add
up to the dsum amount distribution for each bucket?

for example: a formula or macro that will give me 25 contracts out of 20,000
when i add the amounts it should come up to 100,000 and all contracts must be
between 500-5000.

I really appreciate any help i can get on this one.

Your results won't be exact but close to the results you want.

1) Sort the records by amount
2) In a new column on the 1st record that is greater or equal to $500.00 put
this formula
=sum(C$100:C100)

Notice the dollar sign is on the first 100 only where the row number is the
1st row = 500.

3) copy formula down the worksheet.

4) Your buckets can be extract base on the new column amount

bucket 1 : the totals 0 to $100,000
bucket 2 : the totals 100,000 to $200,000
bucket 3 : the totals 200,000 to $500,000
bucket 4 : the totals 500,000 to $1,000,000
bucket 5 : the totals 1,000,000 to $2,000,000


"David" wrote:

Hi everyone,

I have a worksheet with over 20000 records, i did a dcount based on amounts
from 500-5000 and i got a result of 250 records, and dsum based on the same
criteria (500-5000) i got 2,000,000. further i want to distribute the number
of contracts and amount into 4 buckets as follow

Dsum result 10% 10% 30% 50%
total
1,000,000 100,000 100,000 300,000 500,000 1,000,000

Dcount result 10% 10% 30% 50% total
250 25 25 75 125 250

Can i somehow exctract, copy list of contracts in each bucket that will add
up to the dsum amount distribution for each bucket?

for example: a formula or macro that will give me 25 contracts out of 20,000
when i add the amounts it should come up to 100,000 and all contracts must be
between 500-5000.

I really appreciate any help i can get on this one.

Hi Joel,

I really appreciate your help, however this will give me a running total on
the new column where for example the first 100 records will add up to 100K,
and then i would have to start the same running total to get the next bucket.

Is there away that i can divide the whole 250 records into 4 buckets by
pressing a button or running a macro which would base the division on the
result of dcount and dsum. since i will be doing this in the future with much
larger file and more than 100 buckets

"Joel" wrote:

Your results won't be exact but close to the results you want.

1) Sort the records by amount
2) In a new column on the 1st record that is greater or equal to $500.00 put
this formula
=sum(C$100:C100)

Notice the dollar sign is on the first 100 only where the row number is the
1st row = 500.

3) copy formula down the worksheet.

4) Your buckets can be extract base on the new column amount

bucket 1 : the totals 0 to $100,000
bucket 2 : the totals 100,000 to $200,000
bucket 3 : the totals 200,000 to $500,000
bucket 4 : the totals 500,000 to $1,000,000
bucket 5 : the totals 1,000,000 to $2,000,000


"David" wrote:

Hi everyone,

I have a worksheet with over 20000 records, i did a dcount based on amounts
from 500-5000 and i got a result of 250 records, and dsum based on the same
criteria (500-5000) i got 2,000,000. further i want to distribute the number
of contracts and amount into 4 buckets as follow

Dsum result 10% 10% 30% 50%
total
1,000,000 100,000 100,000 300,000 500,000 1,000,000

Dcount result 10% 10% 30% 50% total
250 25 25 75 125 250

Can i somehow exctract, copy list of contracts in each bucket that will add
up to the dsum amount distribution for each bucket?

for example: a formula or macro that will give me 25 contracts out of 20,000
when i add the amounts it should come up to 100,000 and all contracts must be
between 500-5000.

I really appreciate any help i can get on this one.

You dont have to start the running total for each bucket. I arranged the
buckets so the ranges get the results you are looking for. The 2nd bucket is
the sum is betsween 200,000 to 300,00. I can write a macro if I knew the
column where the total was located, but I would use the same algorithm that I
explained in my last posting. I wanted to make sure you were hapy with the
algorithm before I suggested a macro. I didn't want to write the macro and
then you didn't like the results.

"David" wrote:

Hi Joel,

I really appreciate your help, however this will give me a running total on
the new column where for example the first 100 records will add up to 100K,
and then i would have to start the same running total to get the next bucket.

Is there away that i can divide the whole 250 records into 4 buckets by
pressing a button or running a macro which would base the division on the
result of dcount and dsum. since i will be doing this in the future with much
larger file and more than 100 buckets

"Joel" wrote:

Your results won't be exact but close to the results you want.

1) Sort the records by amount
2) In a new column on the 1st record that is greater or equal to $500.00 put
this formula
=sum(C$100:C100)

Notice the dollar sign is on the first 100 only where the row number is the
1st row = 500.

3) copy formula down the worksheet.

4) Your buckets can be extract base on the new column amount

bucket 1 : the totals 0 to $100,000
bucket 2 : the totals 100,000 to $200,000
bucket 3 : the totals 200,000 to $500,000
bucket 4 : the totals 500,000 to $1,000,000
bucket 5 : the totals 1,000,000 to $2,000,000


"David" wrote:

Hi everyone,

I have a worksheet with over 20000 records, i did a dcount based on amounts
from 500-5000 and i got a result of 250 records, and dsum based on the same
criteria (500-5000) i got 2,000,000. further i want to distribute the number
of contracts and amount into 4 buckets as follow

Dsum result 10% 10% 30% 50%
total
1,000,000 100,000 100,000 300,000 500,000 1,000,000

Dcount result 10% 10% 30% 50% total
250 25 25 75 125 250

Can i somehow exctract, copy list of contracts in each bucket that will add
up to the dsum amount distribution for each bucket?

for example: a formula or macro that will give me 25 contracts out of 20,000
when i add the amounts it should come up to 100,000 and all contracts must be
between 500-5000.

I really appreciate any help i can get on this one.

Hi Joel,

yes you are right the data is arranged properly, however let me explain this
a little further. the data that i am working with is very large, and there
are multiple ranges such as 0-5K, 5K-10K, and so on, even it goes upto
1M-10M, and each range will have over 100 bucket according to their last
months performance. which could be somewhere between 0.1% of each range to
50%,

and the amount is not the primary key for distribution, however number of
contract is. for example a bucket in 5k-10K is entitled to 3000 contract or
5% of the entire available contracts in the rage 5k-10K according his or her
last months performance, and the total amount in 5k-10K times 5% will result
in 5M, now i must sumbit 3K contracts to this person with the amount as close
as 5M.

Can i or rather you, lol write a macro that extracts 3K contracts out of
100K records with amount as close to 5K, please keep in mind the amount can
come close however the number of contracts must remain the same.

let me know if i have complicated this too much.

"Joel" wrote:

You dont have to start the running total for each bucket. I arranged the
buckets so the ranges get the results you are looking for. The 2nd bucket is
the sum is betsween 200,000 to 300,00. I can write a macro if I knew the
column where the total was located, but I would use the same algorithm that I
explained in my last posting. I wanted to make sure you were hapy with the
algorithm before I suggested a macro. I didn't want to write the macro and
then you didn't like the results.

"David" wrote:

Hi Joel,

I really appreciate your help, however this will give me a running total on
the new column where for example the first 100 records will add up to 100K,
and then i would have to start the same running total to get the next bucket.

Is there away that i can divide the whole 250 records into 4 buckets by
pressing a button or running a macro which would base the division on the
result of dcount and dsum. since i will be doing this in the future with much
larger file and more than 100 buckets

"Joel" wrote:

Your results won't be exact but close to the results you want.

1) Sort the records by amount
2) In a new column on the 1st record that is greater or equal to $500.00 put
this formula
=sum(C$100:C100)

Notice the dollar sign is on the first 100 only where the row number is the
1st row = 500.

3) copy formula down the worksheet.

4) Your buckets can be extract base on the new column amount

bucket 1 : the totals 0 to $100,000
bucket 2 : the totals 100,000 to $200,000
bucket 3 : the totals 200,000 to $500,000
bucket 4 : the totals 500,000 to $1,000,000
bucket 5 : the totals 1,000,000 to $2,000,000


"David" wrote:

Hi everyone,

I have a worksheet with over 20000 records, i did a dcount based on amounts
from 500-5000 and i got a result of 250 records, and dsum based on the same
criteria (500-5000) i got 2,000,000. further i want to distribute the number
of contracts and amount into 4 buckets as follow

Dsum result 10% 10% 30% 50%
total
1,000,000 100,000 100,000 300,000 500,000 1,000,000

Dcount result 10% 10% 30% 50% total
250 25 25 75 125 250

Can i somehow exctract, copy list of contracts in each bucket that will add
up to the dsum amount distribution for each bucket?

for example: a formula or macro that will give me 25 contracts out of 20,000
when i add the amounts it should come up to 100,000 and all contracts must be
between 500-5000.

I really appreciate any help i can get on this one.

This is complicated but lets try to get it to work.

1) Name the worksheet with all the contract "Contracts" or change code below

2) Change this line of code below to specify which column has the dollar
amount of each contract

Const AmountCol = "C"

3) Create a new worksheet called Awards

Col A Col B Col C
Row 1 Award Percent Min Max
Row 2 5% 5000 10000

This worksheet can have as many rows as required. This sheet will determine
the range sizes.


4) I made the range size 5% of the total. You said 3000 contracts. Which
has priority the 5% or 3000 contracts? I sorted the contracts in descending
order and then selected the largest amounts until the max was exceeded. The
algorithm to get the best fit is complicated. The greeks 2000 years ago
tried to solve this problem. There is a whole branch of mathematics call
"packing problems" that is devoted to this type problem.

I can improve this portion of the program later after we get the basics
working.


Sub MakeBuckets()

Const AmountCol = "C"

Application.DisplayAlerts = False

'Delete all worksheets except Awards and Contracts
For ShtCount = Sheets.Count To 1 Step -1
If Sheets(ShtCount).Name < "Awards" And _
Sheets(ShtCount).Name < "Contracts" Then

Sheets(ShtCount).Delete
End If
Next ShtCount

'create temporary sheet for making buckets
Set tmpsht = Sheets.Add(after:=Sheets(Sheets.Count))
tmpsht.Name = "Temporary"

With Sheets("Awards")
'get each bucket
RowCount = 2
Percent = .Range("A" & RowCount)
MinAward = .Range("B" & RowCount)
MaxAward = .Range("C" & RowCount)

With Sheets("Contracts")
'turn off autofilter
If .AutoFilterMode Then
.Cells.AutoFilter
End If

LastRow = .Range(AmountCol & Rows.Count).End(xlUp).Row
With .Columns(AmountCol & ":" & AmountCol)
.AutoFilter
End With


.Range(AmountCol & "2:" & AmountCol & LastRow).AutoFilter _
Field:=1, _
Criteria1:="=" & MinAward, _
Operator:=xlAnd, _
Criteria2:="<=" & MaxAward

With Sheets("Temporary")
'turn off autofilter
If .AutoFilterMode Then
.Cells.AutoFilter
End If
'clear temporary sheet
.Cells.ClearContents
End With

'copy filtered data to temporary sheet
.Cells.SpecialCells(Type:=xlCellTypeVisible).Copy _
Destination:=Sheets("Temporary").Cells

End With

With Sheets("Temporary")

'sort contracts highest to lowest
LastRow = .Range(AmountCol & Rows.Count).End(xlUp).Row
.Rows("1:" & LastRow).Sort _
key1:=.Range(AmountCol & "1"), _
order1:=xlDescending

'put totals in column IV
.Range("IV2").Formula = "=Sum(" & AmountCol & "$2:" & AmountCol & "2)"
'copy formula down worksheet
.Range("IV2").Copy _
Destination:=.Range("IV2:IV" & LastRow)

'Get Grand Total for range
RangeTotal = .Range("IV" & LastRow)
Awards = RangeTotal * Percent

'filter on formula in column IV
With .Columns("IV:IV")
.AutoFilter
End With


.Range("IV2:IV" & LastRow).AutoFilter _
Field:=1, _
Criteria1:="<=" & Awards


'create Award sheet sheet for making buckets
ShtName = MinAward & " - " & MaxAward

Set AwardSht = Sheets.Add(after:=Sheets(Sheets.Count))
AwardSht.Name = ShtName

'copy filtered data to Award sheet
.Cells.SpecialCells(Type:=xlCellTypeVisible).Copy _
Destination:=AwardSht.Cells

'remove column IV from the Award sheet
AwardSht.Columns("IV").Delete

End With
End With

With Sheets("Contracts")
'turn off autofilter
If .AutoFilterMode Then
.Cells.AutoFilter
End If
End With

Application.DisplayAlerts = False


End Sub


"David" wrote:

Hi Joel,

yes you are right the data is arranged properly, however let me explain this
a little further. the data that i am working with is very large, and there
are multiple ranges such as 0-5K, 5K-10K, and so on, even it goes upto
1M-10M, and each range will have over 100 bucket according to their last
months performance. which could be somewhere between 0.1% of each range to
50%,

and the amount is not the primary key for distribution, however number of
contract is. for example a bucket in 5k-10K is entitled to 3000 contract or
5% of the entire available contracts in the rage 5k-10K according his or her
last months performance, and the total amount in 5k-10K times 5% will result
in 5M, now i must sumbit 3K contracts to this person with the amount as close
as 5M.

Can i or rather you, lol write a macro that extracts 3K contracts out of
100K records with amount as close to 5K, please keep in mind the amount can
come close however the number of contracts must remain the same.

let me know if i have complicated this too much.

"Joel" wrote:

You dont have to start the running total for each bucket. I arranged the
buckets so the ranges get the results you are looking for. The 2nd bucket is
the sum is betsween 200,000 to 300,00. I can write a macro if I knew the
column where the total was located, but I would use the same algorithm that I
explained in my last posting. I wanted to make sure you were hapy with the
algorithm before I suggested a macro. I didn't want to write the macro and
then you didn't like the results.

"David" wrote:

Hi Joel,

I really appreciate your help, however this will give me a running total on
the new column where for example the first 100 records will add up to 100K,
and then i would have to start the same running total to get the next bucket.

Is there away that i can divide the whole 250 records into 4 buckets by
pressing a button or running a macro which would base the division on the
result of dcount and dsum. since i will be doing this in the future with much
larger file and more than 100 buckets

"Joel" wrote:

Your results won't be exact but close to the results you want.

1) Sort the records by amount
2) In a new column on the 1st record that is greater or equal to $500.00 put
this formula
=sum(C$100:C100)

Notice the dollar sign is on the first 100 only where the row number is the
1st row = 500.

3) copy formula down the worksheet.

4) Your buckets can be extract base on the new column amount

bucket 1 : the totals 0 to $100,000
bucket 2 : the totals 100,000 to $200,000
bucket 3 : the totals 200,000 to $500,000
bucket 4 : the totals 500,000 to $1,000,000
bucket 5 : the totals 1,000,000 to $2,000,000


"David" wrote:

Hi everyone,

I have a worksheet with over 20000 records, i did a dcount based on amounts
from 500-5000 and i got a result of 250 records, and dsum based on the same
criteria (500-5000) i got 2,000,000. further i want to distribute the number
of contracts and amount into 4 buckets as follow

Dsum result 10% 10% 30% 50%
total
1,000,000 100,000 100,000 300,000 500,000 1,000,000

Dcount result 10% 10% 30% 50% total
250 25 25 75 125 250

Can i somehow exctract, copy list of contracts in each bucket that will add
up to the dsum amount distribution for each bucket?

for example: a formula or macro that will give me 25 contracts out of 20,000
when i add the amounts it should come up to 100,000 and all contracts must be
between 500-5000.

I really appreciate any help i can get on this one.