• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Delete Positive Negative numbers in a column based on conditions

Hi Chandoo,


I have a problem in removing duplicates of Positive & negative signs in a same column. There will be only one positive & one negative with same amount. More than one in either of the positive & negatives are not possible. A sample of my worksheet goes like this...


Cell Type Amount

A2 Debit 3.25

A3 Debit 6.58

A4 Debit 27063.99

A5 Credit -4369.93

A6 Credit -307.83

A7 Credit -27063.99


In the above sample, I need to remove only the matching positive & negative numbers. Meaning, Cell numbers A4 & A7 should be deleted and only rest of them should remain. Any excel formula / macro you can suggest me to solve this problem?


Thanks for your kind help.


Regards,

Ramnath
 
I have assumed that Debit/Credit is in Column A and the amount is in Column B as values

To highlight quickly use something like this in C2 and copy down

=SUMPRODUCT(1*($B$2:$B$7=-B2))

It will put a 1 in column C if there is a duplicate or a 0 if not

Then either delete rows with 1's manually or sort and delete as a group of 1's
 
Dear Hui,


Thanks for your immediate response.


Yes. Your assumption is right. There are 3 different columns occupying 7 rows.


I am trying to automate some reconciliation and I have done almost 99% of it. Finally, I had this challenge of removing both positive & negative numbers which are one and the same, excepting for the sign. I need some formula or some macro which should do this without any manual involvement.


I myself has written some macros and lots of formulas to achieve the final result and simulated by tagging them to a Command button.


The above requirement should also function the same way so that I need not ask the user to do this alone separately.


Thanks once again for your kind inputs.


Regards,

Ramnath
 
Ramnath


The following code will do what you want

Copy it to a Module in VBA and run as applicable


It will goto cell B2 and then use the data below that until it finds a blank cell in Column C


It then sets all matching values to zero and then deletes the first 3 cells of each row where the Value in column B is now zero

[pre]
Code:
Sub RemoveNegs()

Dim x As Integer
Dim MaxRows As Integer
Dim c As Range
Dim r As Range

Sheets("Sheet1").Select 'Change to suit
Range("B1").Select
Selection.End(xlDown).Select
MaxRows = Range(Selection, Selection.End(xlDown)).Rows.Count

For Each c In Range(Cells(2, 2), Cells(MaxRows, 2)) 'This changes Values to Zero when a negative match
For Each r In Range(Cells(c.Row + 1, 2), Cells(MaxRows + 1, 2))
If c.Value = -r.Value Then
c.Value = 0
r.Value = 0
End If
Next
Next

For x = MaxRows + 1 To 2 Step -1 'This deletes all the rows which are zero
If Cells(x, 2).Value = 0 Then
Range(Cells(x, 1), Cells(x, 3)).Delete Shift:=xlUp
End If
Next

End Sub
[/pre]
 
Back
Top