R

#### Radhakant Panigrahi

can anybody help in the below query

i have the below data with me

RECEIPT_NUMBER TRX_NUMBER PAYMENT_AMOUNT Comments

9907893007 7000604737 151.06 Cancelled

9907893007 7000604737 (151.06) Cancelled

9907893007 7000604737 151.51 Cancelled

9907893007 7000604737 (151.51) Cancelled

9907893007 7000604737 155.25 Bill

9910789407 7000857919 4,239.33 Cancelled

9910789407 7000857919 (4,239.33) Cancelled

9910789407 7000857919 4,239.33 Bill

9910699310 7100467576 88.37 Cancelled

9910699310 7100467576 (88.37) Cancelled

9910699310 7100467576 88.37 Bill

in the above details the some transactions are coming repetitively and

having +ve and -ve with same amounts cancelling each other and i need

manually segregate them and put comments manually in column "D"...and i have

to do it for almost 20000 lines with

1000 transactions having the duplicacy problem. Is there any way or formula

that would help me... i have tried the Vlook up formula but in vein.

Earlier you have provided the below formaula

The above formula converts the Negative values of C column to Positive

values and arriving the result.

If you compare the data by having +ve and â€“ve values then use the below one,

but it is getting all the result as Single Entry

=IF(AND(A2="",B2="",C2="",D2=""),"",IF(SUMPRODUCT(($A$2:$A$25000=A2)*($B$2:$B$25000=B2)*($C$2:$C$25000=C2)*($D$2:$D$25000=D2))>=2,"DUPLICATE",IF(SUMPRODUCT(($A$2:$A$25000=A2)*($B$2:$B$25000=B2)*($C$2:$C$25000=C2)*($D$2:$D$25000=D2))=1,"SINGLE ENTRY","")))

Hi Thanks a lot for your hel in this regard...just to update you i am

manually putting the comment in column "D" by scrolling down....by using

your formula

"=IF(AND(A2="",B2="",C2="",D2=""),"",IF(SUMPRODUCT(($A$2:$A$25000=A2)*($B$2:$B$25000=B2)*(ABS($C$2:$C$25000)=ABS(C2))*($D$2:$D$25000=D2))>=2,"DUPLICATE",IF(SUMPRODUCT(($A$2:$A$25000=A2)*($B$2:$B$25000=B2)*(ABS($C$2:$C$25000)=ABS(C2))*($D$2:$D$25000=D2))=1,"SINGLE ENTRY","")))" i am

getting all the duplicate transactions. However from these duplicate

"transaction Numbers" i need further segreagation where i can get the

"transaction Numbers" cancelling each other and whose sum is "Zero". for

example in the below data, the duplicate transaction number 7000604737 comes

5 times and the first 4 transactions are duplicate and whose sum is "Zero"

So would it be possible to get any formula set for this....really

appericiate for your help in this regard.