Hi guys,

I have the following problem.

I have a database of contracts of my clients. I want to have a certain
tool that gives me all the contracts that come to end in 2006,2007,....
The contract end date is specified in the database, so this isn't the
problem.

So, it would something like
'if maturity date is less than one year then report the value of the
contract in the column 2006'
'if ..... 2years
2007'


and so until 2010.


somebody an idea??

thankx

mat

You appear to have a contradiction in your requirement. You state contracts
which end in 2006, 2007 etc ..but then say 'if maturity date is less than one
year ' so under the latter a contract ending 31 Jan 2007 would be in 2006
category as its maturity date is less than one year from now.

Are you simply wanting to list contracts with an end date in a given year so
all we (you) need to do is test the YEAR of the contract end date and list in
appropriate column?

How do you want the ouput formatted?

"matthias" wrote:

Hi guys,

I have the following problem.

I have a database of contracts of my clients. I want to have a certain
tool that gives me all the contracts that come to end in 2006,2007,....
The contract end date is specified in the database, so this isn't the
problem.

So, it would something like
'if maturity date is less than one year then report the value of the
contract in the column 2006'
'if ..... 2years
2007'


and so until 2010.


somebody an idea??

thankx

mat

You are right, i just want to list contracts with an end date in 2006
in the column 2006 and so on

about the output,

so i would like to have columns from 2006 to 2010. all the contracts
that end in 2006 are listed in 2006,...

from these contracts that fall e.g. under the column 2006, I would like
to list the name of the person and the contract value. (would this be
with a lookup funtion or so)

sorry if I am not being clear... but just ask me and i will try to make
it clearer

thanks a lot

mat

.... So output is two columns per year ?:


2006 2007 etc
Name Value Name Value
col 1 col 2 col 3 col 4 ....

What are the columns for your input i.e date, name and value and are they
sorted/can they be sorted e.g. by date?

Thanks.


"matthias" wrote:

You are right, i just want to list contracts with an end date in 2006
in the column 2006 and so on

about the output,

so i would like to have columns from 2006 to 2010. all the contracts
that end in 2006 are listed in 2006,...

from these contracts that fall e.g. under the column 2006, I would like
to list the name of the person and the contract value. (would this be
with a lookup funtion or so)

sorry if I am not being clear... but just ask me and i will try to make
it clearer

thanks a lot

mat

let me be more specific

e.g.
contracts end term value
client 1 5/2006 1000
client 2 6/2007 2000

so if want this

2006 2007
client 1 1000 client2 2000

the clients can be sorted by name

Hi,
Try this ... no sorting.

Sub a()

Dim ws1 As Worksheet, ws2 As Worksheet
Dim lastrow As Long, r As Long
Dim baseyear As Integer, i As Integer, yy As Integer, icol As Integer

Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")

baseyear = Year(Date)

With ws2
.Cells.ClearContents
For i = 1 To 5 ' Set for 5 years
.Cells(1, (i - 1) * 2 + 1) = baseyear + i - 1
.Cells(2, (i - 1) * 2 + 1).Resize(1, 2) = Array("Client", "Value")
Next i
End With

With ws1
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
For r = 2 To lastrow
yy = Year(.Cells(r, 2))
icol = (yy - baseyear) * 2 + 1
ws2.Cells(Rows.Count, icol).End(xlUp)(2) = .Cells(r, "A")
ws2.Cells(Rows.Count, icol + 1).End(xlUp)(2) = .Cells(r, "C")
Next r
End With

End Sub

HTH

"matthias" wrote:

let me be more specific

e.g.
contracts end term value
client 1 5/2006 1000
client 2 6/2007 2000

so if want this

2006 2007
client 1 1000 client2 2000

the clients can be sorted by name