How to build a column that confirms there is two of the same instance for each unique ID

[salemalnahdi14]

Hi,

I am trying to build a flag column that will flag when there is duplicate types for the same unique ID, I only want to flag when there is duplicate for the same ID and type. I don't want to use the "Unique" tool, as I want to build a flag column instead to use in other parts of the workflow. I am not sure how to best approach this.

Below is a sample dataset and what I would like the output to look like:

Input:

ID	Type
1	A
1	A
1	B
2	A
2	C
3	A

Output:

ID	Type	New Column "Flag"
1	A	Y
1	A	Y
1	B
2	A
2	C
3	A

Thanks in advance for the help!

[binuacs · Accepted solution]

@salemalnahdi14 One way of doing this

[fluteman]

Hi,

A unique row from a dataset perspective is concatenation of the information you consider unique. If there was a measure like a number you wanted summed, this would be excluded from the concatenation.

thus as described by the commentary boxes, one creates the concatenation and then does a count by the unique id - rejoins this to the dataset by the unique id, to pull in the count; then if count > 1 then flag = 'Y'; else 'N'.

You may be able to convert this into a macro - to reuse it.

Hope this helps.

Fluteman

[flying008 · Accepted solution]

Hi, @salemalnahdi14 

FYI.