Arrays
 

I have a list of transactions that i would like to ditribute to a column
named volume . The transactions have a start date, a enddate, a portfolio
name, the volume and a price. The volumes are to be distributed on the dates
that fits to the transactions. ie. if a transactions have a start date of
2005/1/1 and a endate of 2005/1/31 i would like to have the volume
distributed on the dates that i have in column a, based on the portfolio. The
transactions look like below, and the list that i would like to create is
below the transactions.

I have tried to use arrays, but i cant really figure out how to do this.
Does anyone have any suggestions on how to do this? I have done it with
formulas, but as I have thousands of transactions it takes ages to calculate.

Portfolio startdate Enddate Volume Price
Portfolio A 01.01.05 31.01.05 5 32


Portolio A Portfolio B

Volume Volume
01.01.05 5 0
02.01.05 5 0
03.01.05 5 0
04.01.05 5 0



--
Peter B
Norway

Arrays
 

Hi
use SUMPRODUCT. See:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

"Peter B" wrote:

I have a list of transactions that i would like to ditribute to a column
named volume . The transactions have a start date, a enddate, a portfolio
name, the volume and a price. The volumes are to be distributed on the dates
that fits to the transactions. ie. if a transactions have a start date of
2005/1/1 and a endate of 2005/1/31 i would like to have the volume
distributed on the dates that i have in column a, based on the portfolio. The
transactions look like below, and the list that i would like to create is
below the transactions.

I have tried to use arrays, but i cant really figure out how to do this.
Does anyone have any suggestions on how to do this? I have done it with
formulas, but as I have thousands of transactions it takes ages to calculate.

Portfolio startdate Enddate Volume Price
Portfolio A 01.01.05 31.01.05 5 32


Portolio A Portfolio B

Volume Volume
01.01.05 5 0
02.01.05 5 0
03.01.05 5 0
04.01.05 5 0



--
Peter B
Norway

Arrays
 

I have tried sumproduct, but I have so many portfolios and "dates" that it
takes ages to calculate.

"Frank Kabel" wrote:

Hi
use SUMPRODUCT. See:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

"Peter B" wrote:

I have a list of transactions that i would like to ditribute to a column
named volume . The transactions have a start date, a enddate, a portfolio
name, the volume and a price. The volumes are to be distributed on the dates
that fits to the transactions. ie. if a transactions have a start date of
2005/1/1 and a endate of 2005/1/31 i would like to have the volume
distributed on the dates that i have in column a, based on the portfolio. The
transactions look like below, and the list that i would like to create is
below the transactions.

I have tried to use arrays, but i cant really figure out how to do this.
Does anyone have any suggestions on how to do this? I have done it with
formulas, but as I have thousands of transactions it takes ages to calculate.

Portfolio startdate Enddate Volume Price
Portfolio A 01.01.05 31.01.05 5 32


Portolio A Portfolio B

Volume Volume
01.01.05 5 0
02.01.05 5 0
03.01.05 5 0
04.01.05 5 0



--
Peter B
Norway

Arrays
 

Option Explicit
Sub Tester2()
Dim dt1 As Date, dt2 As Date
Dim dblVol As Double
Dim lNum As Long, k As Long
Dim varr() As Variant
dt1 = CDate("1/1/2005")
dt2 = CDate("1/31/2005")
lNum = CLng(dt2 - dt1 + 1)
dblVol = 5
ReDim varr(1 To lNum, 1 To 3)
For k = 1 To lNum
varr(k, 2) = dblVol
varr(k, 3) = 0
varr(k, 1) = dt1 + (k - 1)
Next

For k = 1 To lNum
Debug.Print Format(varr(k, 1), "dd.mm.yy") & " " _
& varr(k, 2) & " " & varr(k, 3)
Next

End Sub

produced:
01.01.05 5 0
02.01.05 5 0
. . .
30.01.05 5 0
31.01.05 5 0

--
Regards,
Tom Ogilvy


"Peter B" wrote in message
...
I have tried sumproduct, but I have so many portfolios and "dates" that it
takes ages to calculate.

"Frank Kabel" wrote:

Hi
use SUMPRODUCT. See:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

"Peter B" wrote:

I have a list of transactions that i would like to ditribute to a
column
named volume . The transactions have a start date, a enddate, a
portfolio
name, the volume and a price. The volumes are to be distributed on
the dates
that fits to the transactions. ie. if a transactions have a start date
of
2005/1/1 and a endate of 2005/1/31 i would like to have the volume
distributed on the dates that i have in column a, based on the
portfolio. The
transactions look like below, and the list that i would like to create
is
below the transactions.

I have tried to use arrays, but i cant really figure out how to do
this.
Does anyone have any suggestions on how to do this? I have done it
with
formulas, but as I have thousands of transactions it takes ages to
calculate.

Portfolio startdate Enddate Volume Price
Portfolio A 01.01.05 31.01.05 5 32


Portolio A Portfolio B

Volume Volume
01.01.05 5 0
02.01.05 5 0
03.01.05 5 0
04.01.05 5 0



--
Peter B
Norway

Arrays
 

Hi
this is IMHO the only option if you really need a dynamic solution.
Otherwise you have to use a pivot table or a macro (e.g. the one Tom
provided)

--
Regards
Frank Kabel
Frankfurt, Germany

Peter B wrote:
I have tried sumproduct, but I have so many portfolios and "dates"
that it takes ages to calculate.

"Frank Kabel" wrote:

Hi
use SUMPRODUCT. See:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

"Peter B" wrote:

I have a list of transactions that i would like to ditribute to a
column named volume . The transactions have a start date, a
enddate, a portfolio name, the volume and a price. The volumes are
to be distributed on the dates that fits to the transactions. ie.
if a transactions have a start date of 2005/1/1 and a endate of
2005/1/31 i would like to have the volume distributed on the dates
that i have in column a, based on the portfolio. The transactions
look like below, and the list that i would like to create is below
the transactions.

I have tried to use arrays, but i cant really figure out how to do
this. Does anyone have any suggestions on how to do this? I have
done it with formulas, but as I have thousands of transactions it
takes ages to calculate.

Portfolio startdate Enddate Volume Price
Portfolio A 01.01.05 31.01.05 5 32


Portolio A Portfolio B

Volume Volume
01.01.05 5 0
02.01.05 5 0
03.01.05 5 0
04.01.05 5 0



--
Peter B
Norway