If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
identify the numbers cancelling by amounts
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 |
Thread Tools | |
Display Modes | |
|
|