Hi, I am trying to find out the duplicate payments which are represented as base amount. Duplicate payment is amount which is placed twice for same account, for same description with same base amount. Anything with is Credit and Debited is not the duplicate payment. like -450 and 450 for 109192. Whereas 232 for 34233 is duplicate payment. Account no.descriptionbase amountCredit/Debittransaction date109192Sales-450C12/05/2023109192Sales450D13/05/202334233transport232C01/05/202334233transport232C01/05/2023109192G&H123C12/05/2023109192G&H232C13/05/2023109192Sales123C01/05/2023 If any more info needed surely hit me back. Thanks

Finding Duplicates

Hi,

I am trying to find out the duplicate payments which are represented as base amount. Duplicate payment is amount which is placed twice for same account, for same description with same base amount.

Anything with is Credit and Debited is not the duplicate payment. like -450 and 450 for 109192. Whereas 232 for 34233 is duplicate payment.

Account no.	description	base amount	Credit/Debit	transaction date
109192	Sales	-450	C	12/05/2023
109192	Sales	450	D	13/05/2023
34233	transport	232	C	01/05/2023
34233	transport	232	C	01/05/2023
109192	G&H	123	C	12/05/2023
109192	G&H	232	C	13/05/2023
109192	Sales	123	C	01/05/2023

If any more info needed surely hit me back. Thanks

Developer Tools

Data Investigation

Accepted answers

jdminton

Duplicate transactions can be a little tricky. Summarize and Unique can definitely help, but you will need a little more as well. I like to use summarize as opposed to unique when looking for duplicates so I can see the items that are matching as duplicates to see if I need to include an additional field as part of my criteria to eliminate duplicates.

In the attached workflow, I also sum the amount and eliminate the net zero amounts as you may have several transactions that net to zero. If your data are invoices, you would want to make sure that payments and credit memos are not part of the data when using this workflow.

Find duplicates.yxmd

All comments

binu_acs

@Akash__on Use the summarize tool -

group by Account No, Description then take count of base amount

Filter where count >=2, join back with the input , join keys AccountNo , description

ShankerV

Hi @Akash__on

This can be achieved with the help of Unique tool too.

Many thanks

Shanker V

DanielvanLeuzen

Hi Akash_on,

I would use the unique tool. You can select in the tool which combination of columns need to have unique values. In your case I would select all columns. It has a U (unique) and D (duplicate) output

jdminton

Find duplicates.yxmd

Akash__on

@jdminton Thanks. This is what I was looking for. If I will have any problem ahead with this solution will ping here.

Best,

Aakash

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703