PDA

View Full Version : Grouping data



Lartk
11-05-2012, 09:00 AM
On the attached workbook, in the "Now" sheet represents my output now. I use this macro to divide up each cusip:

Sub ProcessData4()
Dim lastrow As Long
Dim endat As Long
Dim i As Long

With ActiveSheet

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = lastrow To 5 Step -1

endat = i
Do While .Cells(i - 1, "C").Value = .Cells(i, "C").Value And i - 1 >= 5
i = i - 1
Loop
.Rows(endat + 1).Insert
.Cells(endat + 1, "A") = "Total Quantity:"
.Cells(endat + 1, "I").FormulaR1C1 = "=SUM(R[-1]C:R" & i & "C)"
.Rows(i).Resize(2).Insert
.Cells(i + 1, "A").Value = "Trade Allocation:"
Next i
End With
End Sub

However, as you can see, the cusips are separated by I would also like the buys and sells grouped for each cusip. So can I take the above macro one step further and have it not only group each cusip separately but also group the buy and sell (in column E, B=buy, s = sell) for each cusip? My desired results are on the "Results" sheet.

Lartk
11-05-2012, 10:47 AM
Sub testing2()
lr = Cells(Rows.Count, "G").End(xlUp).Row
For Rw = lr To 6 Step -1
If (Cells(Rw, "C").Value <> Cells(Rw + 1, "C").Value) Or (Cells(Rw, "E").Value <> Cells(Rw + 1, "E").Value) Then
Rows(Rw + 1).Insert
End If
Next Rw
End Sub


This step separates the buys and sells for each cusip in column C. However, Instead of just added one line in between, i need to insert three rows so I can do the "Total Quantity" line and sum total quantity and then put "Trade Allocation" in too.