|
| 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
April 21st, 2010, 08:31 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
Segregating Duplicate Transactions
Hi,
can anybody help in the below query i have the below data with me RECEIPT_NUMBER TRX_NUMBER PAYMENT_AMOUNT Comments 9907893007 7000604737 151.06 Cancelled 9907893007 7000604737 (151.06) Cancelled 9907893007 7000604737 151.51 Cancelled 9907893007 7000604737 (151.51) Cancelled 9907893007 7000604737 155.25 Bill 9910789407 7000857919 4,239.33 Cancelled 9910789407 7000857919 (4,239.33) Cancelled 9910789407 7000857919 4,239.33 Bill 9910699310 7100467576 88.37 Cancelled 9910699310 7100467576 (88.37) Cancelled 9910699310 7100467576 88.37 Bill 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"...and i have to do it for almost 20000 lines with 1000 transactions having the duplicacy problem. Is there any way or formula that would help me... i have tried the Vlook up formula but in vein. Earlier you have provided the below formaula The above formula converts the Negative values of C column to Positive values and arriving the result. If you compare the data by having +ve and –ve values then use the below one, but it is getting all the result as Single Entry. =IF(AND(A2="",B2="",C2="",D2=""),"",IF(SUMPRODUCT( ($A$2:$A$25000=A2)*($B$2:$B$25000=B2)*($C$2:$C$250 00=C2)*($D$2:$D$25000=D2))=2,"DUPLICATE",IF(SUMPR ODUCT(($A$2:$A$25000=A2)*($B$2:$B$25000=B2)*($C$2: $C$25000=C2)*($D$2:$D$25000=D2))=1,"SINGLE ENTRY",""))) Hi Thanks a lot for your hel in this regard...just to update you i am manually putting the comment in column "D" by scrolling down....by using your formula "=IF(AND(A2="",B2="",C2="",D2=""),"",IF(SUMPRODUCT (($A$2:$A$25000=A2)*($B$2:$B$25000=B2)*(ABS($C$2:$ C$25000)=ABS(C2))*($D$2:$D$25000=D2))=2,"DUPLICAT E",IF(SUMPRODUCT(($A$2:$A$25000=A2)*($B$2:$B$25000 =B2)*(ABS($C$2:$C$25000)=ABS(C2))*($D$2:$D$25000=D 2))=1,"SINGLE ENTRY","")))" i am getting all the duplicate transactions. However from these duplicate "transaction Numbers" i need further segreagation where i can get the "transaction Numbers" cancelling each other and whose sum is "Zero". for example in the below data, the duplicate transaction number 7000604737 comes 5 times and the first 4 transactions are duplicate and whose sum is "Zero" 9907893007 7000604737 151.06 Cancelled 9907893007 7000604737 (151.06) Cancelled 9907893007 7000604737 151.51 Cancelled 9907893007 7000604737 (151.51) Cancelled 9907893007 7000604737 155.25 Bill So would it be possible to get any formula set for this....really appericiate for your help in this regard. 
|
|
#2
April 21st, 2010, 08:45 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Segregating Duplicate Transactions
can you confirm that the numbers shown in brakcets in your sample data are
actually negative numbers and not text with brakcets - try changing the format to one that doesnt use brackets for negatives (like general) if you can still see the brakcets then you need to convert the payment amounts to numbers before the formula will work. Reg Migrant "Radhakant Panigrahi" wrote: Hi, can anybody help in the below query i have the below data with me RECEIPT_NUMBER TRX_NUMBER PAYMENT_AMOUNT Comments 9907893007 7000604737 151.06 Cancelled 9907893007 7000604737 (151.06) Cancelled 9907893007 7000604737 151.51 Cancelled 9907893007 7000604737 (151.51) Cancelled 9907893007 7000604737 155.25 Bill 9910789407 7000857919 4,239.33 Cancelled 9910789407 7000857919 (4,239.33) Cancelled 9910789407 7000857919 4,239.33 Bill 9910699310 7100467576 88.37 Cancelled 9910699310 7100467576 (88.37) Cancelled 9910699310 7100467576 88.37 Bill 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"...and i have to do it for almost 20000 lines with 1000 transactions having the duplicacy problem. Is there any way or formula that would help me... i have tried the Vlook up formula but in vein. Earlier you have provided the below formaula The above formula converts the Negative values of C column to Positive values and arriving the result. If you compare the data by having +ve and –ve values then use the below one, but it is getting all the result as Single Entry. =IF(AND(A2="",B2="",C2="",D2=""),"",IF(SUMPRODUCT( ($A$2:$A$25000=A2)*($B$2:$B$25000=B2)*($C$2:$C$250 00=C2)*($D$2:$D$25000=D2))=2,"DUPLICATE",IF(SUMPR ODUCT(($A$2:$A$25000=A2)*($B$2:$B$25000=B2)*($C$2: $C$25000=C2)*($D$2:$D$25000=D2))=1,"SINGLE ENTRY",""))) Hi Thanks a lot for your hel in this regard...just to update you i am manually putting the comment in column "D" by scrolling down....by using your formula "=IF(AND(A2="",B2="",C2="",D2=""),"",IF(SUMPRODUCT (($A$2:$A$25000=A2)*($B$2:$B$25000=B2)*(ABS($C$2:$ C$25000)=ABS(C2))*($D$2:$D$25000=D2))=2,"DUPLICAT E",IF(SUMPRODUCT(($A$2:$A$25000=A2)*($B$2:$B$25000 =B2)*(ABS($C$2:$C$25000)=ABS(C2))*($D$2:$D$25000=D 2))=1,"SINGLE ENTRY","")))" i am getting all the duplicate transactions. However from these duplicate "transaction Numbers" i need further segreagation where i can get the "transaction Numbers" cancelling each other and whose sum is "Zero". for example in the below data, the duplicate transaction number 7000604737 comes 5 times and the first 4 transactions are duplicate and whose sum is "Zero" 9907893007 7000604737 151.06 Cancelled 9907893007 7000604737 (151.06) Cancelled 9907893007 7000604737 151.51 Cancelled 9907893007 7000604737 (151.51) Cancelled 9907893007 7000604737 155.25 Bill So would it be possible to get any formula set for this....really appericiate for your help in this regard.
|
|
#3
April 21st, 2010, 10:37 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Segregating Duplicate Transactions
In cell D2 apply the below formula and copy down as required
=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","Cancelled") -- Jacob (MVP - Excel) "Radhakant Panigrahi" wrote: Hi, can anybody help in the below query i have the below data with me RECEIPT_NUMBER TRX_NUMBER PAYMENT_AMOUNT Comments 9907893007 7000604737 151.06 Cancelled 9907893007 7000604737 (151.06) Cancelled 9907893007 7000604737 151.51 Cancelled 9907893007 7000604737 (151.51) Cancelled 9907893007 7000604737 155.25 Bill 9910789407 7000857919 4,239.33 Cancelled 9910789407 7000857919 (4,239.33) Cancelled 9910789407 7000857919 4,239.33 Bill 9910699310 7100467576 88.37 Cancelled 9910699310 7100467576 (88.37) Cancelled 9910699310 7100467576 88.37 Bill 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"...and i have to do it for almost 20000 lines with 1000 transactions having the duplicacy problem. Is there any way or formula that would help me... i have tried the Vlook up formula but in vein. Earlier you have provided the below formaula The above formula converts the Negative values of C column to Positive values and arriving the result. If you compare the data by having +ve and –ve values then use the below one, but it is getting all the result as Single Entry. =IF(AND(A2="",B2="",C2="",D2=""),"",IF(SUMPRODUCT( ($A$2:$A$25000=A2)*($B$2:$B$25000=B2)*($C$2:$C$250 00=C2)*($D$2:$D$25000=D2))=2,"DUPLICATE",IF(SUMPR ODUCT(($A$2:$A$25000=A2)*($B$2:$B$25000=B2)*($C$2: $C$25000=C2)*($D$2:$D$25000=D2))=1,"SINGLE ENTRY",""))) Hi Thanks a lot for your hel in this regard...just to update you i am manually putting the comment in column "D" by scrolling down....by using your formula "=IF(AND(A2="",B2="",C2="",D2=""),"",IF(SUMPRODUCT (($A$2:$A$25000=A2)*($B$2:$B$25000=B2)*(ABS($C$2:$ C$25000)=ABS(C2))*($D$2:$D$25000=D2))=2,"DUPLICAT E",IF(SUMPRODUCT(($A$2:$A$25000=A2)*($B$2:$B$25000 =B2)*(ABS($C$2:$C$25000)=ABS(C2))*($D$2:$D$25000=D 2))=1,"SINGLE ENTRY","")))" i am getting all the duplicate transactions. However from these duplicate "transaction Numbers" i need further segreagation where i can get the "transaction Numbers" cancelling each other and whose sum is "Zero". for example in the below data, the duplicate transaction number 7000604737 comes 5 times and the first 4 transactions are duplicate and whose sum is "Zero" 9907893007 7000604737 151.06 Cancelled 9907893007 7000604737 (151.06) Cancelled 9907893007 7000604737 151.51 Cancelled 9907893007 7000604737 (151.51) Cancelled 9907893007 7000604737 155.25 Bill So would it be possible to get any formula set for this....really appericiate for your help in this regard.
|
|
#4
April 21st, 2010, 02:15 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Segregating Duplicate Transactions
Hi Jacob,
Thanks for the formaula, but it is still not working... sometimes the data may not be remain the sequence that i have provided...it wont working for the below sequence Could you please help me in this regard... Receipt transaction Amount 9907893007 7000604737 151.51 9907893007 7000604737 -151.06 9907893007 7000604737 -151.51 9907893007 7000604737 151.51 9907893007 7000604737 155.25 9910789407 7000857919 4,239.33 9910789407 7000857919 -4,239.33 9910789407 7000857919 4,239.33 9910699310 7100467576 88.37 9910699310 7100467576 88.37 9910699310 7100467576 -88.37 "Jacob Skaria" wrote: In cell D2 apply the below formula and copy down as required =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","Cancelled") -- Jacob (MVP - Excel) "Radhakant Panigrahi" wrote: Hi, can anybody help in the below query i have the below data with me RECEIPT_NUMBER TRX_NUMBER PAYMENT_AMOUNT Comments 9907893007 7000604737 151.06 Cancelled 9907893007 7000604737 (151.06) Cancelled 9907893007 7000604737 151.51 Cancelled 9907893007 7000604737 (151.51) Cancelled 9907893007 7000604737 155.25 Bill 9910789407 7000857919 4,239.33 Cancelled 9910789407 7000857919 (4,239.33) Cancelled 9910789407 7000857919 4,239.33 Bill 9910699310 7100467576 88.37 Cancelled 9910699310 7100467576 (88.37) Cancelled 9910699310 7100467576 88.37 Bill 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"...and i have to do it for almost 20000 lines with 1000 transactions having the duplicacy problem. Is there any way or formula that would help me... i have tried the Vlook up formula but in vein. Earlier you have provided the below formaula The above formula converts the Negative values of C column to Positive values and arriving the result. If you compare the data by having +ve and –ve values then use the below one, but it is getting all the result as Single Entry. =IF(AND(A2="",B2="",C2="",D2=""),"",IF(SUMPRODUCT( ($A$2:$A$25000=A2)*($B$2:$B$25000=B2)*($C$2:$C$250 00=C2)*($D$2:$D$25000=D2))=2,"DUPLICATE",IF(SUMPR ODUCT(($A$2:$A$25000=A2)*($B$2:$B$25000=B2)*($C$2: $C$25000=C2)*($D$2:$D$25000=D2))=1,"SINGLE ENTRY",""))) Hi Thanks a lot for your hel in this regard...just to update you i am manually putting the comment in column "D" by scrolling down....by using your formula "=IF(AND(A2="",B2="",C2="",D2=""),"",IF(SUMPRODUCT (($A$2:$A$25000=A2)*($B$2:$B$25000=B2)*(ABS($C$2:$ C$25000)=ABS(C2))*($D$2:$D$25000=D2))=2,"DUPLICAT E",IF(SUMPRODUCT(($A$2:$A$25000=A2)*($B$2:$B$25000 =B2)*(ABS($C$2:$C$25000)=ABS(C2))*($D$2:$D$25000=D 2))=1,"SINGLE ENTRY","")))" i am getting all the duplicate transactions. However from these duplicate "transaction Numbers" i need further segreagation where i can get the "transaction Numbers" cancelling each other and whose sum is "Zero". for example in the below data, the duplicate transaction number 7000604737 comes 5 times and the first 4 transactions are duplicate and whose sum is "Zero" 9907893007 7000604737 151.06 Cancelled 9907893007 7000604737 (151.06) Cancelled 9907893007 7000604737 151.51 Cancelled 9907893007 7000604737 (151.51) Cancelled 9907893007 7000604737 155.25 Bill So would it be possible to get any formula set for this....really appericiate for your help in this regard.
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
