Excel help - need to remover or zero out matching tranactions

[grecon]

Hi I am working on an excel sheet and I need to do something specific please look at the following doc.

Thanks

 

[SkipVought]

Hi,

Please be aware that many of us that would look at your issues, cannot, due to company security restrictions.

Please state the nature of your question and post relevent examples as necessary.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[grecon]

basically it is like this:

Order # Cust Balance PAID BAL
135318-1 Kalwall 561.06 0.00 561.06

136353-1 Abbott 66.45 0.00 66.45

136710-1 Acalanes 434.95 0.00 434.95

135931-1 Addison 56.15 0.00 56.15
135931-1 Addison 0.00 56.15 (56.15)

135322-1 Adjo 378.00 0.00 378.00
135322-1 Adjo 0.00 378.00 (378.00)

135594-1 Adjo 24.50 0.00 24.50
135594-1 Adjo 0.00 24.50 (24.50)

136802-1 Adjo 186.00 0.00 186.00

136082-1 AE M 333.85 0.00 333.85
136082-1 AE M 0.00 333.85 (333.85)

137000-1 AE M 64.35 0.00 64.35
137000-1 AE M 0.00 64.35 (64.35)

So in the above example you see order #135322-1 has balance of 378.00 and on the next line a paid of 378.00, the way the data is brought over into excel it puts the balance and paid on two different lines and then for the Final Balance it puts 378.00 and (378.00) I want to have a formula that looks at the order number and the amounts and if the pament matches the balance and order number it delets that order # (both lines the balance and paid) so that I am left with a report that only shows those customers owing a BAL. In the above example it would leave me with only 4 customers the 561.06 66.45 434.95 and 186.00

Hope this makes sense, thanks for looking.

 

[Gavona]

Formula for cell E2 (and then copied down)
=or(And(A2=A3,D2+D3=0),And(A2=A1,D2+D1=0))
Will return True if the invoice is fully settled or False if there is an outstanding balance or it is a blank row.

=or(And(A2=A3,D2+D3=0),And(A2=A1,D2+D1=0),isblank(A2))
Will return True on the blank rows if that is your requirement.

Autofilter column E with a criteria of True and delete those rows.

If you want to use VBA for this then record yourself doing this then post back your code if you need help.

Gavin

 

[grecon]

That didn't work I get false on all the records and a #VALUE! error

 

[SkipVought]

This can be done quite easily with a Pivottable, adding a formula,

NET: Balance-paid

then summing NET for your order and customer. filter out the ZERO values. NO VBA required!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Bong]

But if you (like me) like VBA just for kicks:

Sub reduceBal()
    Set r = Sheet1.UsedRange
    lastr = r.Rows.Count
    For i = 3 To lastr
        If Cells(i, 1) = Cells(i - 1, 1) And Cells(i, 2) = Cells(i - 1, 2) Then
            Cells(i, 6) = Cells(i, 3) + Cells(i - 1, 3)
            Cells(i, 7) = Cells(i, 4) + Cells(i - 1, 4)
            Cells(i, 8) = Cells(i, 5) + Cells(i - 1, 5)
        End If
    Next
End Sub

_________________
Bob Rashkin

 

[SkipVought]

yea, Bob, sometimes simply, "because it's there" ya gotta try!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[grecon]

I do like the VBA, is there a way to just tell it to delete all the records that match, all debit and credits that match the order# that 0 out?

 

[SkipVought]

FYI, I rarely like to tamper with the source data (add, change, delete)

I would much rather create a report based on the source that has the add, change, delete logic in it.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}