I want to eliminate the debit and credit

[optixbaba]

Hi

Please help me eliminate the repeated amount in different signs

I have debits & credits in the same excel column and i want to delete the matching amounts but with opposite signs.

Ope

[FDibbins]

Hi and welcome to the forum

next time, please give your thread a title that is a bit more descriptive of what you want

try this approach...
in C3, copied down, use this...
=ABS(B3)
then in D3, copied down, use this...
=COUNTIF($C$3:$C$14720,ABS(C3))

Sort the data in descending order and delete all rows down to (but excluding) 1

OR if you dont want to sort, you could apply filters, filter on D to exclude 1, and delete what remains

If you want, you can then delete the 2 helper columns (C&D

[bebo021999]

@Fdibbins,
I afraid that with ABS, 2 or more same amounts which all in credit(or all in debit) are deleted
i.e row 21 & 22 with same debit of -21968081.19

[newdoverman]

There are over 3800 duplicates in this data and there are many blank cells in column A that have values in column B. Many of the values in column B show up more than 30 times and as many as 46 times.

If this is valid data, it is going to be very difficult to be sure that the proper matching amounts are cancelling each other.

[AB33]

See attached.
Converted the values in +ve and then using Excel's remove duplicate function, I have deleted all in column C

[tandwfund]

AB33,

Hi - I am not sure this solution is providing what optixbaba is asking for. As I read his request, it sounds like he only wants to delete equal (+) and (-) values, not remove all duplicate values. See this sample, rows 50 - 53 in original tab....

1900011379 9,355,939.20
1900011395 9,355,939.20
1900011396 -9,355,939.20
1900011397 9,355,939.20

IMHO, this part of the data should retain (2) values of 9,355,939.20. The resultant data provided only contains (1) of these values.

Regards,

Terry

[tandwfund]

AB33,

Sorry I missed one number ....

Terry

[Jarko28]

Hi,

I agree with tandwfund in the sense that this needs to account for the unique credit debit pair, but ignore all results with identical values that do not have a matching pair even if the same negative value exists and has already been paired.

I also thought that this needed to take a look at the document ID and account for credits/debits for only items with the same document ID.


My solution is attached. code below:

Sub RemoveUniqueMatches()

Application.ScreenUpdating = False

'Declare variables
Dim lr As Long
Dim i As Long
Dim c As Object
Dim sht As Worksheet

Set sht = ActiveSheet
lr = sht.Range("B" & Rows.Count).End(xlUp).Row

'Create Non Unique item and qty ID's
With Range("c2")
.Formula = "=CONCATENATE(A2,B2)"
.AutoFill Destination:=Range("C2:C" & lr)
End With

'Create unique pair combination ID's
With Range("D2")
.Formula = "=SUBSTITUTE(c2&"" ""&COUNTIF($C$2:$C2,C2),""-"","""")"
.AutoFill Destination:=Range("D2:D" & lr)
End With

'Hard Code Unique ID values
    Columns("D:D").Select
    Selection.Copy
    Range("D1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

'Determine which Unique ID's have a matching pair
With Range("E2")
.Formula = "=COUNTIF(D$2:D30000,D2)"
.AutoFill Destination:=Range("E2:E" & lr)
End With

'Delete matching pairs
With Columns("E")
    .AutoFilter field:=1, Criteria1:=2
    .Resize(Rows.Count - 1).Offset(1).EntireRow.Delete
End With
ActiveSheet.AutoFilterMode = False

'Delete the helper columns
Range("C:E").Clear

Range("A1").Select

    Application.ScreenUpdating = True
    
End Sub

Let me know if this works.

Thanks.