A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

identify the numbers cancelling by amounts



 
 
Thread Tools Display Modes
  #1  
Old April 22nd, 2010, 03:44 PM posted to microsoft.public.excel.worksheet.functions
Radhakant Panigrahi
external usenet poster
 
Posts: 43
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:44 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.