Conditionally populate a data table

[Neil]

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

 

[Gary Brown]

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.

 

[Neil]

Hi Gary,

Thank you - this works, partly.

There could be instances however where the rental effective date is
not the 1st of every month (could be the 2nd), & the precision
required will not be met. Under-estimating the rent payable over the
10,000 odd contracts could be potentially a major cash flow problem.

Maybe there's a workaround?

Many thanks once again

Neil

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 withthe
'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

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
.- Hide quoted text -

- Show quoted text -