Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |