Conditionally populate a data table
 

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

Conditionally populate a data table
 

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
.

Conditionally populate a data table
 

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



On Mar 26, 12:09*am, Gary Brown <junk_at_kinneson_dot_com wrote:
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
.- Hide quoted text -

- Show quoted text -