Sal,

I'm posting below 5 procedures that will produce the summary you're
after.

The assumption is that the 4 columns of original data have been loaded
into A1:D31 using the example data below. (I used the DATA Text to
Coumns, after I'd posted your CSV details into column A)

The solution will be shown in cols G:N ( Obviously if you want to
change the positioning you'll have to adjust the code)

You should attach the first procedure (GainLossCalc) to a macro button

Rgds

Sub GainLossCalc()
Call CopyData
Call UnitPrice
Call OpenCloseID
Call GainLoss
Call TidyUp
End Sub

Sub CopyData()
[A1].CurrentRegion.Copy Destination:=Range("G1")
[F1] = "IDUsed": [K1] = "Unit Price": [L1] = "Open ID"
[M1] = "Close ID": [N1] = "Gain / Loss"
End Sub

Sub UnitPrice()
[K2] = "=j2/h2"
[K2].Copy
Range([J3], [J3].End(xlDown)).Offset(0, 1).PasteSpecial
(xlPasteAll)
End Sub

Sub OpenCloseID()
Dim stFirst As String
Dim iOpenID As Integer
Dim x As Integer, y As Integer
Dim iBal As Integer
Dim iCurRow As Integer

If [B2] 0 Then stFirst = "+" Else stFirst = "-"

For x = 1 To [A1].CurrentRegion.Rows.Count

If [B2].Cells(x, 1) 0 And stFirst = "+" Then
iOpenID = x
iBal = [B2].Cells(x, 1)
End If
If [B2].Cells(x, 1) < 0 And stFirst = "-" Then
iOpenID = x
iBal = [B2].Cells(x, 1)
End If

y = 1
Do While iBal < 0
If (stFirst = "+" And [B2].Cells(x + y) < 0 _
Or stFirst = "-" And [B2].Cells(x + y) 0) _
And [B2].Cells(x + y).Offset(0, 4) < "X" Then
iBal = iBal + [B2].Cells(x + y)
[B2].Offset(x + y - 1, 4) = "X"
[L65536].End(xlUp).Offset(1, 0) = iOpenID
[M65536].End(xlUp).Offset(1, 0) = x + y
End If
y = y + 1
Loop
Next
End Sub

Sub GainLoss()
[N2] =
"=-VLOOKUP(L2,A:D,4)/VLOOKUP(L2,A:D,2)*1000-VLOOKUP(M2,A:D,4)" _
& "/VLOOKUP(M2,A:D,2)*1000"
[N2].Copy
Range([M3], [M3].End(xlDown)).Offset(0, 1).PasteSpecial
(xlPasteAll)
End Sub

Sub TidyUp()
[F:F].ClearContents
[G1].CurrentRegion.Copy: [G1].PasteSpecial (xlPasteValues)
Application.CutCopyMode = False
End Sub






On Mon, 17 Oct 2005 12:06:03 -0700, Sal Young
wrote:


Here's a sample of the original data:
ID,QTY,CODE,Amount,
1,2000,C,285310,
2,1000,C,143630,
3,-1000,V,144975,
4,1000,C,143990,
5,-1000,V,144960,
6,-1000,V,145774,
7,2000,C,285720,
8,-1000,V,145762.4,
9,1000,C,145610,
10,1000,C,145380,
11,1000,C,145880,
12,-1000,V,145598,
13,-1000,V,145454.18,
14,1000,C,143905,
15,-1000,V,145705,
16,2000,C,286540
17,-1000,V,145595
18,-1000,V,145710.64
19,1000,C,145425
20,-1000,V,145702
21,-1000,V,145750
22,1000,C,143390
23,-1000,V,145827.72
24,-1000,V,145903
25,2000,C,285220
26,-1000,V,145702.55
27,-1000,V,145625
28,-1000,V,145738
29,1000,C,145525
30,-1000,V,144975


Here's a sample of the desire goal:
ID,QTY,CODE,Amount,Unit Price,OpenID,CloseID,Gain/Loss
1,2000,C,285310,142.655,1,3,2320
2,1000,C,143630,143.63,1,5,2305
3,-1000,V,144975,-144.975,2,6,2144
4,1000,C,143990,143.99,4,8,1772.4
5,-1000,V,144960,-144.96,7,12,2738
6,-1000,V,145774,-145.774,7,13,2594.18
7,2000,C,285720,142.86,9,15,95
8,-1000,V,145762.4,-145.7624,10,17,215
9,1000,C,145610,145.61,11,18,-169.36
10,1000,C,145380,145.38,14,20,1797
11,1000,C,145880,145.88,16,21,2480
12,-1000,V,145598,-145.598,16,23,2557.72
13,-1000,V,145454.18,-145.45418,19,24,478
14,1000,C,143905,143.905,22,26,2312.55
15,-1000,V,145705,-145.705,25,27,3015
16,2000,C,286540,143.27,25,28,3128
17,-1000,V,145595,-145.595,29,30,-550
18,-1000,V,145710.64,-145.71064,,,
19,1000,C,145425,145.425,,,
20,-1000,V,145702,-145.702,,,
21,-1000,V,145750,-145.75,,,
22,1000,C,143390,143.39,,,
23,-1000,V,145827.72,-145.82772,,,
24,-1000,V,145903,-145.903,,,
25,2000,C,285220,142.61,,,
26,-1000,V,145702.55,-145.70255,,,
27,-1000,V,145625,-145.625,,,
28,-1000,V,145738,-145.738,,,
29,1000,C,145525,145.525,,,
30,-1000,V,144975,-144.975,,,


" wrote:

Sal:

I'll take a shot at this though I must say that your question
leaves much to guess at. If this "Table" resides in cells(A1:E5) then
your formulas would be as follows for cells(C2:E5)

C2:=A2*B2
C3:=A3*B3
C4:=A4*B4
C5:=A5*B5

D2:=A2
D3:=SUM(D2,A3)
D4:=SUM(D3,A4)
D5:=SUM(D4,A5)

E2:=C2
E3:=SUM(E2,C3)
E4:=SUM(E3,C4)
E5:=SUM(E4,C5)

Once you have the formulas in place, you can just autofill them down
the length of your sheet and they should do what it is I think you are
asking of it. Lemme know if I got it right or missed the point
completely...Mark




I need to write some kind of balance sheet where the balance is calculated
based on how much a quantity of one record covers the quantity of another
record with the opposite sign. Here's an example.

QTY UNITCOST TotCost BalQTY BalAMT
500 200 100,000 500 100,000
-100 210 -21,000 400 79,000
500 205 102,500 900 181,500
-600 210 -126,000 300 55,500


The columns & values I have to generate are the BalQTY and BalAMT. I wan to
point out that a record may close multiple records as demonstrated with the
first record closing record 2 (qty 100) and partially record 4 (qty 400).

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________