Seperate unique accounts and manipulate data

[blackie42]

Hi

Wonder if anyone has time to help create some code for following..

Need to insert a line after every unique account in column K

Need to change the sign on values in AB where value in O is Sell

Need to sum values in AB for each unique account (in AB where the line was inserted)

Have attached before (sheet1) and after (sheet2) if this helps - just a sample, actual sheet is a lot bigger of course

Be really grateful for any help

thanks
Jon

[mana]

Option Explicit

Sub test()
    Dim r As Range
    Dim v
    Dim i As Long
    
    Set r = Range("k1", Range("k1").End(xlDown)).Resize(, 18)
    
    v = r.Value
    
    For i = 1 To UBound(v)
        If v(i, 5) = "Sell" Then v(i, 18) = v(i, 18) * -1
    Next
    
    r.Value = v
    
    r.Rows(1).Insert xlDown
    Set r = Range("k1").Resize(i, 18)
    Application.DisplayAlerts = False
    r.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(18), SummaryBelowData:=True
    Application.DisplayAlerts = True
    r.Rows(1).Delete xlUp
        
End Sub

[blackie42]

Hi Mana,

Code is good - any chance you could highlight the accounts that have total as 0.00 in green?

But thanks very much
regards
Jon

[mana]

Option Explicit

Sub test2()
    Dim r As Range
    Dim v
    Dim i As Long
    
    ActiveSheet.Copy
     
    Set r = Range("k1", Range("k1").End(xlDown)).Resize(, 18)
     
    v = r.Value
     
    For i = 1 To UBound(v)
        If v(i, 5) = "Sell" Then v(i, 18) = v(i, 18) * -1
    Next
     
    r.Value = v
      
    r.Columns(18).FormatConditions.Add( _
        Type:=xlExpression, _
        Formula1:="=AND(R[0]C[0]=0,ISBLANK(R[0]C[-13]))") _
        .Interior.Color = vbGreen
  
    r.Rows(1).Insert xlDown
    Set r = Range("k1").Resize(i, 18)
    Application.DisplayAlerts = False
    r.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(18), SummaryBelowData:=True
    Application.DisplayAlerts = True
    r.Rows(1).Delete xlUp
     
End Sub

[blackie42]

Thanks Mana

regards
Jon