Resume Real Spent with Initial Budget

[caluluaiol]

To All,
First, thanks to everyone who helps and contribute to this forum. Although this is my first post, the true is that i use a lot, when i have a problem or need some kind of guidance.

Today, i have a difficulty, and i don´t even know how to look for the solution or find simple tags to find my problem.
I will try to explain next, hoping that someone at least tell me that this is possible, or give some kind of guidance from where to start.

I have an Initial Monthly Budget, and then i have the Real Spent, by month too.
What i need is to automate (vba, formula, or hidden sheet) a table, that compares the Real Spent with the Budget, and gives how much Spent is from which month of the budget.
I really believe this is more a question of math, than functions and vba. But i really cannot see what path should i take to pull this off, so any help will be appreciated.

I upload an image so, you it´s more easily to understand.
So i have a budget in YELLOW, and the expenses in BLUE. What i need it´s to automate the third table, that tells me that in Month 1, i spent 15.000€ from the JAN budget, and 10.000€ from the FEV budget, and so on.

Any thoughts?
Thank you in advance

 

[Eric W]

Welcome to the MrExcel forum!

You can probably do this with formulas, but they'd be complicated. I actually started down that path and decided I could write it in VBA much quicker, and it would be more maintainable.

Starting with this sheet:

Book1
	A	B	C	D	E	F
1
2		Budget			REAL SPENT
3
4		JAN	25000		M1	15000
5		FEB	35000		M2	35000
6		MAR	50000		M3	17000
7		APR	17500		M4	40000
8					M5	20500
9
10		TOTAL	127500		TOTAL	127500
Sheet2

Cell Formulas
Range	Formula
C10	C10	=SUM(C4:C7)
F10	F10	=SUM(F4:F8)

I got to this:

Book1
	A	B	C	D	E	F	G	H	I	J
1
2		Budget			REAL SPENT			COMPARE
3
4		JAN	25000		M1	15000		JAN	M1	15000
5		FEB	35000		M2	35000		JAN	M2	10000
6		MAR	50000		M3	17000		FEB	M2	25000
7		APR	17500		M4	40000		FEB	M3	10000
8					M5	20500		MAR	M3	7000
9								MAR	M4	40000
10		TOTAL	127500		TOTAL	127500		MAR	M5	3000
11								APR	M5	17500
12
13										127500
Sheet2

Cell Formulas
Range	Formula
C10	C10	=SUM(C4:C7)
F10	F10	=SUM(F4:F8)

So to test it, open a new workbook and set up the sheet like the first mini-sheet I created. Then press Alt-F11 to open the VBA editor. Press Alt-IM to Insert a Module. Then paste this code in the window that opens:

Sub CompareBudget()
Dim BudTab As Variant, SpnTab As Variant, ResTab(1 To 10000, 1 To 3)
Dim br As Long, sr As Long, rr As Long, amt As Double, tot As Double

    BudTab = Range("B4:C7").Value
    SpnTab = Range("E4:F8").Value
    
    br = 1
    sr = 1
    rr = 0
    tot = 0
    
    Do
        amt = IIf(BudTab(br, 2) < SpnTab(sr, 2), BudTab(br, 2), SpnTab(sr, 2))
        rr = rr + 1
        ResTab(rr, 1) = BudTab(br, 1)
        ResTab(rr, 2) = SpnTab(sr, 1)
        ResTab(rr, 3) = amt
        tot = tot + amt
        
        BudTab(br, 2) = BudTab(br, 2) - amt
        If BudTab(br, 2) = 0 Then
            br = br + 1
            If br > UBound(BudTab) Then Exit Do
        End If
        
        SpnTab(sr, 2) = SpnTab(sr, 2) - amt
        If SpnTab(sr, 2) = 0 Then
            sr = sr + 1
            If sr > UBound(SpnTab) Then Exit Do
        End If
        
    Loop
    
    ResTab(rr + 2, 3) = tot
    Range("H4").Resize(rr + 2, 3) = ResTab
    
End Sub

There are 3 places in the code that reference ranges (B4:C7, E4:F8, and H4). Change these to match your workbook. If you can explain how your sheet is actually laid out, I can probably get the macro to automatically determine the ranges. Once you have the ranges set, close the VBA editor with Alt-Q.

Back in Excel, press Alt-F8 to open the macro editor. Select CompareBudget and click Run.


Hope this helps!

 

[caluluaiol]

Thank you so much for your time and to be able to provide your help.
I will try your solution right away, and will be back to you.

Yesterdays i was trying to get to a result using a matrix, but always get stuck in some part. Even with an IF formula it´s just start to be too big, and difficult to track!

 

[bebo021999]

Try with Matrix.
Yellow cells are hardcoded value, the rest are formulas.

Deciles.xlsx
	A	B	C	D	E	F
1		JAN	FEB	MAR	APR	TOTAL
2	M1	15,000	-	-	-	15,000
3	M2	10,000	25,000	-	-	35,000
4	M3	-	10,000	7,000	-	17,000
5	M4	-	-	40,000	-	40,000
6	M5	-	-	3,000	17,500	20,500
7	TOTAL	25,000	35,000	50,000	17,500	127,500
Sheet2

Cell Formulas
Range	Formula
B2:E6	B2	=MIN($F2-SUM($A2:A2),B$7-SUM(B$1:B1))
F7	F7	=SUM(B7:E7)

 

[caluluaiol]

It works perfectly! Really amazing.
With your solution, i think i can adapt this to any variable that maybe appear in my need.
Thank you once again!

There are 3 places in the code that reference ranges (B4:C7, E4:F8, and H4). Change these to match your workbook. If you can explain how your sheet is actually laid out, I can probably get the macro to automatically determine the ranges. Once you have the ranges set, close the VBA editor with Alt-Q.

I have the budget in one sheet, that could cover N periods of time. Sometime i have for example 18 months, sometimes more or sometimes less.
And in another sheet, would be a field with the monthly resume of the real expenses. That may have more or less months than the budget.

I don´t know if you can automate this variable ranges, but to be honest, what you came across it´s amazing and truly helpful.

 

[caluluaiol]

Try with Matrix.
Yellow cells are hardcoded value, the rest are formulas.

Deciles.xlsx
	A	B	C	D	E	F
1		JAN	FEB	MAR	APR	TOTAL
2	M1	15,000	-	-	-	15,000
3	M2	10,000	25,000	-	-	35,000
4	M3	-	10,000	7,000	-	17,000
5	M4	-	-	40,000	-	40,000
6	M5	-	-	3,000	17,500	20,500
7	TOTAL	25,000	35,000	50,000	17,500	127,500
Sheet2

Cell Formulas
Range	Formula
B2:E6	B2	=MIN($F2-SUM($A2:A2),B$7-SUM(B$1:B1))
F7	F7	=SUM(B7:E7)

Thank you for our time.
This works very well too! Thank you.

I was thinking in a matrix kind a solution, but i will never come across a "simple" formula like that!

 

[Eric W]

I have the budget in one sheet, that could cover N periods of time. Sometime i have for example 18 months, sometimes more or sometimes less.
And in another sheet, would be a field with the monthly resume of the real expenses. That may have more or less months than the budget.

It sounds like you have the ranges in different places. They could still be identified, but just so you know, here's how to set up the code to pull data (or save it) from a particular sheet:

    BudTab = Sheets("Budget").Range("B4:C7").Value
    SpnTab = Sheets("Expenses").Range("E4:F8").Value

    Sheets("Results").Range("H4").Resize(rr + 2, 3) = ResTab

Glad we could help!