Remove duplicate

dear Sir,


i attached a file in which column A is dr_cr and E id INST_NO and column G is INST_AMT. This file like a bank statement. in which one instrument(cheque) present and i denote it c(credit) in column A. but if cheque credit then d(debit) means that this cheque present and dishonour. but some time one cheque credit and then debit and then credit. it means that we have to remove previous credit and debit entries. in this attached file you found this type of entries. i want to remove this type of entries. i further explain.
1. if one instrument have one credit and one debit its ok.
2. if one instrument two credit and one debit then remove one credit and one debit where instrument no and amount and drawee bank must be same.
3. if one instrument have two credit and two debit we have two remove one one debit and one credit.


4. if one instrument have three credit and two debit then we have to remove two credit and two debit so one credit left.


i hope i clear the situation.

Hi tanmanoj,

Enter below formula in H2 and drag down.

=SUMPRODUCT(($E$2:$E$10106=$E2)*($F$2:$F$10106=$F2)*($G$2:$G$10106=$G2))

Now apply filter and you'll see 1,2,3,5 in the column H where 3 and 5 is the pain area. Now you can analyse and remove unwanted rows easily.

Regards,
DILIPandey