HI there all,

I'm hoping someone could help me.

I currently have 2 Separate houses that I let out to 2 separate tenants. Lets call them Sally in house A, and Jane in house B lol.

The problem is... is that both tenants get benefit for part of their 4 weekly rent, then they have to make up the shortfall themselves.. e.g. If I'm charging £550 every 4 weeks - I will get a £450 payment from the government as their Housing benefit, then the tenant makes up the difference every 2 weeks by paying me £50. All payments go staring in to my bank account.

Therefore... for May - from Tenant Sally this is:
£450 Benefit
£50 First 2 weeks
£50 Second 2 weeks
= £550 in that 4 week cycle.

Its becoming quite complicated recently since randomly Sally could get a grant, for say £300 from the government which go straight in to my account separately... they don't even send me a letter lol.
That means Sally is + in her account with me.... I owe her money.

Sometime in the past also... her housing benefit amount could go up or down... where she owes me or I owe her.

How we've solved it in the past is Sally just stops paying me her portion of the rent... in this instance 3x4 Week cycles = £300. Then she re-instates her payment and all's balanced again.

I've got another house now - Jane in house B - and this is starting again lol.

As you can see this is a nightmare for tracking.... As I don't want to be conning them nor for defs I don't want to loose money.

I was wondering if anyone knows how to do the following;

At the min I download my statements every month via an Excel file from my banks website.
I copy this data in to a new sheet in my budgeting workbook.

Soo.... May's data is shown as sheet MAY10.

What I normally do then is to trawl through the statement (ctrl-f mostly) and find the payments from the Housing Benefit for each tenant... and their payment.

What I'd like is for excel to show this for me... e.g. in a calculations worksheet show me what has come in each month (and what date) and whether the tenant is + or - for that month.

Sorry this is soo long... i hope I haven't overdone it but pretty complicated (to me lol)