Formula to identify unique values within a column?

[laurennewton]

Hello all,

I am looking for a solution for the following issue:

Currently I have an excel formula that looks at a column labeled "Document Number" and compares one document number to others to see if there are duplicates. If there are duplicates the formula populates the column "Unique Values?" with a 1 and if the document number is not listed more than once than it is  assigned a 0.

Is there a way to recreate this formula in Alteryx formula tool?

Thanks!

[ChrisTX]

The easiest way may be to use a Summarize tool with the action Count Distinct on your Document Number field.  Then you could use a Formula tool to set a new field value to 0 or 1, based on the value in the Count Distinct column.

Also take a look at the Unique tool under the Preparation palette, and Only Unique from the CReW macro pack.

Chris

[apathetichell · Accepted solution]

@ChrisTXis totally right that the summarize tool/count distinct/count distinct non-null is made for this.

1) Group by document number

2) count distinct (or count distinct non-null if you have nulls)