Delete Positive Negative numbers in a column based on conditions

[dramnath1981]

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

 

[Hui]

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

 

[dramnath1981]

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

 

[Hui]

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]

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]

 

[dramnath1981]

Thanks Hui. You are really helpful.


Regards,

Ramnath