PDA

View Full Version : Separate rows depending on specific criteria



Lartk
11-23-2012, 07:57 AM
Please see the attached workbook. The "Current" sheet is how things look right now. I you can see, in Column C is the same symbol. However, in Column E is, rows 6-8 have a B (stands for Buy) and row 9 has a S (stands for Sell). I would like to be able to separate these trades so even though they all have the same cusip, I would like to further separate them by what is column E, and group all the buys and all the sells together. See the "Results" sheet for how I would like it to look.

Can you help?

Bob Phillips
11-23-2012, 08:47 AM
Public Sub ProcessData()
Dim Lastrow As Long
Dim i As Long

Application.ScreenUpdating = False

With ActiveSheet

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

If .Cells(i, "C").Value2 = .Cells(i + 1, "C").Value2 And _
.Cells(i, "E").Value2 <> .Cells(i + 1, "E").Value2 Then

.Rows(i + 1).Resize(3).Insert
.Cells(i + 1, "A").Resize(3).Value = Application.Transpose(Array("Trade Allocation", "", "Total Quantity"))
.Rows(i + 1).Resize(3).Interior.ColorIndex = xlColorIndexNone
End If
Next i
End With

Application.ScreenUpdating = True
End Sub

Lartk
11-23-2012, 09:19 AM
That works great to divide up the trades by Buy or Sell. Two issues I am seeing though..see the attached sheet. As you can see, In Cell A5 is "Trade Allocation", which is correct. In cell A9 is "Trade Allocation"..this cell should read "Total Quantity". Cell A11 should read "Trade Allocation" and cell A13 should read "Total Quantity."

Also, in the lines for total quantity, I am trying to sum up the amount for each block trade. So I9 needs to be the sum of the above quantites for that trade. I13 needs to be the sum of the above quantity for that trade...etc.

can you help?

Sheet 2 has results of how i would like it to look.

Thanks!

Bob Phillips
11-23-2012, 11:31 AM
Public Sub ProcessData()
Dim Lastrow As Long
Dim endrow As Long
Dim i As Long

Application.ScreenUpdating = False

With ActiveSheet

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

endrow = i
Do

i = i - 1
Loop Until .Cells(i, "C").Value2 <> .Cells(i + 1, "C").Value2 Or _
.Cells(i, "E").Value2 <> .Cells(i + 1, "E").Value2

.Cells(endrow + 1, "I").Formula = "=SUM(I" & endrow & ":I" & i + 1 & ")"

If i > 6 Then

.Rows(i + 1).Resize(3).Insert
.Cells(i + 1, "A").Value = "Trade Allocation"
.Cells(i + 3, "A").Value = "Total Quantity"
.Rows(i + 1).Resize(3).Interior.ColorIndex = xlColorIndexNone
End If

i = i + 1
Next i
End With

Application.ScreenUpdating = True
End Sub

Lartk
11-26-2012, 09:09 AM
Sub SeparateMerrill()
Dim Lastrow As Long
Dim endrow As Long
Dim i As Long

Application.ScreenUpdating = False

With ActiveSheet

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

endrow = i
Do

i = i - 1
Loop Until .Cells(i, "C").Value2 <> .Cells(i + 1, "C").Value2 Or _
.Cells(i, "E").Value2 <> .Cells(i + 1, "E").Value2

.Cells(endrow + 1, "I").Formula = "=SUM(I" & endrow & ":I" & i + 1 & ")"

If i > 6 Then

.Rows(i + 1).Resize(3).Insert
.Cells(i + 1, "A").Value = "Trade Allocation"
.Cells(i + 3, "A").Value = "Total Quantity"
.Rows(i + 1).Resize(3).Interior.ColorIndex = xlColorIndexNone
End If

i = i + 1
Next i
End With

Application.ScreenUpdating = True
End Sub

The above code does not work becasue it is too specific. See the attached sheet, on the "Before macro" sheet is my data before I run the above macro. After I run the macro, the results of what the output is is on the "After macro" sheet. As you can see, it gets very messed up because the references are too specific. The number of rows of data can vary each time so the references need to be more general. On the "Desired results" sheet, I have what I would like the data to look like after I run the macro on the data in the "before macro" sheet.

Can you help fix it?

Bob Phillips
11-26-2012, 09:14 AM
The above code does not work becasue it is too specific.

It was coded to the data that you provided. If it was different,, you should have showed that to start with.

Lartk
11-26-2012, 09:34 AM
Yes the data I originally provided was just one example. But I should have also said that the number of rows will always vary. As you can see in the Before and After sheet attached above, some of the trades will need to be separated (such as the the trade for AAAAAA in which A31-A33 are sales and therefore need to be separated.) And other trades are all set just the way they are because they dont have anything that needs to be separated (such as the Buy of BBBBBB and the buy of CCCCCC..etc..) It will always be the case where some trades need to be formatted a little further while others are already good to go.

magelan
11-26-2012, 10:51 AM
This sounds like you want a pivot table.

Lartk
11-26-2012, 11:47 AM
No not a pivot table, just need to further separate trades not only by cusip but also by whether it is a buy or a sell.

magelan
11-26-2012, 12:06 PM
No not a pivot table, just need to further separate trades not only by cusip but also by whether it is a buy or a sell.

right, and if you throw that into a pivot table, you can separate by all of that, and drag and drop filters as you please. its probably the easiest and best way to organize lists of data.