Hi,

1. I have a list of about 10K contracts with amounts & effective dates
from which rents are to be paid.
2. These dates range between 01 April 2009 & 31 March 2011.
3. The list has a column of frequency of payments (between 1 & 12),
which means that rent is to be paid once or 12 times.


I have to find out the total monthly rental payable - from 01 April
2009 until 31 March 2011.

The table looks something like this

ContractCode RentAmount RentEffectiveFrom Frequency
1 1000 01.05.2009 3
1 1100 01.08.2009 3
1 1200 01.11.2009 3
2 100 01.04.2009 12
2 120 01.04.2010 12

The result expected is something like this

CCode Apr09 May09 Jun09 Jul09 Aug09 Sep09
Oct09 Nov09 Dec09 Jan09 Feb09 Mar09 Apr10
1 1000 1000 1000
1
1000 1100 1100
1
1200 1200 1200

2 100 100 100 100 100
100 until Mar09
2
Apr10 to Mar11


I'm certain this is possible with either VBA or with formulas - can
you please help?

Thanks