| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
segreating duplicate Trasactions
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 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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
segreating duplicate Trasactions
Hi
It looks to me that all your duplicated amounts are Cancelled - which would be correct, with contra values being written against the first sum of money. If you apply an Autofilter, then filter on Comments for Cancelled. Alternatively, you can mark the whole block of your dataSort Column CAscending and all of the Cancelled transactions will fall to the bottom of the list. -- Regards Roger Govier 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 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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
segreating duplicate Trasactions
I am not clear whether this is what you are looking for€¦
I assume that your data is present from Column A to Column D and your data headers are in 1st row and the data is starting from Row2. Copy and paste the below formula in E2 cell and drag it for the remaining cells of E Column Based on the Left Column data. =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,"DUPLICATE ",IF(SUMPRODUCT(($A$2:$A$25000=A2)*($B$2:$B$25000= B2)*(ABS($C$2:$C$25000)=ABS(C2))*($D$2:$D$25000=D2 ))=1,"SINGLE ENTRY",""))) 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",""))) -- Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "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 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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
segreating duplicate Trasactions
Hi Thanks a lot for your hel in this regard...just to update you i am
manually putting the comment in column "D" ....by using your formula i a 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". Regards, Radhakant "Ms-Exl-Learner" wrote: I am not clear whether this is what you are looking for€¦ I assume that your data is present from Column A to Column D and your data headers are in 1st row and the data is starting from Row2. Copy and paste the below formula in E2 cell and drag it for the remaining cells of E Column Based on the Left Column data. =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,"DUPLICATE ",IF(SUMPRODUCT(($A$2:$A$25000=A2)*($B$2:$B$25000= B2)*(ABS($C$2:$C$25000)=ABS(C2))*($D$2:$D$25000=D2 ))=1,"SINGLE ENTRY",""))) 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",""))) -- Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "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 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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
segreating duplicate Trasactions
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. "Ms-Exl-Learner" wrote: I am not clear whether this is what you are looking for€¦ I assume that your data is present from Column A to Column D and your data headers are in 1st row and the data is starting from Row2. Copy and paste the below formula in E2 cell and drag it for the remaining cells of E Column Based on the Left Column data. =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,"DUPLICATE ",IF(SUMPRODUCT(($A$2:$A$25000=A2)*($B$2:$B$25000= B2)*(ABS($C$2:$C$25000)=ABS(C2))*($D$2:$D$25000=D2 ))=1,"SINGLE ENTRY",""))) 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",""))) -- Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "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 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| duplicate | Excel Discussion (Misc queries) | |||
| duplicate name help please | Excel Worksheet Functions | |||
| Duplicate | Excel Discussion (Misc queries) | |||
| How do you delete duplicate addresses, but keep duplicate names? | Excel Discussion (Misc queries) | |||
| Duplicate | Excel Worksheet Functions | |||
Linear Mode
