Positive and Negative Value sum up to 0 for same employee name

[uday]

Hi,

I want to create a macro where positive and negative line items are matched up to 0 and move to the Clear tab. I have already provided the desired output that I want to generate. For Prosun there are 3 line items with the same value in different integer, however, I want to pick up the first pair which match up to 0 and move to the clear tab.

Regards,
Uday

 

[Marc L]

Hi,​

​

according to your attachment a VBA demonstration for starters :​

Sub Demo1()
      Const S = "Clear Transactions"
        Dim L&, M&(), V, W, R&, X
        Sheets(S).UsedRange.Offset(1).Clear
    With [Data!A1].CurrentRegion.Columns
            L = .Rows.Count
            ReDim M(1 To L, 0)
            V = .Parent.Evaluate(.Item(2).Address & "&" & .Item(1).Address)
            W = .Parent.Evaluate("-" & .Item(2).Address & "&" & .Item(1).Address)
        For R = 2 To L - 1
            If M(R, 0) = 0 Then
                X = Application.Match(W(R, 1), V, 0)
                If IsNumeric(X) Then M(R, 0) = 1: M(X, 0) = 1: V(R, 1) = Empty: V(X, 1) = Empty
            End If
        Next
           R = Application.Sum(M)
        If R Then
            Application.ScreenUpdating = False
           .Item(3).Value2 = M
           .Resize(, 3).Sort .Item(3), 1
           .Item(3).Clear
           .Rows(L + 1 - R & ":" & L).Cut Sheets(S).[A2]
            Application.ScreenUpdating = True
        End If
    End With
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​