Bucket filling and splitting loan numbers based on range values

rushabh21 · Jul 2, 2012

Hi folks,

This is one of the best forum around for excel.

I have a problem to crack.

[pre]

Code:

Assume list of loan numbers as : -
Loan	Principal Balance
L1	$3,000.00
L2	$6,000.00
L3	$8,000.00
L4	$5,500.00
L5	$6,000.00
L6	$6,500.00
L7	$4,000.00
L8	$9,000.00
L9	$10,000.00
L10	$150,000.00

There are buckets they need to fit

Buckets	        Buckets size
Bucket 1	$5,000.00
Bucket 2	$8,000.00
Bucket 3	$6,000.00
Bucket 4	$7,000.00
Bucket 5	$7,000,000.00								

Solution should look like this
--------------------------
&#39;Loan&#39;	&#39;Amount of Principal Balance&#39; 	&#39;Bucket&#39;
L1	$3,000.00	BUCKET 1
L2	$2,000.00	BUCKET 1	At this point bucket 1 is full but loan 2 has 3k remaining
L2	$3,000.00	BUCKET 2	At this point bucket 2 can take 5k capacity left
L3	$5,000.00	BUCKET 2	At this point bucket 2 is full
L3	$3,000.00	BUCKET 3	At this point bucket 3 has 3k capacity left
L4	$3,000.00	BUCKET 3	At this point bucket 3 is full
L4	$2,500.00	BUCKET 4	At this point bucket 4 has 4500 capacity left
L5	$4,500.00	BUCKET 5	At this point bucket 5 has enough capacity to handle remaining loan
L6	$6,500.00	BUCKET 5
L7	$4,000.00	BUCKET 5
L8	$9,000.00	BUCKET 5
L9	$10,000.00	BUCKET 5
L10	$150,000.00	BUCKET 5

[/pre]
In general the 10 loans are now reported in 13 rows…

Based on the bucket value, each row will have respective calculation. This solution is applicable for about 50k loans.

I think its not possible without a VB or Macro.

Someone please help

Luke M · Jul 2, 2012

How's this?

[pre]

Code:

Sub FillBuckets()
Dim LoanRange As Range
Dim BucketRange As Range
Dim xBalance As Double
Dim xBucket As Double
Dim xCheck As Double
Dim LoanCount As Integer
Dim BucketCount As Integer

LoanCount = 1
BucketCount = 1
i = 1

Set LoanRange = Range(&#34;A2:B11&#34;)
Set BucketRange = Range(&#34;A17:B21&#34;)

xBucket = BucketRange.Cells(BucketCount, 2).Value
xBalance = LoanRange.Cells(LoanCount, 2).Value
Application.ScreenUpdating = False
Do
xCheck = xBucket - xBalance

Select Case xCheck

Case Is &#62; 0 &#39;Bucket is bigger than remaining loan
Cells(i, &#34;D&#34;) = LoanRange.Cells(LoanCount, 1)
Cells(i, &#34;E&#34;) = xBalance
Cells(i, &#34;F&#34;) = BucketRange.Cells(BucketCount, 1)

xBucket = xBucket - xBalance
LoanCount = LoanCount + 1
xBalance = LoanRange.Cells(LoanCount, 2)

Case 0 &#39;Loan fills bucket perfectly
Cells(i, &#34;D&#34;) = LoanRange.Cells(LoanCount, 1)
Cells(i, &#34;E&#34;) = xBalance
Cells(i, &#34;F&#34;) = BucketRange.Cells(BucketCount, 1)

LoanCount = LoanCount + 1
BucketCount = BucketCount + 1
xBucket = BucketRange.Cells(BucketCount, 2)

xBalance = LoanRange.Cells(LoanCount, 2)

Case Is &#60; 0 &#39;Loan is bigger than bucket
Cells(i, &#34;D&#34;) = LoanRange.Cells(LoanCount, 1)
Cells(i, &#34;E&#34;) = xBucket
Cells(i, &#34;F&#34;) = BucketRange.Cells(BucketCount, 1)

xBalance = xBalance - xBucket
BucketCount = BucketCount + 1
xBucket = BucketRange.Cells(BucketCount, 2)

End Select

i = i + 1

Loop While BucketCount &#60;= BucketRange.Rows.Count And LoanCount &#60;= LoanRange.Rows.Count

Application.ScreenUpdating = True

End Sub

[/pre]

Hui · Jul 3, 2012

Rushabh21

Firstly, Welcome to the Chandoo.org forums.

You can also use the technique I discussed here: http://chandoo.org/wp/2010/11/18/scheduling-variable-sources/

I have placed your data in a mockup: https://www.dropbox.com/s/vflv4mgtfkr4u51/Buckets.xls

rushabh21 · Jul 3, 2012

Luke M....I tried your solution and seems to be producing accurate results..

I think this issue is solved.

Excel Ninja...I am unable to pull your spreadsheet at work since its blocked..I am sure your solution will also work...

Will look at it at home..

THANKS a BUNCH for your replies..

I have been dreaming for this day@!!!!!

Luke M · Jul 3, 2012

Always glad we could help. Let us know if you run into any other issues.

rushabh21 · Jul 3, 2012

Hi Luke,

Wanted to add another twist to this

Each loan has an indicator 1 or 2

If indicator is 1 then use bucket 1

If indicator is 2 then use bucket 2

Bucket 1 and 2 are similar but has different bucket sizes.

Loan numbers will be sorted by indicator in asc so they will be grouped together.

Something like this

'Loan' 'Principal Balance' 'Indicator'

L1 $3,000.00 1

L2 $6,000.00 1

L3 $8,000.00 1

L4 $5,500.00 1

L5 $6,000.00 2

L6 $6,500.00 2

L7 $4,000.00 2

L8 $9,000.00 2

L9 $10,000.00 2

L10 $150,000.00 2

'Buckets 1' 'Buckets size'

A $2,500.00

B $3,000.00

C $8,000.00

D $8,500.00

E $7,000,000.00

'Buckets 2' 'Buckets size'

X $5,000.00

Y $6,500.00

Z $7,000,000.00

Solution should look like this

---------------------------------

'Lo NO' 'UPB in each bucket' 'Bucket'

L1 $2,500.00 A

L1 $500.00 B

L2 $2,500.00 B

L2 $3,500.00 C

L3 $4,500.00 C

L3 $3,500.00 D

L4 $5,000.00 D

L4 $500.00 E

L5 $5,000.00 X

L5 $1,000.00 Y

L6 $5,500.00 Y

L6 $1,000.00 Z

L7 $4,000.00 Z

L8 $9,000.00 Z

L9 $10,000.00 Z

L10 $150,000.00 Z

Luke M · Jul 3, 2012

Adjusted to account for multiple buckets. Has the ability to add more buckets if needed.

[pre]

Code:

Sub FillBuckets()
Dim LoanRange As Range
Dim BucketRange As Range
Dim xBalance As Double
Dim xBucket As Double
Dim xCheck As Double
Dim LoanCount As Integer
Dim BucketCount As Integer
Dim i As Integer
Dim xIndicate As Integer

Set LoanRange = Range(&#34;A2:C11&#34;)

LoanCount = 1
i = 1

xBalance = LoanRange.Cells(LoanCount, 2).Value
xIndicate = LoanRange.Cells(1, 3).Value

&#39;Set a restart point, setup the different buckets
BeginLoop:
If xIndicate = 1 Then
Set BucketRange = Range(&#34;A17:B21&#34;)
ElseIf xIndicate = 2 Then
Set BucketRange = Range(&#34;A24:B26&#34;)
Else
&#39;Room for other buckets
End If
BucketCount = 1
xBucket = BucketRange.Cells(BucketCount, 2)

Application.ScreenUpdating = False
Do
xCheck = xBucket - xBalance

Select Case xCheck

Case Is &#62; 0 &#39;Bucket is bigger than remaining loan
Cells(i, &#34;D&#34;) = LoanRange.Cells(LoanCount, 1)
Cells(i, &#34;E&#34;) = xBalance
Cells(i, &#34;F&#34;) = BucketRange.Cells(BucketCount, 1)

xBucket = xBucket - xBalance
LoanCount = LoanCount + 1
xBalance = LoanRange.Cells(LoanCount, 2)
If LoanRange.Cells(LoanCount, 3) &#62; LoanRange.Cells(LoanCount - 1, 3) Then
xIndicate = LoanRange.Cells(LoanCount, 3).Value
GoTo BeginLoop
End If
Case 0 &#39;Loan fills bucket perfectly
Cells(i, &#34;D&#34;) = LoanRange.Cells(LoanCount, 1)
Cells(i, &#34;E&#34;) = xBalance
Cells(i, &#34;F&#34;) = BucketRange.Cells(BucketCount, 1)

LoanCount = LoanCount + 1
BucketCount = BucketCount + 1
xBucket = BucketRange.Cells(BucketCount, 2)

xBalance = LoanRange.Cells(LoanCount, 2)

Case Is &#60; 0 &#39;Loan is bigger than bucket
Cells(i, &#34;D&#34;) = LoanRange.Cells(LoanCount, 1)
Cells(i, &#34;E&#34;) = xBucket
Cells(i, &#34;F&#34;) = BucketRange.Cells(BucketCount, 1)

xBalance = xBalance - xBucket
BucketCount = BucketCount + 1
xBucket = BucketRange.Cells(BucketCount, 2)

End Select

i = i + 1

Loop While BucketCount &#60;= BucketRange.Rows.Count And LoanCount &#60;= LoanRange.Rows.Count

Application.ScreenUpdating = True

End Sub

[/pre]

rushabh21 · Jul 3, 2012

There is something wrong. When I move the macro to existing spreadsheet, my buckets are at different location. So here are the changes I made,

Set LoanRange = Range("A36:C48")

===

i = 36

====

If xIndicate = 1 Then

Set BucketRange = Range("A22:B26")

ElseIf xIndicate = 2 Then

Set BucketRange = Range("A29:B31")

====

What should xIndicate = LoanRange.Cells(1, 3).Value be???

Also I think the "Do" loop should have the if then else for xIndicate

rushabh21 · Jul 3, 2012

Also if I change the Indicator order where 2 comes first and then 1...the script doesnt work right...

sorry I maybe asking for too much here

Luke M · Jul 3, 2012

Change the i=36 back to i=1. the i variable is keeping track of which row we are in relative to the LoanRange. So, LoanRange.Cells(1,2) refers to cell B36, as that is the cell in the first row, second column of LoanRange.

That said, the starting line of:

[pre]

Code:

xIndicate = LoanRange.Cells(1, 3).Value

[/pre]
Does not need to be changed, as the code is just setting the variable to the initial value of the first row, 3rd column (ie, cell C36).

We don't need to have the If then else in the Do Loop as the macro uses a GoTo function instead. I did it this way so that we can use a single variable for BucketRange and it's easier to add additional bucket ranges if needed.

Luke M · Jul 3, 2012

Regarding the order, yes, the problem is with this line:

[pre]

Code:

If LoanRange.Cells(LoanCount, 3) &#62; LoanRange.Cells(LoanCount - 1, 3) Then
If the order can change (but loans are still "grouped" by indicator) then code should be:

If LoanRange.Cells(LoanCount, 3) &#60;&#62; LoanRange.Cells(LoanCount - 1, 3) And _
LoanCount &#60; LoanRange.Rows.Count Then

[/pre]

rushabh21 · Jul 3, 2012

I have to change i to 36 since the result after running should be printed on row 36 and not row 1.

rushabh21 · Jul 3, 2012

The new If then else is better

rushabh21 · Jul 3, 2012

Actually the last loan when it switches from indicator 2 to 1..is not showing up on the result. Something is dropping it

Luke M · Jul 3, 2012

Ah, I'd forgotten what i was controlling. Doh! That's what I get for having too much code floating around. However, that helps me see what the problem is w/ regard to loan not showing up. Macro does the Go To function w/o incrementing i, thus the 1 record is getting overwritten. Change block of code to

[pre]

Code:

If LoanRange.Cells(LoanCount, 3) &#62; LoanRange.Cells(LoanCount - 1, 3) Then
xIndicate = LoanRange.Cells(LoanCount, 3).Value
i = i + 1 &#39;Still need to increment value
GoTo BeginLoop
End If

[/pre]

rushabh21 · Jul 3, 2012

You mean

If LoanRange.Cells(LoanCount, 3) <> LoanRange.Cells(LoanCount - 1, 3) And LoanCount < LoanRange.Rows.Count Then

xIndicate = LoanRange.Cells(LoanCount, 3).Value

i = i + 1 'Still need to increment value

GoTo BeginLoop

End If

Luke M · Jul 3, 2012

Yep. Again, forgive my errors. It's been a long day...time to go home. =(

rushabh21 · Jul 3, 2012

I think this is working now.

Thanks again for all the help

Bucket filling and splitting loan numbers based on range values

rushabh21

New Member

Luke M

Excel Ninja

Hui

Excel Ninja

rushabh21

New Member

Luke M

Excel Ninja

rushabh21

New Member

Luke M

Excel Ninja

rushabh21

New Member

rushabh21

New Member

Luke M

Excel Ninja

Luke M

Excel Ninja

rushabh21

New Member

rushabh21

New Member

rushabh21

New Member

Luke M

Excel Ninja

rushabh21

New Member

Luke M

Excel Ninja

rushabh21

New Member