Prev: Centreing Text In Excel Cells
Next: Default Cell value in changing environment
From: Radhakant Panigrahi on 22 Apr 2010 10:44
Hi,
can anybody help in the below query
i have the below data with me
RECEIPT_NUMBER TRX_NUMBER Amount Comments
9907893007 7000604737 151.06 Nil
9907893007 7000604737 175.51 Bill
9907893007 7000604737 -151.51 Nil
9907893007 7000604737 -151.51 Nil
9907893007 7000604737 -151.06 Nil
9910789407 7000857919 4,239.33 Nil
9910789407 7000857919 4,239.33 Bill
9910789407 7000857919 -4,239.33 Nil
9910699310 7100467576 -88.37 Nil
9910699310 7100467576 88.37 Bill
9910699310 7100467576 88.37 Nil

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"...for
example number 7000604737 comes 5 times and i 4 lines of this number are
cancelling each other and their sum to "ZERO"

I used the below formula but it did not worked...
=IF(OR(SUMPRODUCT(($A$2:$A3=A2)*($B$2:$B3=B2)*
($C$2:$C3=C2))>1,SUMPRODUCT(($A$2:$A$1000=A2)*
($B$2:$B$1000=B2)*($C$2:$C$1000=-C2))=0),"Bill","Nil")

Can anybody help me in identityfying the solution for this

Regards,
Radhakant
 | 
Pages: 1
Prev: Centreing Text In Excel Cells
Next: Default Cell value in changing environment