Reconciliation

Hi,

I would appreciate any suggestions how to create a macro that deletes the rows with the same value in column A and the sum of values in column B equal to zero.

Example:
Column A Column B
AAA 50
BBB 100
AAA -50

I was thinking of using the worksheet function SumIf, but am not sure how exactly to do that.

Thanks.
Eduard

I think I'd use another column that contained that =sumif() formula:

=sumif(a:a,a1,b:b)
and drag down.

Then apply data|Filter|autofilter to that helper column and filter to show 0's.

Then delete the visible rows.

If I needed a macro, I'd record it when I did it manually.

Eduard wrote:
>
> Hi,
>
> I would appreciate any suggestions how to create a macro that deletes
> the rows with the same value in column A and the sum of values in
> column B equal to zero.
>
> Example:
> Column A Column B
> AAA 50
> BBB 100
> AAA -50
>
> I was thinking of using the worksheet function SumIf, but am not sure
> how exactly to do that.
>
> Thanks.
> Eduard
>
> --
> Eduard
> ------------------------------------------------------------------------
> Eduard's Profile: http://www.excelforum.com/member.php...o&userid=26802
> View this thread: http://www.excelforum.com/showthread...hreadid=400571

--

Dave Peterson

Another way as a macro:

Option Explicit
Sub testme01()

Dim myCell As Range
Dim myRng As Range
Dim delRng As Range

With Worksheets("sheet1")
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))

For Each myCell In myRng.Cells
If Application.SumIf(.Range("a:a"), myCell.Value, _
.Range("b:b")) = 0 Then
If delRng Is Nothing Then
Set delRng = myCell
Else
Set delRng = Union(myCell, delRng)
End If
End If
Next myCell
End With

If delRng Is Nothing Then
'do nothing
Else
delRng.EntireRow.Delete
End If

End Sub

Eduard wrote:
>
> Hi,
>
> I would appreciate any suggestions how to create a macro that deletes
> the rows with the same value in column A and the sum of values in
> column B equal to zero.
>
> Example:
> Column A Column B
> AAA 50
> BBB 100
> AAA -50
>
> I was thinking of using the worksheet function SumIf, but am not sure
> how exactly to do that.
>
> Thanks.
> Eduard
>
> --
> Eduard
> ------------------------------------------------------------------------
> Eduard's Profile: http://www.excelforum.com/member.php...o&userid=26802
> View this thread: http://www.excelforum.com/showthread...hreadid=400571

--

Dave Peterson

Thanks, Dave.

I'v tried the first option and it works as I expected.

Eduard