Alteryx use case for accounting - help with excel specific formulas

[aspiringanalyst]

Good day,

I am a fairly new user of Alteryx and i have been trying to integrate this tool into my daily work, however while I am still learning I have run into difficulties.

For example, in my work one of my trask is to identify reversed account entries, i.e. yesterday someone recorded a $20 sale and today someone entered a -$20 sale.

The formula I am usually using for this in excel looks like the following:

=(COUNTIFS($M$12:M12,M12)<=MIN(COUNTIFS($M$12:$M$2999111,-M12),(COUNTIFS($M$12:$M$2999111,M12))))

This returns a "TRUE" for the entries I should investigate more.

These days the excel files i have to operate on are getting bigger and bigger and regular excel does not cut it anymore.

[Ben_H · Accepted solution]

Hi @aspiringanalyst 

I've attached a solution, but there will be other ways.

I used a formula tool to create a value with the opposite symbol i.e. 20 becomes -20.

Summarise the values to get to a list of unique input values (obviously could use the unique tool instead), then join the summarised values to the opposite symbol field. The records that join are those that require investigation.

Regards,

Ben