Prev: Pivot table formulas
Next: pop up calender
From: Radhakant Panigrahi on 28 Apr 2010 11:29
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"

From: "Bernie Deitrick" deitbe on 28 Apr 2010 14:44
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


"Radhakant Panigrahi" <rkp.gen(a)gmail.com> wrote in message
news:0165B70F-01AA-44E7-9F00-A8B460D0E5D5(a)microsoft.com...
> 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"
>


From: Radhakant Panigrahi on 29 Apr 2010 01:19
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.

"Bernie Deitrick" wrote:

> 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
>
>
> "Radhakant Panigrahi" <rkp.gen(a)gmail.com> wrote in message
> news:0165B70F-01AA-44E7-9F00-A8B460D0E5D5(a)microsoft.com...
> > 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"
> >
>
>
> .
>
 | 
Pages: 1
Prev: Pivot table formulas
Next: pop up calender