Assumptions:
- Headings are in row 1
- Data starts in row 2
- Col A = Contract Code
- Col B = Rent Amount
- Col C = effective date (1st of month date)
- Col D -= frequency of pymt
- Col E thru Col AB = Month Headings - Apr-09 thru Mar-11
- Month Headings are actually dates (1st of the month)
- i.e. Apr-09 is actually 01.04.2009 /
May-09 is actually 01.05.2009, etc
- Analysis ToolPak is installed
[This is an addin that comes with Excel 2003 and lower but is usually not
activated. It is a part of Excel 2007. If this addin is not installed, the
formula below will give the #NAME? error because the EoMonth( ) function is
used. To install the addin, go to TOOLS ADD-INS... and check Analysis
ToolPak.]

In cell E2, put the formula...
=IF(AND(E$1=$C2,E$1<=EOMONTH($C2,$D2-1)),$B2,0)

Copy this formula down and across. It is recommended to format it with the
'comma' format so that if the result = 0, a dash ( - ) will appear.

--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"Neil" wrote:

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
.