Finding Duplicates

[Akash__on]

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

[binuacs]

@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 · Accepted solution]

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.

[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