look for offsetting positions, then move to another tab

[daveyc18]

Sub delete_cancelled()

Sheets("IE").Select

Application.ScreenUpdating = False


finalrow = Cells(Rows.Count, "A").End(xlUp).Row



For i = 2 To finalrow
    For j = 2 To finalrow

'establish blotter, account number, etc.

blotter = Range("p" & i)
account = Range("j" & i)
net_amt = Abs(Range("e" & i))
amt = Abs(Range("a" & i))
buy_sold_indic = Range("l" & i)
sd = Range("x" & i)
Tag = Range("aa" & i)
cancel_indic = Range("o" & i)

'establish botter, account number, etc. for non-cancelled trades

    blotter2 = Range("p" & j)
    account2 = Range("j" & j)
    net_amt2 = Abs(Range("e" & j))
    amt2 = Abs(Range("a" & j))
     buy_sold_indic2 = Range("l" & j)
    sd2 = Range("x" & j)
    tag2 = Range("aa" & j)
    cancel_indic2 = Range("o" & j)





'if blotter, account, etc. for the cancelled trade equals the blotter, account, etc. for non-cancelled trades, and the cancelled and non-cancelled trades buy/sold and cancel indicators don't equal to each other, then cut the cancelled row and the non-cancelled row to the  IE Cancelled tab


If blotter = blotter2 And account = account2 And net_amt = net_amt2 And sd = sd2 And Tag = tag2 And buy_sold_indic <> buy_sold_indic2 And cancel_indic <> cancel_indic2 Then




Range("a" & i).EntireRow.Cut

Sheets("IE Cancelled").Activate

totalrow = Cells(Rows.Count, "A").End(xlUp).Row + 1

Range("a" & totalrow).Select
ActiveSheet.Paste

Sheets("IE").Activate

Range("a" & j).EntireRow.Cut


Sheets("IE Cancelled").Activate

totalrow = Cells(Rows.Count, "A").End(xlUp).Row + 1

Range("a" & totalrow).Select
ActiveSheet.Paste

Sheets("IE").Activate


 End If




Next j
Next i

   
   
   
   'delete blank rows
   
   
   Sheets("IE").Activate

finalrow = Cells(Rows.Count, "A").End(xlUp).Row

   
   For k = finalrow To 2 Step -1
   
   If Range("a" & k) = "" Then
   Range("a" & k).EntireRow.Delete
   End If
   Next k

essentially the macro is trying to loop through thousands for rows ....and if it finds any pair of offsetting trades, move them to the canceled/offsetting worksheet

offsetting trades are essentially identically , but have opposite values (e.g., 100 buys, other has -100 sells) and a few other categories (sample rows will be provided in the next post).

i thought the code above did the trick, but it didnt seem to work for me this month.

also, it's very slow.....any way to make it more efficient?

 

[daveyc18]

Loading Google Sheets

sample data

ofc...there will be other rows of data that do not have offset ...

 

[daveyc18]

Loading Google Sheets

sample data

ofc...there will be other rows of data that do not have offset ...

bump

anybody have an efficient way to detect such pairs then move to another tab ?

 

[daveyc18]

anybody ??? lol