I want to eliminate the debit and credit

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

@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

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.

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:

Please Login or Register  to view this content.

Let me know if this works.

Thanks.