transactions amounts offset eachother

  • Thread starter Radhakant Panigrahi
  • Start date
R

Radhakant Panigrahi

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
offset each other by amountand their sum to "ZERO"
 
B

Bernie Deitrick

In another column, use a formula like

=ABS(C2) and copy down.

Then sort by your receipt number, trx number, and that number to get pairs
at least close together.

HTH,
Bernie
MS Excel MVP
 
R

Radhakant Panigrahi

Hi Bernie,

i have tried and it did not worked and there are almost 5000 lines in which
i have 1000 lines having these issue where i have to do manually for all the
duplicate transactions...is there any worksheet formula that could help
me...or would there be any VB code can be set up for this.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top